In diesem Tutorial finden Sie eine detaillierte Beschreibung zum Erstellen und Ausführen der benannten Blöcke (Prozeduren und Funktionen).
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.
Abgesehen davon werden wir die Hauptunterschiede zwischen diesen beiden Unterprogrammen behandeln. Außerdem werden wir die in Oracle integrierten Funktionen diskutieren.
In diesem Tutorial zu Oracle Stored Procedure lernen Sie:
- Terminologien in PL / SQL-Unterprogrammen
- Was ist die Prozedur in PL / SQL?
- Was ist Funktion?
- Ähnlichkeiten zwischen Prozedur und Funktion
- Verfahren Vs. Funktion: Schlüsseldifferenzen
- Integrierte Funktionen in PL / SQL
Terminologien in PL / SQL-Unterprogrammen
Bevor wir uns mit PL / SQL-Unterprogrammen befassen, werden wir die verschiedenen Terminologien diskutieren, die Teil dieser Unterprogramme sind. Nachfolgend finden Sie die Terminologien, die wir diskutieren werden.
Parameter:
Der Parameter ist eine Variable oder ein Platzhalter eines gültigen PL / SQL-Datentyps, über den das PL / SQL-Unterprogramm die Werte mit dem Hauptcode austauscht. Dieser Parameter ermöglicht es, Eingaben in die Unterprogramme zu geben und aus diesen Unterprogrammen zu extrahieren.
- Diese Parameter sollten zum Zeitpunkt der Erstellung zusammen mit den Unterprogrammen definiert werden.
- Diese Parameter sind in der aufrufenden Anweisung dieser Unterprogramme enthalten, um die Werte mit den Unterprogrammen zu interagieren.
- Der Datentyp des Parameters im Unterprogramm und die aufrufende Anweisung sollten identisch sein.
- Die Größe des Datentyps sollte zum Zeitpunkt der Parameterdeklaration nicht erwähnt werden, da die Größe für diesen Typ dynamisch ist.
Aufgrund ihres Verwendungszwecks werden Parameter als klassifiziert
- IN-Parameter
- OUT-Parameter
- IN OUT Parameter
IN Parameter:
- Dieser Parameter wird zur Eingabe der Unterprogramme verwendet.
- Es ist eine schreibgeschützte Variable innerhalb der Unterprogramme. Ihre Werte können im Unterprogramm nicht geändert werden.
- In der aufrufenden Anweisung können diese Parameter eine Variable oder ein Literalwert oder ein Ausdruck sein. Beispielsweise kann es sich um den 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 aus den Unterprogrammen abzurufen.
- Es ist 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 aktuellen Unterprogrammen zu speichern.
IN OUT Parameter:
- Dieser Parameter wird sowohl zum Eingeben als auch zum Abrufen von Ausgaben aus den Unterprogrammen verwendet.
- Es ist 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.
Dieser Parametertyp sollte zum Zeitpunkt der Erstellung der Unterprogramme erwähnt werden.
RÜCKKEHR
RETURN ist das Schlüsselwort, das den Compiler anweist, das Steuerelement vom Unterprogramm in die aufrufende Anweisung umzuschalten. Im Unterprogramm bedeutet RETURN einfach, dass die Steuerung das Unterprogramm verlassen muss. Sobald der Controller das Schlüsselwort RETURN im Unterprogramm gefunden hat, wird der darauf folgende Code ü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 ihren übergeordneten Block zurück. Bei Funktionen gibt die Anweisung RETURN auch den Wert zurück. Der Datentyp dieses Werts wird immer zum Zeitpunkt der Funktionsdeklaration angegeben. Der Datentyp kann ein beliebiger gültiger PL / SQL-Datentyp sein.
Was ist die Prozedur in PL / SQL?
Eine Prozedur in PL / SQL ist eine Unterprogrammeinheit, die aus einer Gruppe von PL / SQL-Anweisungen besteht, die beim Namen aufgerufen werden können. Jede Prozedur in PL / SQL hat einen eigenen eindeutigen Namen, unter dem sie referenziert und aufgerufen werden kann. Diese Unterprogrammeinheit in der Oracle-Datenbank wird als Datenbankobjekt gespeichert.
Hinweis: Das Unterprogramm ist nichts anderes als eine Prozedur und muss gemäß den Anforderungen manuell erstellt werden. Einmal erstellt, 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 in der Datenbank gespeichert werden können.
- Der Aufruf dieser PLSQL-Prozeduren kann unter Bezugnahme auf ihren Namen erfolgen, um die PL / SQL-Anweisungen auszuführen.
- Es wird hauptsächlich verwendet, um einen Prozess in PL / SQL auszuführen.
- Es kann verschachtelte Blöcke haben oder in den anderen Blöcken oder Paketen definiert und verschachtelt sein.
- Es enthält einen Deklarationsteil (optional), einen Ausführungsteil und einen Ausnahmebehandlungsteil (optional).
- Die Werte können an die Oracle-Prozedur übergeben oder über Parameter aus der Prozedur abgerufen werden.
- Diese Parameter sollten in der aufrufenden Anweisung enthalten sein.
- Eine Prozedur in SQL kann eine RETURN-Anweisung haben, um das Steuerelement 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 EXEC-Schlüsselwort aufgerufen werden.
Syntax:
CREATE OR REPLACE PROCEDURE( … )[ IS | AS ] BEGIN EXCEPTION END;
- CREATE PROCEDURE weist den Compiler an, eine neue Prozedur in Oracle zu erstellen. 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 in Oracle gespeicherte Prozedur in einige andere Blöcke verschachtelt ist. Wenn die Prozedur 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 druckt. Wir werden den EXEC-Befehl verwenden, um die Prozedur aufzurufen.
CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)ISBEGINdbms_output.put_line (‘Welcome '|| p_name);END;/EXEC welcome_msg (‘Guru99’);
Code Erläuterung:
- Codezeile 1 : Erstellen der Prozedur mit dem Namen 'welcome_msg' und einem Parameter 'p_name' vom Typ 'IN'.
- Codezeile 4 : Drucken der Begrüßungsnachricht durch Verketten des Eingabenamens.
- Prozedur wurde erfolgreich kompiliert.
- Codezeile 7 : Aufrufen der Prozedur mit dem Befehl EXEC mit dem Parameter 'Guru99'. Die Prozedur wird ausgeführt und die Nachricht als "Welcome Guru99" ausgedruckt.
Was ist Funktion?
Functions ist ein eigenständiges PL / SQL-Unterprogramm. Wie die PL / SQL-Prozedur haben Funktionen einen eindeutigen Namen, unter dem sie referenziert werden können. Diese werden als PL / SQL-Datenbankobjekte gespeichert. Nachfolgend sind einige Merkmale von Funktionen aufgeführt.
- Funktionen sind ein eigenständiger Block, der hauptsächlich zu Berechnungszwecken verwendet wird.
- Funktion Verwenden Sie 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 die Funktion mit DML-Operation nur von anderen PL / SQL-Blöcken aufgerufen werden kann.
- Es kann verschachtelte Blöcke haben oder in den anderen Blöcken oder Paketen 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 andere OUT-Parameter als die Verwendung von RETURN zurückgeben.
- Da der Wert immer zurückgegeben wird, wird beim Aufrufen der Anweisung immer ein Zuweisungsoperator zum Auffüllen der Variablen verwendet.
Syntax
CREATE OR REPLACE FUNCTION( )RETURN [ IS | AS ] BEGIN EXCEPTION 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 Datentyp RETURN sollte erwähnt werden.
- Das Schlüsselwort 'IS' wird verwendet, wenn die Prozedur in einige andere Blöcke verschachtelt ist. Wenn die Prozedur eigenständig ist, wird 'AS' verwendet. Abgesehen von diesem Codierungsstandard haben beide die gleiche Bedeutung.
Beispiel 1: Erstellen einer Funktion und Aufrufen mit dem anonymen Block
In diesem Programm erstellen wir eine Funktion, die den Namen als Eingabe verwendet und die Begrüßungsnachricht 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.CHAR2ISBEGINRETURN (‘Welcome ‘|| p_name);END;/DECLARElv_msg VARCHAR2(250);BEGINlv_msg := welcome_msg_func (‘Guru99’);dbms_output.put_line(lv_msg);END;SELECT welcome_msg_func(‘Guru99:) FROM DUAL;
Code Erläuterung:
- Codezeile 1 : Erstellen der Oracle-Funktion mit dem Namen 'welcome_msg_func' und einem Parameter 'p_name' vom Typ 'IN'.
- Codezeile 2 : Deklarieren des Rückgabetyps als VARCHAR2
- Codezeile 5 : Rückgabe des verketteten Werts 'Welcome' und des Parameterwerts.
- Codezeile 8 : Anonymer Block zum Aufrufen der obigen Funktion.
- Codezeile 9 : Deklarieren der Variablen mit dem Datentyp, der dem Rückgabedatentyp der Funktion entspricht.
- Codezeile 11 : Aufruf der Funktion und Auffüllen des Rückgabewerts in die Variable 'lv_msg'.
- 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 geleitet.
Ähnlichkeiten zwischen Prozedur und Funktion
- Beide können von anderen PL / SQL-Blöcken aufgerufen werden.
- Wenn die im Unterprogramm ausgelöste Ausnahme nicht im Abschnitt zur Behandlung von Unterprogrammausnahmen behandelt wird, wird sie an den aufrufenden Block weitergegeben.
- Beide können beliebig viele Parameter haben.
- Beide werden in PL / SQL als Datenbankobjekte behandelt.
Verfahren Vs. Funktion: Schlüsseldifferenzen
| Verfahren | Funktion |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
Integrierte Funktionen in PL / SQL
PL / SQL enthält verschiedene integrierte Funktionen für die Arbeit mit Zeichenfolgen und Datumsdatentypen. Hier sehen wir die häufig verwendeten Funktionen und ihre Verwendung.
Konvertierungsfunktionen
Diese integrierten Funktionen werden verwendet, um einen Datentyp in einen anderen Datentyp zu konvertieren.
| Funktionsname | Verwendung | Beispiel |
|---|---|---|
| TO_CHAR | Konvertiert den anderen Datentyp in einen Zeichendatentyp | TO_CHAR (123); |
| TO_DATE (Zeichenfolge, Format) | Konvertiert die angegebene Zeichenfolge in das Datum. Die Zeichenfolge sollte mit dem Format übereinstimmen. | TO_DATE ('2015-JAN-15', 'JJJJ-MON-TT'); Ausgabe: 15.01.2015 |
| TO_NUMBER (Text, Format) | Konvertiert den Text in den Zahlentyp des angegebenen Formats. Informat '9' bezeichnet die Anzahl der Ziffern | Wählen Sie TO_NUMBER ('1234', '9999') aus dual; Ausgabe: 1234 Wählen Sie TO_NUMBER ('1.234,45', '9.999,99') aus dual; Ausgabe: 1234 |
String-Funktionen
Dies sind die Funktionen, die für den Zeichendatentyp verwendet werden.
| Funktionsname | Verwendung | Beispiel |
|---|---|---|
| INSTR (Text, Zeichenfolge, Start, Vorkommen) | Gibt die Position eines bestimmten Textes in der angegebenen Zeichenfolge an.
| Select INSTR ( 'AEROPLANE', 'E', 2,1) aus dual Output : 2 Wählen INSTR ( 'AEROPLANE', 'E', 2,2) von dual Output: 9 (2 nd Auftreten von E) |
| SUBSTR (Text, Start, Länge) | Gibt den Teilzeichenfolgenwert der Hauptzeichenfolge an.
| Wählen Sie substr ('Flugzeug', 1,7) aus Dual Output : aeropla |
| OBER (Text) | Gibt den Großbuchstaben des bereitgestellten Textes zurück | Wählen Sie das obere ('guru99') aus dual; Ausgabe : GURU99 |
| UNTER (Text) | Gibt den Kleinbuchstaben des bereitgestellten Textes zurück | Wählen Sie niedriger ('Flugzeug') aus Dual; Ausgabe : Flugzeug |
| INITCAP (Text) | Gibt den angegebenen Text mit dem Anfangsbuchstaben in Großbuchstaben zurück. | Wählen Sie ('guru99') aus der doppelten Ausgabe : Guru99 Wählen Sie ('meine Geschichte') aus der doppelten Ausgabe : Meine Geschichte |
| LÄNGE (Text) | Gibt die Länge der angegebenen Zeichenfolge zurück | Wählen Sie LÄNGE ('guru99') aus dual; Ausgabe : 6 |
| LPAD (Text, Länge, pad_char) | Fügt 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; Ausgabe : $$$$ guru99 |
| RPAD (Text, Länge, pad_char) | Fügt 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 der doppelten Ausgabe : guru99 ---- |
| LTRIM (Text) | Schneidet den führenden Leerraum aus dem Text ab | Wählen Sie LTRIM ('Guru99') aus dual; Ausgabe : Guru99 |
| RTRIM (Text) | Schneidet den nachgestellten Leerraum aus dem Text ab | Wählen Sie RTRIM ('Guru99') aus dual; Ausgabe ; Guru99 |
Datumsfunktionen
Dies sind Funktionen, die zum Bearbeiten mit Datumsangaben verwendet werden.
| Funktionsname | Verwendung | Beispiel |
|---|---|---|
| ADD_MONTHS (Datum, Anzahl der Monate) | Fügt dem Datum die angegebenen Monate hinzu | ADD_MONTH ('2015-01-01', 5); Ausgabe : 01.05.2015 |
| SYSDATE | Gibt das aktuelle Datum und die aktuelle Uhrzeit des Servers zurück | Wählen Sie SYSDATE aus dual; Ausgabe : 04.10.2015 14:11:43 Uhr |
| TRUNC | Runde der Datumsvariablen auf den niedrigstmöglichen Wert | Wählen Sie sysdate, TRUNC (sysdate) aus dual; Ausgabe : 04.10.2015 14:12:39 04.10.2015 |
| RUNDEN | Rundet das Datum auf die nächsthöhere Grenze, entweder höher oder niedriger | Wählen Sie sysdate, ROUND (sysdate) aus der doppelten Ausgabe : 04.10.2015 14:14:34 05.10.2015 |
| MONTHS_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 gelernt.
- So erstellen Sie eine Prozedur und verschiedene Arten, sie aufzurufen
- So erstellen Sie eine Funktion und verschiedene Arten, sie aufzurufen
- Ähnlichkeiten und Unterschiede zwischen Prozedur und Funktion
- Allgemeine Parameter und RETURN-Terminologien in PL / SQL-Unterprogrammen
- Allgemeine integrierte Funktionen in Oracle PL / SQL