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

Was sind PL/SQL-Unterprogramme?

In diesem Tutorial finden Sie eine detaillierte Beschreibung, wie Sie die benannten Blöcke, Prozeduren und Funktionen erstellen und ausführen.

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

Wir behandeln auch die wesentlichen Unterschiede zwischen diesen beiden Teilprogrammen und diskutieren die Oracle Integrierte Funktionen.

Terminologien in PL/SQL-Unterprogrammen

Bevor wir uns mit PL/SQL-Unterprogrammen befassen, besprechen wir die verschiedenen Fachbegriffe, die Bestandteil dieser Unterprogramme sind.

Parameter

Ein Parameter ist eine Variable oder ein Platzhalter für beliebige gültige Werte. PL/SQL-Datentyp Über diesen Parameter tauscht das PL/SQL-Unterprogramm Werte mit dem Hauptcode aus. Dieser Parameter ermöglicht die Eingabe in die Unterprogramme und die Ausgabe von Daten.traction von Werten aus ihnen.

• Diese Parameter sollten zusammen mit den Unterprogrammen zum Zeitpunkt der Erstellung definiert werden.
• Sie sind in der Aufrufanweisung enthalten, um mit den Unterprogrammen zu interagieren.
• Der Datentyp des Parameters im Unterprogramm und in der aufrufenden Anweisung muss derselbe sein.
• Die Größe des Datentyps sollte nicht bei der Parameterdeklaration angegeben werden, da die Größe dynamisch ist.

Je nach ihrem Zweck werden die Parameter wie folgt klassifiziert:

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

IN-Parameter

• Wird zur Eingabe an die Unterprogramme verwendet.
• Innerhalb der Unterprogramme handelt es sich um eine schreibgeschützte Variable; ihr Wert kann innerhalb des Unterprogramms nicht geändert werden.
• Im aufrufenden Code kann es sich um eine Variable, einen Literalwert oder einen Ausdruck handeln, wie zum Beispiel '5*8' oder 'a/b'.
• Standardmäßig sind die Parameter vom Typ IN.

OUT-Parameter

• Wird verwendet, um Ausgaben von den Unterprogrammen zu erhalten.
• Innerhalb der Unterprogramme handelt es sich um eine les- und schreibbare Variable; ihr Wert kann innerhalb dieser geändert werden.
• In der aufrufenden Anweisung sollte es sich immer um eine Variable handeln, die den Wert aus dem Unterprogramm aufnimmt.

IN OUT-Parameter

• Wird sowohl für die Eingabe als auch für die Ausgabe von Unterprogrammen verwendet.
• Innerhalb der Unterprogramme handelt es sich um eine les- und schreibbare Variable; ihr Wert kann innerhalb dieser geändert werden.
• In der aufrufenden Anweisung sollte es sich immer um eine Variable handeln, die den Wert aus dem Unterprogramm aufnimmt.

Der Parametertyp sollte beim Erstellen der Unterprogramme angegeben werden.

RÜCKKEHR

Das Schlüsselwort RETURN weist den Compiler an, die Programmausführung vom Unterprogramm zur aufrufenden Anweisung zu lenken. Innerhalb eines Unterprogramms bedeutet RETURN, dass das Unterprogramm verlassen werden muss; sobald der Compiler auf RETURN stößt, wird der nachfolgende Code übersprungen.

Normalerweise ruft der Hauptblock die Unterprogramme auf, und die Programmausführung wechselt vom Hauptblock zum aufgerufenen Unterprogramm. Mit dem Befehl `RETURN` im Unterprogramm wird die Programmausführung an den Hauptblock zurückgegeben. Bei Funktionen gibt die `RETURN`-Anweisung ebenfalls einen Wert zurück, dessen Datentyp bei der Funktionsdeklaration angegeben wird.

Was ist eine Prozedur in PL/SQL?

