Oracle Gespeicherte PL/SQL-Prozedur und -Funktionen mit Beispielen

In diesem Tutorial erfahren Sie ausführlich, wie Sie die benannten Blöcke (Prozeduren und Funktionen) erstellen und ausführen.

Prozeduren und Funktionen sind die Unterprogramme, die als Datenbankobjekte erstellt und in der Datenbank gespeichert werden können. Sie können auch innerhalb der anderen Blöcke aufgerufen oder referenziert werden.

Darüber hinaus werden wir die Hauptunterschiede zwischen diesen beiden Unterprogrammen behandeln. Außerdem werden wir darüber diskutieren Oracle Integrierte Funktionen.

Terminologien in PL/SQL-Unterprogrammen

Bevor wir etwas über PL/SQL-Unterprogramme lernen, werden wir die verschiedenen Terminologien besprechen, die Teil dieser Unterprogramme sind. Nachfolgend finden Sie die Terminologien, die wir besprechen werden.

Parameter

Der Parameter ist eine Variable oder ein Platzhalter eines beliebigen gültigen Parameters PL/SQL-Datentyp über den das PL/SQL-Unterprogramm die Werte mit dem Hauptcode austauscht. Dieser Parameter ermöglicht es, den Unterprogrammen Eingaben zu geben und aus diesen Unterprogrammen zu extrahieren.

  • Diese Parameter sollten zusammen mit den Unterprogrammen zum Zeitpunkt der Erstellung definiert werden.
  • Diese Parameter sind in der Aufrufanweisung dieser Unterprogramme enthalten, um die Werte mit den Unterprogrammen zu interagieren.
  • Der Datentyp des Parameters im Unterprogramm und in der aufrufenden Anweisung sollte identisch sein.
  • Die Größe des Datentyps sollte zum Zeitpunkt der Parameterdeklaration nicht angegeben werden, da die Größe für diesen Typ dynamisch ist.

Basierend auf ihrem Zweck werden Parameter als klassifiziert

  1. IN-Parameter
  2. OUT-Parameter
  3. IN OUT-Parameter

IN-Parameter

  • Dieser Parameter wird zur Eingabe von Eingaben an die Unterprogramme verwendet.
  • Es handelt sich um eine schreibgeschützte Variable innerhalb der Unterprogramme. Ihre Werte können innerhalb des Unterprogramms nicht geändert werden.
  • In der aufrufenden Anweisung können diese Parameter eine Variable, ein Literalwert oder ein Ausdruck sein. Beispielsweise könnte es sich um einen arithmetischen Ausdruck wie „5*8“ oder „a/b“ handeln, wobei „a“ und „b“ Variablen sind .
  • Standardmäßig sind die Parameter vom Typ IN.

OUT-Parameter

  • Dieser Parameter wird verwendet, um die Ausgabe der Unterprogramme abzurufen.
  • Es handelt sich um eine Lese-/Schreibvariable innerhalb der Unterprogramme. Ihre Werte können innerhalb der Unterprogramme geändert werden.
  • In der aufrufenden Anweisung sollten diese Parameter immer eine Variable sein, die den Wert aus den aktuellen Unterprogrammen enthält.

IN OUT-Parameter

  • Dieser Parameter wird sowohl für die Eingabe als auch für den Erhalt der Ausgabe von den Unterprogrammen verwendet.
  • Es handelt sich um eine Lese-/Schreibvariable innerhalb der Unterprogramme. Ihre Werte können innerhalb der Unterprogramme geändert werden.
  • In der aufrufenden Anweisung sollten diese Parameter immer eine Variable sein, um den Wert aus den Unterprogrammen zu speichern.

Diese Parametertypen sollten zum Zeitpunkt der Erstellung der Unterprogramme angegeben werden.

RÜCKKEHR

RETURN ist das Schlüsselwort, das den Compiler anweist, die Steuerung vom Unterprogramm auf die aufrufende Anweisung umzustellen. Im Unterprogramm bedeutet RETURN einfach, dass die Steuerung das Unterprogramm verlassen muss. Sobald der Controller das Schlüsselwort RETURN im Unterprogramm findet, wird der Code danach übersprungen.

