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 diesen übergeordneten Blöcken 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 immer zum Zeitpunkt der Funktionsdeklaration angegeben. Der Datentyp kann jeder gültige PL/SQL-Datentyp sein.

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 der SELECT-Abfrage aufgerufen werden, während Funktionen mit DML-Operationen nur von anderen PL/SQL-Blöcken aufgerufen werden können.
• 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 der Wert immer zurückgegeben wird, wird er in aufrufenden Anweisungen immer von einem Zuweisungsoperator begleitet, um die Variablen aufzufüllen.

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.

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

Ablauf	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'); Ausgang: 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; Ausgang: 1234 Wählen Sie TO_NUMBER('1,234.45′,'9,999.99') aus Dual; Ausgang: 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 Ausgang: 2 Wählen Sie INSTR('AEROPLANE','E',2,2) aus dual Ausgang: 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 Ausgang: Aeropla
UPPER (Text)	Gibt den Großbuchstaben des bereitgestellten Textes zurück	Wählen Sie Upper('guru99') aus Dual; Ausgang: GURU99
UNTERE (Text)	Gibt den Kleinbuchstaben des bereitgestellten Textes zurück	Wählen Sie unter „Dual“ die untere Option („AerOpLane“). Ausgang: Flugzeug
INITCAP (Text)	Gibt den angegebenen Text mit dem Anfangsbuchstaben in Großbuchstaben zurück.	Wählen Sie ('guru99') aus Dual Ausgang: Guru99 Wählen Sie ('meine Geschichte') aus Dual Ausgang: Meine Geschichte
LÄNGE (Text)	Gibt die Länge der angegebenen Zeichenfolge zurück	Wählen Sie LÄNGE ('guru99') aus Dual; Ausgang: 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; Ausgang: $$$$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 Ausgang: guru99—-
LTRIM (Text)	Entfernt den führenden Leerraum aus dem Text	Wählen Sie LTRIM(' Guru99') aus Dual; Ausgang: Guru99
RTRIM (Text)	Entfernt den Leerraum am Ende des Textes	Wählen Sie RTRIM('Guru99') aus Dual; Ausgang; 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); Ausgang: 05 / 01 / 2015
SYSDATUM	Gibt das aktuelle Datum und die aktuelle Uhrzeit des Servers zurück	Wählen Sie SYSDATE aus Dual; Ausgang: 10 4:2015:2 Uhr
TRUNC	Runden Sie die Datumsvariable auf den niedrigeren möglichen Wert	select sysdate, TRUNC(sysdate) from dual; Ausgang: 10 4:2015:2 12
ROUND	Rundet das Datum auf die nächste Grenze, entweder höher oder niedriger.	Wählen Sie „Sysdate“, „ROUND(Sysdate)“ aus „Dual“. Ausgang: 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 Ausgang: 2

Zusammenfassung

In diesem Kapitel haben wir Folgendes gelernt.

• 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