A Ablauf In PL/SQL ist eine Prozedur eine Unterprogrammeinheit, die aus einer Gruppe von PL/SQL-Anweisungen besteht, die über ihren Namen aufgerufen werden können. Jede Prozedur hat einen eigenen, eindeutigen Namen und wird im Dateisystem gespeichert. Oracle Datenbank als Datenbankobjekt.

Hinweis: Ein Unterprogramm ist nichts anderes als eine Prozedur und muss je nach Bedarf manuell erstellt werden. Nach der Erstellung wird es als Datenbankobjekt gespeichert.

Die Merkmale einer Prozedur-Unterprogrammeinheit in PL/SQL sind:

• Prozeduren sind eigenständige Blöcke, die in der Datenbank.
• Sie können über ihren Namen aufgerufen werden, um die PL/SQL-Anweisungen auszuführen.
• Sie werden hauptsächlich zur Ausführung eines Prozesses verwendet.
• Sie können verschachtelte Blöcke enthalten oder in anderen Blöcken oder Paketen verschachtelt sein.
• Sie enthalten einen Deklarationsteil (optional), einen Ausführungsteil und einen Ausnahmebehandlungsteil (optional).
• Werte können über Parameter an eine Prozedur übergeben oder von ihr abgerufen werden.
• Diese Parameter sollten in der aufrufenden Anweisung enthalten sein.
• Eine Prozedur kann eine RETURN-Anweisung haben, um die Kontrolle an den aufrufenden Block zurückzugeben, aber sie kann keinen Wert über RETURN zurückgeben.
• Prozeduren können nicht direkt aus SELECT-Anweisungen aufgerufen werden; sie können aus einem anderen Block oder über das Schlüsselwort EXEC aufgerufen werden.

Syntax

CREATE OR REPLACE PROCEDURE
<procedure_name>
(
<parameter1 IN/OUT <datatype>
..
.
)
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• Der Befehl CREATE PROCEDURE weist den Compiler an, eine neue Prozedur zu erstellen. Das Schlüsselwort 'OR REPLACE' bewirkt, dass die vorhandene Prozedur (falls vorhanden) durch die neue ersetzt wird.
• Der Prozedurname sollte eindeutig sein.
• Das Schlüsselwort „IS“ wird verwendet, wenn die gespeicherte Prozedur in einem anderen Block verschachtelt ist. Ist die Prozedur eigenständig, wird „AS“ verwendet. Abgesehen von diesem Codierungsstandard haben beide die gleiche Bedeutung.

Beispiel 1: Erstellen einer Prozedur und deren Aufruf mit EXEC. In diesem Beispiel erstellen wir ein Oracle Prozedur, die einen Namen als Eingabe entgegennimmt und eine Willkommensnachricht ausgibt; aufgerufen wird sie über den Befehl EXEC.

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 Erläuterung:

• Code Zeile 1: Die Prozedur mit dem Namen 'welcome_msg' und einem Parameter 'p_name' vom Typ 'IN' wird erstellt.
• Code Zeile 4: Die Begrüßungsnachricht wird durch Verkettung des Eingabenamens ausgegeben.
• Die Prozedur wurde erfolgreich kompiliert.
• Code Zeile 7: Aufruf der Prozedur mit EXEC und dem Parameter 'Guru99'. Die Prozedur wird ausgeführt und gibt „Willkommen“ aus. Guru99 ".

Was ist eine Funktion?

Eine Funktion ist ein eigenständiges PL/SQL-Unterprogramm. Wie eine Prozedur hat eine Funktion einen eindeutigen Namen und wird als PL/SQL-Datenbankobjekt gespeichert. Ihre Eigenschaften sind:

• Funktionen sind eigenständige Bausteine, die hauptsächlich für Berechnungen verwendet werden.
• Eine Funktion verwendet das Schlüsselwort RETURN, um einen Wert zurückzugeben, dessen Datentyp zum Zeitpunkt der Erstellung definiert wird.
• Eine Funktion sollte entweder einen Wert zurückgeben oder eine Ausnahme auslösen; return ist in Funktionen obligatorisch.
• Eine Funktion ohne DML-Anweisungen kann direkt in einer SELECT-Abfrage aufgerufen werden, wohingegen eine Funktion mit DML nur aus anderen PL/SQL-Blöcken aufgerufen werden kann.
• Es kann verschachtelte Blöcke enthalten oder in anderen Blöcken oder Paketen verschachtelt sein.
• Es enthält einen Deklarationsteil (optional), einen Ausführungsteil und einen Ausnahmebehandlungsteil (optional).
• Werte können über Parameter an die Funktion übergeben oder von ihr abgerufen werden.
• Diese Parameter sollten in der aufrufenden Anweisung enthalten sein.
• Eine Funktion kann neben der Verwendung von RETURN auch über OUT-Parameter einen Wert zurückgeben.
• Da die aufrufende Anweisung immer einen Wert zurückgibt, verwendet sie stets einen Zuweisungsoperator, um eine Variable zu belegen.

Syntax

CREATE OR REPLACE FUNCTION
<function_name>
(
<parameter1 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. 'OR REPLACE' weist ihn an, die vorhandene Funktion (falls vorhanden) durch die neue zu ersetzen.
• Der Funktionsname sollte eindeutig sein.
• Der Datentyp RETURN sollte angegeben werden.
• Das Schlüsselwort „IS“ wird verwendet, wenn die Funktion in einem anderen Block verschachtelt ist. Ist die Funktion eigenständig, wird „AS“ verwendet.

Beispiel 1: Erstellen einer Funktion und deren Aufruf mithilfe eines anonymen Blocks. In diesem Programm erstellen wir eine Funktion, die einen Namen als Eingabe entgegennimmt und eine Willkommensnachricht zurückgibt. Dazu verwenden wir einen anonymen Block und eine SELECT-Anweisung, um sie aufzurufen.

CREATE OR REPLACE FUNCTION welcome_msg_func ( p_name IN VARCHAR2) RETURN VARCHAR2
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 Erläuterung:

• Code Zeile 1: Es wird eine Funktion mit dem Namen 'welcome_msg_func' und einem Parameter 'p_name' vom Typ 'IN' erstellt.
• Code Zeile 2: Deklaration des Rückgabetyps als VARCHAR2.
• Code Zeile 5: Gibt den verketteten Wert 'Willkommen' und den Parameterwert zurück.
• Code Zeile 8: Anonymer Block zum Aufruf der obigen Funktion.
• Code Zeile 9: Die Variable wird mit demselben Datentyp wie der Rückgabetyp der Funktion deklariert.
• Code Zeile 11: Die Funktion wird aufgerufen und der Rückgabewert in der Variablen 'lv_msg' gespeichert.
• Code Zeile 12: Der Wert der Variablen wird ausgegeben. Die Ausgabe lautet: „Willkommen“ Guru99 ".
• Code Zeile 14: Dieselbe Funktion wird über eine SELECT-Anweisung aufgerufen. Der Rückgabewert wird an die Standardausgabe weitergeleitet.

Ähnlichkeiten zwischen einer Prozedur und einer Funktion

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

Prozedur vs. Funktion: Wesentliche Unterschiede

Integrierte Funktionen in PL/SQL

PL / SQL Enthält verschiedene integrierte Funktionen zur Bearbeitung von Zeichenketten- und Datumsdaten. Hier sehen wir die häufig verwendeten Funktionen und deren Anwendung.

Konvertierungsfunktionen

Diese integrierten Funktionen wandeln einen Datentyp in einen anderen um.

String-Funktionen

Diese Funktionen werden auf den Datentyp Zeichen angewendet.

Datumsfunktionen

Diese Funktionen dienen der Datumsmanipulation.

Häufig gestellte Fragen