Normalerweise ruft der übergeordnete oder Hauptblock die Unterprogramme auf, und dann wechselt die Steuerung von diesem übergeordneten Block zu den aufgerufenen Unterprogrammen. RETURN im Unterprogramm gibt die Steuerung an den übergeordneten Block zurück. Bei Funktionen gibt die RETURN-Anweisung auch den Wert zurück. Der Datentyp dieses Werts wird zum Zeitpunkt der Funktionsdeklaration immer angegeben. Der Datentyp kann jeder gültige PL/SQL-Datentyp sein.

Was ist eine Prozedur in PL/SQL?

A Verfahren in PL/SQL ist eine Unterprogrammeinheit, die aus einer Gruppe von PL/SQL-Anweisungen besteht, die namentlich aufgerufen werden können. Jede Prozedur in PL/SQL hat ihren eigenen eindeutigen Namen, mit dem sie angesprochen und aufgerufen werden kann. Diese Unterprogrammeinheit in der Oracle Die Datenbank wird als Datenbankobjekt gespeichert.

Hinweis: Das Unterprogramm ist nichts anderes als eine Prozedur und muss je nach Anforderung manuell erstellt werden. Nach der Erstellung werden sie als Datenbankobjekte gespeichert.

Nachfolgend sind die Merkmale der Prozedur-Unterprogrammeinheit in PL/SQL aufgeführt:

  • Prozeduren sind eigenständige Blöcke eines Programms, die im gespeichert werden können Datenbank.
  • Der Aufruf dieser PLSQL-Prozeduren kann unter Bezugnahme auf ihren Namen erfolgen, um die PL/SQL-Anweisungen auszuführen.
  • Es wird hauptsächlich zum Ausführen eines Prozesses in PL/SQL verwendet.
  • Es kann verschachtelte Blöcke enthalten oder innerhalb der anderen Blöcke oder Pakete definiert und verschachtelt sein.
  • Es enthält einen Deklarationsteil (optional), einen Ausführungsteil und einen Ausnahmebehandlungsteil (optional).
  • Die Werte können übergeben werden Oracle Prozedur oder durch Parameter aus der Prozedur abgerufen.
  • Diese Parameter sollten in der aufrufenden Anweisung enthalten sein.
  • Eine Prozedur in SQL kann eine RETURN-Anweisung haben, um die Kontrolle an den aufrufenden Block zurückzugeben, sie kann jedoch keine Werte über die RETURN-Anweisung zurückgeben.
  • Prozeduren können nicht direkt aus SELECT-Anweisungen aufgerufen werden. Sie können von einem anderen Block oder über das Schlüsselwort EXEC aufgerufen werden.

Syntax

CREATE OR REPLACE PROCEDURE 
<procedure_name>
	(
	<parameterl IN/OUT <datatype>
	..
	.
	)
[ IS | AS ]
	<declaration_part>
BEGIN
	<execution part>
EXCEPTION
	<exception handling part>
END;
  • CREATE PROCEDURE weist den Compiler an, eine neue Prozedur zu erstellen Oracle. Das Schlüsselwort „OR REPLACE“ weist die Kompilierung an, die vorhandene Prozedur (falls vorhanden) durch die aktuelle zu ersetzen.
  • Der Prozedurname sollte eindeutig sein.
  • Das Schlüsselwort „IS“ wird verwendet, wenn die gespeicherte Prozedur ausgeführt wird Oracle ist in einige andere Blöcke verschachtelt. Wenn das Verfahren eigenständig ist, wird „AS“ verwendet. Abgesehen von diesem Codierungsstandard haben beide die gleiche Bedeutung.

Beispiel 1: Prozedur erstellen und mit EXEC aufrufen

In diesem Beispiel erstellen wir eine Oracle Prozedur, die den Namen als Eingabe verwendet und die Begrüßungsnachricht als Ausgabe ausgibt. Wir werden den EXEC-Befehl verwenden, um die Prozedur aufzurufen.

CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2) 
IS
BEGIN
dbms_output.put_line (‘Welcome '|| p_name);
END;
/
EXEC welcome_msg (‘Guru99’);

Code-Erklärung:

  • Codezeile 1: Erstellen der Prozedur mit dem Namen „welcome_msg“ und einem Parameter „p_name“ vom Typ „IN“.
  • Codezeile 4: Drucken der Willkommensnachricht durch Verketten des Eingabenamens.
  • Die Prozedur wurde erfolgreich kompiliert.
  • Codezeile 7: Aufruf der Prozedur mit dem EXEC-Befehl mit dem Parameter „Guru99“. Der Vorgang wird ausgeführt und die Nachricht wird als „Welcome Guru99“ ausgedruckt.

Was ist Funktion?

Functions ist ein eigenständiges PL/SQL-Unterprogramm. Wie PL/SQL-Prozeduren haben Funktionen einen eindeutigen Namen, mit dem sie referenziert werden können. Diese werden als PL/SQL-Datenbankobjekte gespeichert. Nachfolgend sind einige Merkmale der Funktionen aufgeführt.

  • Funktionen sind eigenständige Blöcke, die hauptsächlich zu Berechnungszwecken verwendet werden.
  • Die Funktion verwendet das Schlüsselwort RETURN, um den Wert zurückzugeben, und der Datentyp hierfür wird zum Zeitpunkt der Erstellung definiert.
  • Eine Funktion sollte entweder einen Wert zurückgeben oder die Ausnahme auslösen, dh die Rückgabe ist in Funktionen obligatorisch.
  • Funktionen ohne DML-Anweisungen können direkt in einer SELECT-Abfrage aufgerufen werden, während die Funktion mit DML-Operation nur von anderen PL/SQL-Blöcken aufgerufen werden kann.
  • Es kann verschachtelte Blöcke enthalten oder innerhalb der anderen Blöcke oder Pakete definiert und verschachtelt sein.
  • Es enthält einen Deklarationsteil (optional), einen Ausführungsteil und einen Ausnahmebehandlungsteil (optional).
  • Die Werte können an die Funktion übergeben oder über die Parameter aus der Prozedur abgerufen werden.
  • Diese Parameter sollten in der aufrufenden Anweisung enthalten sein.
  • Eine PLSQL-Funktion kann den Wert auch über OUT-Parameter zurückgeben, außer über RETURN.
  • Da immer der Wert zurückgegeben wird, wird die aufrufende Anweisung immer von einem Zuweisungsoperator begleitet, um die Variablen zu füllen.

Funktionen in PL/SQL

Syntax

CREATE OR REPLACE FUNCTION 
<procedure_name>
(
<parameterl IN/OUT <datatype>
)
RETURN <datatype>
[ IS | AS ]
<declaration_part>
BEGIN
<execution part> 
EXCEPTION
<exception handling part>
END;
  • CREATE FUNCTION weist den Compiler an, eine neue Funktion zu erstellen. Das Schlüsselwort „OR REPLACE“ weist den Compiler an, die vorhandene Funktion (falls vorhanden) durch die aktuelle zu ersetzen.
  • Der Funktionsname sollte eindeutig sein.
  • Der RETURN-Datentyp sollte erwähnt werden.
  • Das Schlüsselwort „IS“ wird verwendet, wenn die Prozedur in andere Blöcke eingebettet ist. Wenn das Verfahren eigenständig ist, wird „AS“ verwendet. Abgesehen von diesem Codierungsstandard haben beide die gleiche Bedeutung.

Beispiel 1: Funktion erstellen und mit Anonymous Block aufrufen

In diesem Programm erstellen wir eine Funktion, die den Namen als Eingabe verwendet und die Willkommensnachricht als Ausgabe zurückgibt. Wir werden einen anonymen Block und eine Select-Anweisung verwenden, um die Funktion aufzurufen.

Funktionen in PL/SQL

CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2
IS
BEGIN
RETURN (‘Welcome ‘|| p_name);
END;
/
DECLARE
lv_msg VARCHAR2(250);
BEGIN
lv_msg := welcome_msg_func (‘Guru99’);
dbms_output.put_line(lv_msg);
END;
SELECT welcome_msg_func(‘Guru99:) FROM DUAL;

Code-Erklärung:

  • Codezeile 1: Erstellen der Oracle Funktion mit dem Namen „welcome_msg_func“ und einem Parameter „p_name“ vom Typ „IN“.
  • Codezeile 2: Deklaration des Rückgabetyps als VARCHAR2
  • Codezeile 5: Gibt den verketteten Wert „Willkommen“ und den Parameterwert zurück.
  • Codezeile 8: Anonymer Block zum Aufrufen der obigen Funktion.
  • Codezeile 9: Deklarieren der Variablen mit dem gleichen Datentyp wie der Rückgabedatentyp der Funktion.
  • Codezeile 11: Rufen Sie die Funktion auf und füllen Sie den Rückgabewert in die Variable „lv_msg“ ein.
  • Codezeile 12: Drucken des Variablenwerts. Die Ausgabe, die Sie hier erhalten, ist „Welcome Guru99“.
  • Codezeile 14: Aufruf derselben Funktion über die SELECT-Anweisung. Der Rückgabewert wird direkt an die Standardausgabe weitergeleitet.

Ähnlichkeiten zwischen Verfahren und Funktion

  • Beide können von anderen PL/SQL-Blöcken aufgerufen werden.
  • Wenn die im Unterprogramm ausgelöste Ausnahme nicht im Unterprogramm behandelt wird Ausnahmebehandlung Abschnitt, dann wird es an den aufrufenden Block weitergegeben.
  • Beide können beliebig viele Parameter haben.
  • Beide werden in PL/SQL als Datenbankobjekte behandelt.

Verfahren vs. Funktion: Hauptunterschiede

Verfahren Funktion
Wird hauptsächlich zur Ausführung eines bestimmten Prozesses verwendet Wird hauptsächlich zur Durchführung einiger Berechnungen verwendet
Aufruf in SELECT-Anweisung nicht möglich Eine Funktion, die keine DML-Anweisungen enthält, kann in der SELECT-Anweisung aufgerufen werden
Verwenden Sie den OUT-Parameter, um den Wert zurückzugeben Verwenden Sie RETURN, um den Wert zurückzugeben
Es ist nicht zwingend erforderlich, den Wert zurückzugeben Es ist zwingend erforderlich, den Wert zurückzugeben
RETURN beendet einfach die Steuerung des Unterprogramms. RETURN verlässt die Steuerung aus dem Unterprogramm und gibt auch den Wert zurück
Der Rückgabedatentyp wird zum Zeitpunkt der Erstellung nicht angegeben Der Rückgabedatentyp ist zum Zeitpunkt der Erstellung obligatorisch

Integrierte Funktionen in PL/SQL

PL / SQL enthält verschiedene integrierte Funktionen zum Arbeiten mit Zeichenfolgen und Datumsdatentypen. Hier sehen wir uns die häufig verwendeten Funktionen und deren Verwendung an.

Konvertierungsfunktionen

Diese integrierten Funktionen werden verwendet, um einen Datentyp in einen anderen Datentyp zu konvertieren.

Funktionsname Anwendungsbereich Beispiel
TO_CHAR Konvertiert den anderen Datentyp in einen Zeichendatentyp TO_CHAR(123);
TO_DATE (Zeichenfolge, Format) Konvertiert die angegebene Zeichenfolge in ein Datum. Die Zeichenfolge sollte mit dem Format übereinstimmen.

TO_DATE('2015-JAN-15', 'YYYY-MON-DD');

Output: 1 / 15 / 2015

TO_NUMBER (Text, Format)

Konvertiert den Text in den Zahlentyp des angegebenen Formats.

Informat '9' gibt die Anzahl der Ziffern an

Wählen Sie TO_NUMBER('1234′,'9999') aus Dual;

Output: 1234

Wählen Sie TO_NUMBER('1,234.45′,'9,999.99') aus Dual;

Output: 1234

String-Funktionen

Dies sind die Funktionen, die für den Zeichendatentyp verwendet werden.

Funktionsname Anwendungsbereich Beispiel
INSTR(Text, String, Start, Vorkommen) Gibt die Position eines bestimmten Textes in der angegebenen Zeichenfolge an.

  • text – Hauptzeichenfolge
  • string – Text, der durchsucht werden muss
  • start – Startposition der Suche (optional)
  • Übereinstimmung – Vorkommen der gesuchten Zeichenfolge (optional)
Wählen Sie INSTR('AEROPLANE','E',2,1) aus dual

Output: 2

Wählen Sie INSTR('AEROPLANE','E',2,2) aus dual

Output: 9 (2nd Vorkommen von E)

SUBSTR (Text, Anfang, Länge) Gibt den Teilstringwert des Hauptstrings an.

  • Text – Hauptzeichenfolge
  • Start – Ausgangsposition
  • Länge – Länge der Unterzeichenfolge
Wählen Sie substr('aeroplane',1,7) aus dual aus

Output: Aeropla

UPPER (Text) Gibt den Großbuchstaben des bereitgestellten Textes zurück Wählen Sie Upper('guru99') aus Dual;

Output: GURU99

UNTERE (Text) Gibt den Kleinbuchstaben des bereitgestellten Textes zurück Wählen Sie unter „Dual“ die untere Option („AerOpLane“).

Output: Flugzeug

INITCAP (Text) Gibt den angegebenen Text mit dem Anfangsbuchstaben in Großbuchstaben zurück. Wählen Sie ('guru99') aus Dual

Output: Guru99

Wählen Sie ('meine Geschichte') aus Dual

Output: Meine Geschichte

LÄNGE (Text) Gibt die Länge der angegebenen Zeichenfolge zurück Wählen Sie LÄNGE ('guru99') aus Dual;

Output: 6

LPAD (Text, Länge, pad_char) Füllt die Zeichenfolge auf der linken Seite für die angegebene Länge (Gesamtzeichenfolge) mit dem angegebenen Zeichen auf Wählen Sie LPAD('guru99', 10, '$') aus dual;

Output: $$$$guru99

RPAD (Text, Länge, pad_char) Füllt die Zeichenfolge auf der rechten Seite für die angegebene Länge (Gesamtzeichenfolge) mit dem angegebenen Zeichen auf Wählen Sie RPAD('guru99′,10,'-') aus Dual aus

Output: guru99—-

LTRIM (Text) Entfernt den führenden Leerraum aus dem Text Wählen Sie LTRIM(' Guru99') aus Dual;

Output: Guru99

RTRIM (Text) Entfernt den Leerraum am Ende des Textes Wählen Sie RTRIM('Guru99') aus Dual;

Output; Guru99

Datumsfunktionen

Dies sind Funktionen, die zur Manipulation von Datumsangaben verwendet werden.

Funktionsname Anwendungsbereich Beispiel
ADD_MONTHS (Datum, Anzahl Monate) Fügt dem Datum die angegebenen Monate hinzu ADD_MONTH('2015-01-01',5);

Output: 05 / 01 / 2015

SYSDATUM Gibt das aktuelle Datum und die aktuelle Uhrzeit des Servers zurück Wählen Sie SYSDATE aus Dual;

Output: 10 4:2015:2 Uhr

TRUNC Runden Sie die Datumsvariable auf den niedrigeren möglichen Wert select sysdate, TRUNC(sysdate) from dual;

Output: 10 4:2015:2 12

ROUND Rundet das Datum auf nearesEs gibt weder eine höhere noch eine niedrigere Grenze Wählen Sie „Sysdate“, „ROUND(Sysdate)“ aus „Dual“.

Output: 10 4:2015:2 14

MONATE_BETWEEN Gibt die Anzahl der Monate zwischen zwei Daten zurück Wählen Sie MONTHS_BETWEEN (Sysdate+60, Sysdate) aus Dual

Output: 2

Zusammenfassung

In diesem Kapitel haben wir Folgendes gelerntwing.

  • So erstellen Sie eine Prozedur und verschiedene Möglichkeiten, sie aufzurufen
  • So erstellen Sie eine Funktion und verschiedene Arten, sie aufzurufen
  • Ähnlichkeiten und Unterschiede zwischen Verfahren und Funktion
  • Allgemeine Terminologien für Parameter und RETURN in PL/SQL-Unterprogrammen
  • Gemeinsame integrierte Funktionen in Oracle PL / SQL