Oracle Database 10g PL/SQL Programmierung - Beck-Shop

Oracle Database 10g PL/SQL Programmierung
von
Doris Heidenberger, Scott Urman, Ron Hardman, Michael McLaughlin
1. Auflage
Hanser München 2005
Verlag C.H. Beck im Internet:
www.beck.de
ISBN 978 3 446 22849 8
Zu Inhaltsverzeichnis
schnell und portofrei erhältlich bei beck-shop.de DIE FACHBUCHHANDLUNG
Oracle Database 10g
PL/SQL Programmierung
Scott Urman, Ron Hardman, Michael
McLaughlin
ISBN 3-446-22849-7
Leseprobe
Weitere Informationen oder Bestellungen unter
http://www.hanser.de/3-446-22849-7 sowie im Buchhandel
8
8
Prozeduren, Funktionen und
Packages erstellen
In Kapitel 3 lernten wir zwei wichtige Arten von PL/SQL-Blöcken kennen: anonyme
und benannte. Ein anonymer Block, der entweder mit DECLARE oder BEGIN beginnt,
wird bei jedem Aufruf kompiliert. Er wird auch nicht in der Datenbank gespeichert
und lässt sich nicht direkt aus anderen PL/SQL-Blöcken aufrufen. Die Konstrukte, die
wir in diesem und den nächsten beiden Kapiteln untersuchen – Prozeduren, Funktionen, Packages und Trigger – sind benannte Blöcke und weisen diese Restriktionen
nicht auf. Sie können in der Datenbank gespeichert und bei passender Gelegenheit
ausgeführt werden. In diesem Kapitel untersuchen wir die Syntax für das Erstellen von
Prozeduren, Funktionen und Packages. In Kapitel 9 zeigen wir, wie sie verwendet werden und beschreiben einige ihrer Auswirkungen. Kapitel 10 beschäftigt sich mit Datenbank-Triggern.
8.1
Prozeduren und Funktionen
PL/SQL-Prozeduren und -Funktionen verhalten sich sehr ähnlich wie Prozeduren
und Funktionen in anderen 3GLs (Sprachen der dritten Generation). Sie weisen viele
gleiche Eigenschaften auf. Allgemein betrachtet bezeichnet man Prozeduren und
Funktionen auch als Unterprogramme. Der folgende Code erstellt beispielsweise eine
Prozedur in der Datenbank:
–– Online verfügbar in AddNewAuthor.sql
CREATE OR REPLACE PROCEDURE AddNewAuthor (
p_ID authors.ID%TYPE,
p_FirstName authors.first_name%TYPE,
p_LastName authors.last_name%TYPE) AS
BEGIN
328
8
Prozeduren, Funktionen und Packages erstellen
–– Insert a new row into the authors table, using the supplied
–– arguments for the column values.
INSERT INTO authors (id, first_name, last name)
VALUES (p_ID, p_FirstName, p_LastName);
END AddNewAuthor;
Nach dem Erstellen der Prozedur rufen wir sie aus einem anderen PL/SQL-Block auf:
–– Online verfügbar in AddNewAuthor.sql
BEGIN
AddNewAuthor(100, 'Zelda', 'Zudnik');
END;
Dieses Beispiel zeigt einige wichtige Punkte auf:
■ Die Prozedur AddNewAuthor wird zuerst mit der Anweisung CREATE OR
REPLACE PROCEDURE erstellt. Danach wird die Prozedur zuerst kompiliert
und dann in kompilierter Form in der Datenbank gespeichert. Der kompilierte
Code kann nun von anderen PL/SQL-Blöcken ausgeführt werden. (Auch der
Quellcode der Prozedur wird in der Datenbank hinterlegt. Mehr dazu in Kapitel
9).
■ Beim Aufruf einer Prozedur können Parameter übergeben werden. Im obigen
Beispiel wurden der Prozedur zur Laufzeit die ID des neuen Autors mit Vor- und
Nachnamen übergeben. In der Prozedur hat der Parameter p_ID den Wert 100,
p_FirstName den Wert 'Zelda' und p_LastName den Wert 'Zudnik', weil diese
Literale beim Aufruf der Prozedur an sie übergeben wurden. Die Deklarationen
%TYPE geben die Typen an, mit denen die Parameter für die Tabelle authors
wie bei der Deklaration von Variablen übereinstimmen müssen.
■ Ein Prozeduraufruf ist eine PL/SQL-Anweisung. Die Prozedur lässt sich nicht als
Teil eines Ausdrucks aufrufen. Beim Aufruf einer Prozedur wird die Steuerung an
die erste ausführbare Anweisung in der Prozedur übergeben. Wird die Prozedur
beendet, wird die Steuerung an die Anweisung unmittelbar nach dem Prozeduraufruf übergeben. In dieser Hinsicht verhalten sich PL/SQL-Prozeduren wie die
Prozeduren anderer 3GLs. Funktionen werden als Teil eines Ausdrucks
aufgerufen.
■ Eine Prozedur ist ein PL/SQL-Block mit einem deklarativen, ausführbaren und
einem Exception-Bereich. Wie beim anonymen Block ist nur der ausführbare
Bereich obligatorisch. AddNewAuthor besitzt lediglich einen ausführbaren
Bereich.
8.1
Prozeduren und Funktionen
8.1.1
329
Unterprogramme erstellen
Ähnlich wie andere Data-Dictionary-Objekte werden Unterprogramme mit der CREATE-Anweisung erstellt. Prozeduren werden mit CREATE PROCEDURE und Funktionen mit CREATE FUNCTION erstellt. In den folgenden Abschnitten untersuchen
wir diese Anweisungen etwas genauer.
Prozeduren erstellen
Das folgende Übersichtsdiagramm zeigt die Basissyntax für die Anweisung CREATE
OR REPLACE PROCEDURE:
Procedure_name ist der Name der zu erstellenden Prozedur, argument ist der Name eines Prozedurparameters, type ist der Typ des dazugehörigen Parameters, und procedure_body ist ein PL/SQL-Block mit dem Prozedurcode. Im Abschnitt „Unterprogrammparameter“ erhalten Sie weitere Informationen zu Prozedur- und Funktionsparametern und zur Bedeutung der Schlüsselwörter IN, OUT, INOUT und NOCOPY.
Die Argumentliste ist optional. In diesem Fall enthalten weder die Prozedurdeklaration
noch der Prozeduraufruf Klammern. Für CREATE OR REPLACE PROCEDURE gibt
es weitere Klauseln, auf die wir später in diesem Kapitel näher eingehen werden.
Um den Code einer Prozedur zu ändern, muss diese gelöscht und neu angelegt werden. Da dies bei der Entwicklung von Prozeduren eine übliche Operation ist, lässt sich
das mit Hilfe der Schlüsselwörter OR REPLACE in einem Schritt erledigen. Ist die Prozedur vorhanden, wird sie ohne Warnmeldung gelöscht. (Zum Löschen einer Prozedur verwenden Sie den Befehl DROP PROCEDURE). Sollte die Prozedur noch nicht
vorhanden sein, wird sie einfach angelegt. Falls die Prozedur vorhanden ist, aber die
Schlüsselwörter OR REPLACE vergessen wurden, gibt die CREATE-Anweisung den
folgenden Oracle-Fehler zurück: „ORA-955: Name is already used by an existing object“.
330
8
Prozeduren, Funktionen und Packages erstellen
Wie bei anderen CREATE-Anweisungen ist das Anlegen einer Prozedur eine DDLOperation. Damit wird vor und nach dem Anlegen der Prozedur ein implizites COMMIT ausgeführt. Man kann entweder das Schlüsselwort IS oder AS verwenden – beide
sind äquivalent.
Prozedurbodys Der Body einer Prozedur ist ein PL/SQL-Block mit einem deklarativen, einemausführbaren und einem Exception-Bereich. Der deklarative Bereich
liegt zwischen den Schlüsselwörtern AS oder IS und dem Schlüsselwort BEGIN. Der
ausführbare Bereich (nur dieser ist obligatorisch) liegt zwischen den Schlüsselwörtern BEGIN und EXCEPTION oder, falls der Exception-Bereich fehlt, zwischen den
Schlüsselwörtern BEGIN und END. Der Exception-Bereich, falls vorhanden, liegt
zwischen den Schlüsselwörtern EXCEPTION und END.
Tipp:
In der Deklaration einer Prozedur oder Funktion kommt das
Schlüsselwort DECLARE nicht vor. Stattdessen wird das
Schlüsselwort IS oder AS verwendet. Diese Syntax stammt
ursprünglich aus Ada, worauf PL/SQL basiert.
Die Struktur einer Anweisung zum Erstellen einer Prozedur sieht wie folgt aus:
CREATE OR REPLACE PROCEDURE procedure_name [parameter_list]
AS
/* Hier ist der deklarative Bereich. */
BEGIN
/* Hier ist der ausführbare Bereich. */
EXCEPTION
/* Hier ist der Exception-Bereich. */
END [procedure_name];
Der Prozedurname kann in der Prozedurdeklaration optional nach der finalen ENDAnweisung aufgenommen werden. Steht nach dem END ein Identifier, muss er mit
dem Prozedurnamen übereinstimmen.
Tipp:
Es entspricht einem guten Programmierstil, den Prozedurnamen
nach dem letzten END anzugeben. Damit wird der Quellcode
transparenter, die zusammengehörigen END- und CREATEAnweisungen werden hervorgehoben, und der PL/SQL-Compiler
kann schon sehr früh falsche BEGIN-END-Paare ermitteln.
8.1
Prozeduren und Funktionen
331
Funktionen erstellen
Funktionen und Prozeduren sind sehr ähnlich. Beide übernehmen Parameter in beliebigen Modi. Beide enthalten verschiedene Formen von PL/SQL-Blöcken mit deklarativem, ausführbarem und Exception-Bereich. Beide können in der Datenbank gespeichert oder in einem Block deklariert werden. Ein Prozeduraufruf ist aber eine PL/SQLAnweisung, während eine Funktion als Teil eines Ausdrucks aufgerufen wird. Die folgende Funktion gibt beispielsweise TRUE zurück, wenn das angegebene Buch drei Autoren aufweist, andernfalls FALSE:
–– Online verfügbar in ThreeAuthors.sql
CREATE OR REPLACE FUNCTION ThreeAuthors(p_ISBN IN books.isbn%TYPE)
RETURN BOOLEAN AS
v_Author3 books.author3%TYPE;
BEGIN
–– Select the third author for the supplied book into v_Author3.
SELECT author3
INTO v_Author3
FROM books
WHERE isbn = p_ISBN;
–– If v_Author3 is NULL, that means that the book has less then 3
–– authors, so we can return false. Otherwise, return true.
IF v_Author3 IS NULL THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END ThreeAuthors;
Die Funktion ThreeAuthors liefert einen Booleschen Wert. Die folgende SQL*PlusSession zeigt den Aufruf. Beachten Sie, dass der Funktionsaufruf keine Anweisung ist,
sondern als Teil der IF-Anweisung innerhalb der Schleife erscheint.
–– Online verfügbar in ThreeAuthors.sql
SQL> BEGIN
2
FOR v_Rec IN (SELECT ISBN, title FROM books) LOOP
3
IF ThreeAuthors(v_Rec.ISBN) THEN
4
DBMS_OUTPUT.PUT_LINE('"' || v_Rec.title || '" has 3 authors');
5
END IF;
6
END LOOP;
7 END;
8 /
"Oracle DBA 101" has 3 authors
"Oracle Performance Tuning 101" has 3 authors
"Oracle9i: A Beginner's Guide" has 3 authors
332
8
Prozeduren, Funktionen und Packages erstellen
"Oracle9i DBA 101" has 3 authors
"Oracle Database 10g A Beginner's Guide" has 3 authors
"Oracle E-Business Suite Financials Handbook" has 3 authors
"Oracle E-Business Suite Manufacturing & Supply Chain Management" has 3
authors
"Oracle Database 10g XML & SQL Design, Build, & Manage XML Applications
in Java, C, C++,
& PL/SQL" has 3 authors
"Oracle PL/SQL Tips and Techniques" has 3 authors
PL/SQL procedure successfully completed.
Syntax von Funktionen Die Syntax zum Erstellen einer gespeicherten Funktion
ähnelt der Syntax für eine Prozedur: Die folgende Übersicht zeigt die Syntax.
Function_name ist der Name der Funktion, argument und type sind das Gleiche wie bei
Prozeduren und return_type ist der Typ des Rückgabewertes der Funktion. function_
body ist ein PL/SQL-Block mit dem Quellcode der Funktion. Für einen Funktionsbody
gelten die gleichen Regeln wie für einen Prozedurbody – so kann der Name der Funktion optional nach dem letzten END erscheinen.
Wie auch bei Prozeduren ist die Argumentliste hier optional. In diesem Fall gibt es weder in der Funktionsdeklaration noch im Funktionsaufruf Klammern. Der Rückgabetyp der Funktion ist allerdings obligatorisch, da der Funktionsaufruf Teil eines Aus-
8.1
Prozeduren und Funktionen
333
drucks ist. Über den Funktionstyp wird der Typ des Ausdrucks ermittelt, der den
Funktionsaufruf enthält.
RETURN-Anweisungen Im Funktionsbody wird über die RETURN-Anweisung
die Steuerung an die aufrufende Umgebung durch Übergabe eines Wertes zurückgegeben. Die allgemeine Syntax der RETURN-Anweisung lautet wie folgt:
RETURN expression;
wobei expression der zurückzugebende Wert ist. Bei der Ausführung von RETURN
wird expression in den Typ konvertiert, der in der RETURN-Klausel der Funktionsdefinition angegeben wurde. An diesem Punkt kehrt die Steuerung unmittelbar zur aufrufenden Umgebung zurück.
In einer Funktion kann es mehr als eine RETURN-Anweisung geben, obwohl nur eine
ausgeführt wird. Es führt zu einem Fehler, wenn eine Funktion ohne Ausführung von
RETURN endet. So enthält die im obigen Abschnitt vorgestellte Funktion ThreeAuthors zwei RETURN-Anweisungen. Welche ausgeführt wird, hängt davon ab, ob das
betreffende Buch drei Autoren hat.
In einer Funktion muss die RETURN-Anweisung mit einem Ausdruck verknüpft sein.
RETURN kann auch in einer Prozedur verwendet werden. In diesem Fall besitzt die
Anweisung kein Argument, was dazu führt, dass die Steuerung unmittelbar an die aufrufende Umgebung zurückgegeben wird. Die aktuellen Werte der formalen Parameter, die als OUT oder IN OUT deklariert sind, werden an die konkreten Parameter zurückgegeben, und die Ausführung wird bei der Anweisung fortgesetzt, die unmittelbar
auf den Prozeduraufruf folgt.
Prozeduren und Funktionen löschen
So wie eine Tabelle gelöscht werden kann, können auch Prozeduren und Funktionen
gelöscht werden. Damit wird die Prozedur oder Funktion aus dem Data Dictionary
entfernt. Die Syntax zum Löschen einer Prozedur lautet:
DROP PROCEDURE procedure_name;
und zum Löschen einer Funktion:
DROP FUNCTION function_name;
wobei procedure_name der Name einer vorhandenen Prozedur und function_name der
Name einer existierenden Funktion ist. Die folgende Anweisung löscht beispielsweise
die Prozedur AddNewAuthor:
DROP PROCEDURE AddNewAuthor;
Handelt es sich bei dem zu löschenden Objekt um eine Funktion, müssen Sie DROP
FUNCTION verwenden. Bei einer Prozedur setzen Sie DROP PROCEDURE ein. Wie
CREATE ist auch DROP ein DDL-Befehl: Damit wird vor und nach der Anweisung ein
334
8
Prozeduren, Funktionen und Packages erstellen
implizites COMMIT ausgeführt. Sollte das Unterprogramm nicht vorhanden sein, löst
die DROP-Anweisung den Fehler „ORA-4043: Object does not exist“ aus.
8.1.2
Unterprogrammparameter
Wie in anderen 3GLs können Sie Prozeduren und Funktionen erstellen, die Parameter
übernehmen. Diese Parameter können verschiedene Modi besitzen und nach Wert
(By Value) oder Referenz (By Reference) übergeben werden. In den folgenden Abschnitten betrachten wir dies etwas genauer.
Parameter-Modi
Die bereits bekannte Prozedur AddNewAuthor können wir aus folgendem anonymen
PL/SQL-Block aufrufen:
–– Online verfügbar als callANA.sql
DECLARE
–– Variables describing the new author
v_NewFirstName authors.first_name%TYPE := 'Cynthia';
v_NewLastName authors.last_name%TYPE := 'Camino';
v_NewAuthorID authors.ID%TYPE := 100;
BEGIN
–– Add Cynthia Camino to the database
AddNewAuthor(v_NewAuthorID, v_NewFirstName, v_NewLastName);
END;
Die in diesem Block deklarierten Variablen (v_NewAuthorID, v_NewFirstName, v_
NewLastName) werden an AddNewAuthor als Argumente übergeben. In diesem
Kontext bezeichnet man sie als konkrete Parameter, während man die Parameter in der
Prozedurdeklaration (p_ID, p_FirstName, p_LastName) formale Parameter nennt.
Konkrete Parameter enthalten Werte, die an eine Prozedur beim Aufruf übergeben
werden. Sie erhalten von der Prozedur bei der Rückkehr die Ergebnisse (in Abhängigkeit vom Modus). Die Werte der konkreten Parameter werden in der Prozedur verwendet. Die formalen Parameter sind die Patzhalter für die Werte der konkreten Parameter. Beim Aufruf der Prozedur werden den formalen Parametern die Werte der
konkreten Parameter zugewiesen. Innerhalb der Prozedur werden sie über die formalen Parameter referenziert. Kehrt die Prozedur zurück, werden den konkreten Parametern die Werte der formalen Parameter zugewiesen. Diese Zuweisungen folgen den
normalen Regeln für PL/SQL-Zuweisungen, inklusive der Typenkonvertierung (falls
erforderlich).
Formale Parameter können drei Modi besitzen – IN, OUT oder IN OUT. (Der Modifizierer NOCOPY wird im nächsten Abschnitt beschrieben.) Wird für einen formalen
8.1
Prozeduren und Funktionen
335
Parameter der Modus nicht angegeben, wird er standardmäßig auf IN eingestellt. Tabelle 8-1 enthält die Unterschiede zwischen den Modi.
Tabelle 8-1:
Parametermodi.
Modus
Beschreibung
IN
Die Werte des konkreten Parameters werden beim Aufruf der Prozedur an sie
übergeben. Innerhalb der Prozedur agiert der formale Parameter wie eine PL/SQLKonstante – er wird als read-only betrachtet und kann nicht geändert werden.
Wennbei Beendigung der Prozedur die Steuerung an die aufrufende Umgebung
zurückgegeben wird, wird der konkrete Parameter nicht geändert.
OUT
Der Wert des konkreten Parameters wird beim Aufruf der Prozedur ignoriert.
Innerhalb der Prozedur agiert der formale Parameter wie eine nicht initialisierte
PL/SQL-Variable und hat damit den Wert NULL. Er kann gelesen und in ihn kann
geschrieben werden. Wenn bei Beendigung der Prozedur die Steuerung an die
aufrufende Umgebung zurückkehrt, werden die Inhalte des formalen Parameters
dem konkreten Parameter zugewiesen. (Dieses Verhalten kann mit dem NOCOPYModifizierer geändert werden).
IN OUT
Dieser Modus ist eine Kombination von IN und OUT. Der Wert des konkreten
Parameters wird beim Aufruf der Prozedur an sie übergeben. Innerhalb der Prozedur
agiert der formale Parameter wie eine initialisierte Variable, in die geschrieben und
aus der gelesen werden kann. Wenn bei Beendigung der Prozedur die Steuerung an
die aufrufende Umgebung zurückkehrt, werden die Inhalte des formalen Parameters
dem konkreten Parameter zugewiesen. (In Abhängigkeit von NOCOPY, wie bei
OUT).
IN-Parametern Werte zuweisen Betrachten Sie die folgende Prozedur, die einen einzelnen IN-Parameter verwendet:
–– Online verfügbar in parameterModes.sql
CREATE OR REPLACE PROCEDURE ModeIn (
p_InParameter IN NUMBER) AS
v_LocalVariable NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT('Inside ModeIn: ');
IF (p_InParameter IS NULL) THEN
DBMS_OUTPUT.PUT_LINE('p_InParameter is NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('p_InParameter = ' || p_InParameter);
END IF;
/* Assign p_InParameter to v_LocalVariable. This is legal,
since we are reading from an IN parameter and not writing
to it. */
v_LocalVariable := p_InParameter;
DBMS_OUTPUT.PUT('At end of ModeIn: ');
336
8
Prozeduren, Funktionen und Packages erstellen
IF (p_InParameter IS NULL) THEN
DBMS_OUTPUT.PUT_LINE('p_InParameter is NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('p_InParameter = ' || p_InParameter);
END IF;
END ModeIn;
Die folgende SQL*Plus-Session zeigt einen erfolgreichen Aufruf von ModeIn:
–– Online verfügbar in parameterModes.sql
SQL> DECLARE
2
v_In NUMBER := 1;
3 BEGIN
4
– Call ModeIn with a variable, which should remain unchanged.
5
DBMS_OUTPUT.PUT_LINE('Before calling ModeIn, v_In = ' || v_In);
6
ModeIn(v_In);
7
DBMS_OUTPUT.PUT_LINE('After calling ModeIn, v_In = ' || v_In);
8 END;
9 /
Before calling ModeIn, v_In = 1
Inside ModeIn: p_InParameter = 1
At end of ModeIn: p_InParameter = 1
After calling ModeIn, v_In = 1
PL/SQL procedure successfully completed.
Wie zu erwarten, bleibt der Wert von v_In vor, während und nach dem Prozeduraufruf derselbe.
OUT-Parametern Werte zuweisen Betrachten Sie nun die folgende Prozedur,
die einen einzelnen OUT-Parameter verwendet:
–– Online verfügbar in parameterModes.sql
CREATE OR REPLACE PROCEDURE ModeOut (
p_OutParameter OUT NUMBER) AS
v_LocalVariable NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT('Inside ModeOut: ');
IF (p_OutParameter IS NULL) THEN
DBMS_OUTPUT.PUT_LINE('p_OutParameter is NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('p_OutParameter = ' || p_OutParameter);
END IF;
/* Assign 7 to p_OutParameter. This is legal, since we
are writing to an OUT parameter. */
p_OutParameter := 7;
/* Assign p_OutParameter to v_LocalVariable. This is also legal,
* since we are reading from an OUT parameter. */
v_LocalVariable := p_OutParameter;
8.1
Prozeduren und Funktionen
337
DBMS_OUTPUT.PUT('At end of ModeOut: ');
IF (p_OutParameter IS NULL) THEN
DBMS_OUTPUT.PUT_LINE('p_OutParameter is NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('p_OutParameter = ' || p_OutParameter);
END IF;
END ModeOut;
Die folgende SQL*Plus-Session zeigt einen erfolgreichen Aufruf von ModeOUT:
–– Online verfügbar in parameterModes.sql
SQL> DECLARE
2
v_Out NUMBER := 1;
3 BEGIN
4
-- Call ModeOut with a variable, which should be modified.
5
DBMS_OUTPUT.PUT_LINE('Before calling ModeOut, v_Out = ' || v_Out);
6
ModeOut(v_Out);
7
DBMS_OUTPUT.PUT_LINE('After calling ModeOut, v_Out = ' || v_Out);
8 END;
9 /
Before calling ModeOut, v_Out = 1
Inside ModeOut: p_OutParameter is NULL
At end of ModeOut: p_OutParameter = 7
After calling ModeOut, v_Out = 7
PL/SQL procedure successfully completed.
Aus dem obigen Beispiel lassen sich zwei Dinge ableiten – der formale Parameter p_
OutParameter ist NULL, selbst wenn der konkrete Parameter v_Out auf 1 initialisiert wurde. Der finale Wert des formalen Parameters p_OutParameter wird bei der
Rückkehr aus der Prozedur in den konkreten Parameter v_Out kopiert.
Hinweis:
Löst die Prozedur einen Fehler aus, werden die Werte der
formalen IN- und OUT-Parameter nicht in die dazugehörigen
konkreten Parameter kopiert (gemäß NOCOPY).
IN OUT-Parametern Werte zuweisen Betrachten Sie nun die folgende Prozedur, die einen einzelnen IN OUT-Parameter verwendet:
–– Online verfügbar in parameterModes.sql
CREATE OR REPLACE PROCEDURE ModeInOut (
p_InOutParameter IN OUT NUMBER) IS
v_LocalVariable NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT('Inside ModeInOut: ');
IF (p_InOutParameter IS NULL) THEN
DBMS_OUTPUT.PUT_LINE('p_InOutParameter is NULL');
338
8
Prozeduren, Funktionen und Packages erstellen
ELSE
DBMS_OUTPUT.PUT_LINE('p_InOutParameter = ' || p_InOutParameter);
END IF;
/* Assign p_InOutParameter to v_LocalVariable. This is legal,
since we are reading from an IN OUT parameter. */
v_LocalVariable := p_InOutParameter;
/* Assign 8 to p_InOutParameter. This is legal, since we
are writing to an IN OUT parameter. */
p_InOutParameter := 8;
DBMS_OUTPUT.PUT('At end of ModeInOut: ');
IF (p_InOutParameter IS NULL) THEN
DBMS_OUTPUT.PUT_LINE('p_InOutParameter is NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('p_InOutParameter = ' || p_InOutParameter);
END IF;
END ModeInOut;
Die folgende SQL*Plus-Session zeigt einen erfolgreichen Aufruf von ModeInOut:
–– Online verfügbar in parameterModes.sql
SQL> DECLARE
2
v_InOut NUMBER := 1;
3 BEGIN
4
–– Call ModeInOut with a variable, which should be modified.
5
DBMS_OUTPUT.PUT_LINE('Before calling ModeInOut, v_InOut = ' ||
6
v_InOut);
7
ModeInOut(v_InOut);
8
DBMS_OUTPUT.PUT_LINE('After calling ModeInOut, v_InOut = ' ||
9
v_InOut);
10 END;
11 /
Before calling ModeInOut, v_InOut = 1
Inside ModeInOut: p_InOutParameter = 1
At end of ModeInOut: p_InOutParameter = 8
After calling ModeInOut, v_InOut = 8
PL/SQL procedure successfully completed.
Dies unterscheidet sich von der Ausgabe von ModeOut in einem wichtigen Aspekt:
Der formale Parameter p_InOutParameter wird mit dem Wert der aktuellen Variable v_InOut initialisiert. Wie bei ModeOut wird aber der finale Wert des formalen
Parameters p_InOutParameter bei der Rückkehr aus der Prozedur in die konkrete
Parametervariable v_InOut kopiert.
Literale oder Konstanten als konkrete Parameter Aufgrund dieses Kopiervorgangs müssen die konkreten Parameter, die den formalen Parametern OUT
oder IN OUT entsprechen, eine Variable und keine Konstante oder ein Ausdruck sein.
Es muss einen Ort geben, an dem der Rückgabewert gespeichert werden kann. Der
8.1
Prozeduren und Funktionen
339
PL/SQL-Compiler erkennt diese Situation und löst einen Fehler aus, wie die folgende
SQL*Plus-Session zeigt:
–– Online verfügbar in parameterModes.sql
SQL> BEGIN
2
–– We cannot call ModeOut (or ModeInOut) with a constant, since
3
–– the actual parameter must identify a storage location.
4
ModeOut(3);
5 END;
6 /
ModeOut(3);
*
ERROR at line 4:
ORA-06502: line 4, column 11:
PLS-00363: expression '3' cannot be used as an assignment target
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored
Wir können aber einen konkreten Parameter mit einem konstanten Wert ohne Fehler
für einen formalen IN-Parameter verwenden:
–– Online verfügbar in parameterModes.sql
SQL> BEGIN
2
–– We can call ModeIn with a constant, though.
3
ModeIn(3);
4 END;
5 /
Inside ModeIn: p_InParameter = 3
At end of ModeIn: p_InParameter = 3
PL/SQL procedure successfully completed.
Wie zu erwarten, bleibt der formale Parameter p_In während der Ausführung der
Prozedur unverändert.
Modifikationen von IN-Parametern Zusätzlich zur Gültigkeitsprüfung der
konkreten OUT-Parameter stellt der PL/SQL-Compiler auch sicher, dass kein formaler IN-Parameter geändert wurde, wie die folgende SQL*Plus-Sitzung zeigt:
–– Online verfügbar in parameterModes.sql
SQL>
2
3
4
5
6
7
8
CREATE OR REPLACE PROCEDURE IllegalModeIn (
p_InParameter IN NUMBER) AS
BEGIN
/* Assign 7 to p_InParameter. This is ILLEGAL, since we
are writing to an IN parameter. */
p_InParameter := 7;
END IllegalModeIn;
/
340
8
Prozeduren, Funktionen und Packages erstellen
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE ILLEGALMODEIN:
LINE/COL ERROR
–––– –––––––––––––––––––––––––––––––6/3
PLS-00363: expression 'P_INPARAMETER' cannot be used as an
assignment target
6/3
PL/SQL: Statement ignored
Constraints für formale Parameter
Beim Aufruf einer Prozedur werden die Werte der konkreten Parameter übergeben.
Diese Parameter werden mit den formalen Parametern in der Prozedur referenziert.
Auch die Constraints für die Variablen werden als Teil des Parameterübergabeverfahrens übergeben. In der Deklaration einer Prozedur ist es unzulässig, CHAR- oder
VARCHAR2-Parameter in der Länge zu beschränken und NUMBER-Parametern eine
Genauigkeit oder Anzahl der Nachkommastellen vorzugeben, weil die Constraints
von den konkreten Parametern übernommen werden. So ist beispielsweise die folgende Prozedurdeklaration unzulässig und führt zu einem Kompilierungsfehler:
–– Online verfügbar in ParameterLength.sql
CREATE OR REPLACE PROCEDURE ParameterLength (
p_Parameter1 IN OUT VARCHAR2(10),
p_Parameter2 IN OUT NUMBER(3,1)) AS
BEGIN
p_Parameter1 := 'abcdefghijklm'; – 15 characters in length
p_Parameter2 := 12.3;
END ParameterLength;
Die korrekte Deklaration für diese Prozedur lautet wie folgt:
–– Online verfügbar in ParameterLength.sql
CREATE OR REPLACE PROCEDURE ParameterLength (
p_Parameter1 IN OUT VARCHAR2,
p_Parameter2 IN OUT NUMBER) AS
BEGIN
p_Parameter1 := 'abcdefghijklmno'; – 15 characters in length
p_Parameter2 := 12.3;
END ParameterLength;
Welche Constraints bestehen nun für p_Parameter1 und p_Parameter2? Sie rühren von den konkreten Parametern her. Wenn wir ParameterLength wie folgt aufrufen:
8.1
Prozeduren und Funktionen
341
–– Online verfügbar in ParameterLength.sql
DECLARE
v_Variable1 VARCHAR2(40);
v_Variable2 NUMBER(7,3);
BEGIN
ParameterLength(v_Variable1, v_Variable2);
END;
hat p_Parameter1 eine maximale Länge von 40 (vom konkreten Parameter v_
Variable1) und p_Parameter2 hat die Genauigkeit 7 und die Anzahl der Nachkommastellen 3 (vom konkreten Parameter v_Variable2). Dies sollte man stets vor
Augen haben. Betrachten Sie dazu den folgenden Block, der ebenfalls ParameterLength aufruft:
–– Online verfügbar in ParameterLength.sql
DECLARE
v_Variable1 VARCHAR2(10);
v_Variable2 NUMBER(7,3);
BEGIN
ParameterLength(v_Variable1, v_Variable2);
END;
Der einzige Unterschied zwischen diesem und dem vorherigen Block ist, dass v_
Variable1 und damit p_Parameter1 eine Länge von 10 anstatt von 40 besitzt. Da
ParameterLength an p_Parameter1 (und damit v_Variable1) eine Zeichenfolge der Länge 15 zuweist, ist in der Zeichenfolge nicht genügend Platz vorhanden. Das
führt beim Aufruf der Prozedur zu einem Oracle-Fehler:
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error character string buffer too
small
ORA-06512: at "EXAMPLE.PARAMETERLENGTH", line 5
ORA-06512: at line 5
Die Ursache des Fehlers liegt nicht in der Prozedur, sondern im Code, der die Prozedur aufruft. Außerdem ist ORA-6502 ein Laufzeitfehler und kein Kompilierfehler. Damit wird der Block erfolgreich kompiliert. Der Fehler wurde tatsächlich bei der Rückkehr der Prozedur ausgelöst und die PL/SQL-Engine versuchte, den konkreten Wert
'abcdefghijklmno' in den formalen Parameter zu kopieren.
342
8
Prozeduren, Funktionen und Packages erstellen
Tipp:
Um Fehler wie ORA-6502 zu vermeiden, sollten Sie beim
Erstellen der Prozedur alle Constraint-Anforderungen der
konkreten Parameter dokumentieren. Diese Dokumentation
kann aus Kommentaren bestehen und sollte neben den
Parameterdefinitionen angeben, was die Prozedur macht.
Alternativ können Sie den formalen Parameter mit %TYPE
deklarieren.
%TYPE und Prozedurparameter Obwohl formale Parameter nicht mit Constraints deklariert werden können, können ihnen mit %TYPE Constraints zugewiesen
werden. Wird ein formaler Parameter mit Hilfe von %TYPE deklariert und der zugrunde liegende Typ ist beschränkt, existiert das Constraint nicht für den konkreten,
sondern für den formalen Parameter. Wenn wir ParameterLength wie folgt deklarieren:
–– Online verfügbar in ParameterLength.sql
CREATE OR REPLACE PROCEDURE ParameterLength (
p_Parameter1 IN OUT VARCHAR2,
p_Parameter2 IN OUT books.copyright%TYPE) AS
BEGIN
p_Parameter2 := 12345;
END ParameterLength;
wird p_Parameter2 mit der Genauigkeit von 3 beschränkt, weil das die Genauigkeit
der Spalte copyright ist. Selbst wenn wir ParameterLength mit einem konkreten
Parameter aufrufen, dessen Genauigkeit ausreicht, wird die formale Genauigkeit übernommen. Damit generiert das folgende Beispiel den Fehler ORA-6502:
–– Online verfügbar in ParameterLength.sql
SQL> DECLARE
2
v_Variable1 VARCHAR2(1);
3
–– Declare v_Variable2 with no constraints
4
v_Variable2 NUMBER;
5 BEGIN
6
–– Even though the actual parameter has room for 12345, the
7
–– constraint on the formal parameter is taken and we get
8
–– ORA-6502 on this procedure call.
9
ParameterLength(v_Variable1, v_Variable2);
10 END;
11 /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error number precision too large
8.1
Prozeduren und Funktionen
343
ORA-06512: at "EXAMPLE.PARAMETERLENGTH", line 5
ORA-06512: at line 9
Hinweis:
Der Fehler ORA-6502 wurde für Oracle8i erweitert. Für Oracle8i
meldete der Fehler lediglich „ORA-6502: PL/SQL numeric or
value error“, ungeachtet der tatsächlichen Fehlerursache.
In Unterprogrammen ausgelöste Exceptions
Tritt in einem Unterprogramm ein Fehler auf, wird eine Exception ausgelöst. Diese
Exception kann benutzer- oder vordefiniert sein. Besitzt die Prozedur für diesen Fehler keinen Exception Handler (oder die Exception wird in einem Exception Handler
ausgelöst), wird die Steuerung unmittelbar aus der Prozedur an die aufrufende Umgebung übergeben. Das geschieht in Übereinstimmung mit den Regeln zur Weitergabe
von Exceptions (siehe Kapitel 9). In diesem Fall werden die Werte der formalen Parameter OUT und INOUT nicht an die konkreten Parameter zurückgegeben. Die konkreten Parameter haben die gleichen Werte, als wäre die Prozedur nicht aufgerufen
worden. Angenommen, wir erstellen folgende Prozedur:
–– Online verfügbar in RaiseError.sql
/* Illustrates the behavior of unhandled exceptions and
* OUT variables. If p_Raise is TRUE, then an unhandled
* error is raised. If p_Raise is FALSE, the procedure
* completes successfully.
*/
CREATE OR REPLACE PROCEDURE RaiseError (
p_Raise IN BOOLEAN,
p_ParameterA OUT NUMBER) AS
BEGIN
p_ParameterA := 7;
IF p_Raise THEN
/* Even though we have assigned 7 to p_ParameterA, this
* unhandled exception causes control to return immediately
* without returning 7 to the actual parameter associated
* with p_ParameterA.
*/
RAISE DUP_VAL_ON_INDEX;
ELSE
–– Simply return with no error. This will return 7 to the
–– actual parameter.
RETURN;
END IF;
END RaiseError;
Wenn wir RaiseError mit folgendem Block aufrufen:
344
8
Prozeduren, Funktionen und Packages erstellen
–– Online verfügbar in RaiseError.sql
DECLARE
v_Num NUMBER := 1;
BEGIN
DBMS_OUTPUT.PUT_LINE('Value before first call: ' || v_Num);
RaiseError(FALSE, v_Num);
DBMS_OUTPUT.PUT_LINE('Value after successful call: ' || v_Num);
DBMS_OUTPUT.PUT_LINE('');
v_Num := 2;
DBMS_OUTPUT.PUT_LINE('Value before second call: ' || v_Num);
RaiseError(TRUE, v_Num);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Value after unsuccessful call: ' || v_Num);
END;
erhalten wir die folgende Ausgabe:
Value
Value
Value
Value
before first call: 1
after successful call: 7
before second call: 2
after unsuccessful call: 2
Vor dem ersten Aufruf von RaiseError enthielt v_Num 1. Der erste Aufruf war erfolgreich, und v_Num wurde der Wert 7 zugewiesen. Dann änderte der Block v_Num
vor dem zweiten Aufruf von RaiseError in 2. Dieser zweite Aufruf war nicht erfolgreich, und v_Num bleibt unverändert 2 (anstatt ein zweites Mal in 7 geändert zu werden).
Hinweis:
Die Semantik des Exception-Handlings ändert sich,
wenn ein OUT- oder IN OUT-Parameter mit dem
NOCOPY-Hint deklariert wird.
Übergabe von Parametern nach Referenz und nach Wert
Zur Übergabe eines Unterprogrammparameters bieten sich zwei Möglichkeiten –
nach Referenz oder nach Wert. Übergibt man einen Parameter nach Referenz, wird
dem dazugehörigen formalen Parameter ein Zeiger auf den konkreten Parameter
übergeben. Erfolgt die Übergabe eines Parameters nach Wert, wird er aus dem konkreten in den formalen Parameter kopiert. Die Übergabe nach Referenz ist generell
schneller, weil der Kopiervorgang entfällt. Das gilt insbesondere für Collectionparameter (Tabellen und Varrays, siehe Kapitel 6) aufgrund ihrer Größe. Standardmäßig
kopiert PL/SQL die IN-Parameter nach Referenz und IN OUT- und OUT-Parameter
nach Wert. Das geschieht, um die weiter oben besprochenen Exception-Semantiken
8.1
Prozeduren und Funktionen
345
zu erhalten, und damit die Constraints für die konkreten Parameter verifiziert werden
können. Vor Oracle8i konnte man dieses Verhalten nicht ändern.
NOCOPY verwenden Oracle8i bietet einen Compiler-Hint namens NOCOPY.
Die Syntax zur Deklaration eines Parameters mit diesem Hint lautet:
parameter_name [mode] NOCOPY datatype
wobei parameter_name der Name des Parameters und mode der Parametermodus (IN,
OUT oder IN OUT) ist. datatype ist der Datentyp des Parameters. Ist NOCOPY vorhanden, versucht der PL/SQL-Compiler, den Parameter nach Referenz und nicht nach
Wert zu übergeben. NOCOPY ist ein Compiler-Hint und keine Anweisung, so dass es
nicht immer übernommen wird. Das folgende Beispiel zeigt die Syntax für NOCOPY:
–– Online verfügbar in NoCopyTest.sql
CREATE OR REPLACE PROCEDURE NoCopyTest (
p_InParameter
IN NUMBER,
p_OutParameter
OUT NOCOPY VARCHAR2,
p_InOutParameter IN OUT NOCOPY CHAR) IS
BEGIN
NULL;
END NoCopyTest;
Verwendet man NOCOPY für einen IN-Parameter, erhält man einen Compilerfehler,
weil IN-Parameter immer nach Referenz übergeben werden und NOCOPY damit unzulässig ist.
Exception-Semantik mit NOCOPY Wird ein Parameter nach Referenz übergeben, wirken sich alle Änderungen des formalen Parameters auch auf den konkreten
Parameter aus, da beide auf denselben Speicherort zeigen. Sollte also eine Prozedur
mit einer unbehandelten Exception nach Änderung des formalen Parameters scheitern, geht der ursprüngliche Wert des konkreten Parameters verloren. Angenommen,
wir ändern RaiseError für den Einsatz von NOCOPY wie folgt:
–– Online verfügbar in NoCopyTest.sql
CREATE OR REPLACE PROCEDURE RaiseErrorNoCopy (
p_Raise IN BOOLEAN,
p_ParameterA OUT NOCOPY NUMBER) AS
BEGIN
p_ParameterA := 7;
IF p_Raise THEN
RAISE DUP_VAL_ON_INDEX;
ELSE
RETURN;
END IF;
END RaiseErrorCopy;
346
8
Prozeduren, Funktionen und Packages erstellen
so ist die einzige Änderung, dass p_ParameterA jetzt nach Referenz und nicht nach
Wert übergeben wird. Nun rufen wir RaiseError wie folgt auf:
–– Online verfügbar in NoCopyTest.sql
v_Num NUMBER := 1;
BEGIN
DBMS_OUTPUT.PUT_LINE('Value before first call: ' || v_Num);
RaiseErrorNoCopy(FALSE, v_Num);
DBMS_OUTPUT.PUT_LINE('Value after successful call: ' || v_Num);
DBMS_OUTPUT.PUT_LINE('');
v_Num := 2;
DBMS_OUTPUT.PUT_LINE('Value before second call: ' || v_Num);
RaiseErrorNoCopy(TRUE, v_Num);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Value after unsuccessful call: ' || v_Num);
END;
(Dies ist derselbe Block, den wir bereits im obigen Abschnitt "In Unterprogrammen
ausgelöste Exceptions" gesehen haben, nur dass hier RaiseErrorNoCopy anstelle
von RaiseError aufgerufen wird). Die Ausgabe dieses Blocks sieht allerdings anders
aus:
Value
Value
Value
Value
before first call: 1
after successful call: 7
before second call: 2
after unsuccessful call: 7
Der konkrete Parameter wurde beide Male geändert, auch nach Auslösen der Exception.
Restriktionen bei NOCOPY In bestimmten Fällen wird NOCOPY ignoriert
und der Parameter wird nach Wert übergeben. Dabei wird kein Fehler generiert. Beachten Sie, dass NOCOPY nur ein Hint ist – der Compiler muss dieser Vorgabe nicht
zwingend befolgen. NOCOPY wird in folgenden Situationen ignoriert:
■ Der konkrete Parameter ist ein Element eines assoziativen Arrays. Ist der konkrete
Parameter eine vollständige Tabelle, gilt diese Restriktion nicht.
■ Der tatsächliche Parameter ist durch eine Gesamtstellenzahl, Skalierung oder ein
NOT NULL-Constraint beschränkt. Diese Restriktion gilt nicht für Zeichenparameter, deren maximale Länge beschränkt ist. Der Grund ist, dass der PL/SQLCompiler die Prüfung auf Constraintverletzungen nur bei Rückkehr aus einem
Unterprogramm durchführt, wenn die Werte aus dem formalen in den konkreten
Parameter kopiert werden. Liegt eine Constraintverletzung vor, muss der
ursprüngliche Wert des tatsächlichen Parameters unverändert bleiben, was
mit NOCOPY unmöglich ist.
8.1
347
Prozeduren und Funktionen
■ Die konkreten und formalen Parameter sind jeweils Datensätze, die entweder
implizit als Variable zur Schleifensteuerung oder mit %ROWTYPE deklariert
werden, wobei die Constraints auf die korrespondierenden Felder variieren.
■ Die Übergabe des konkreten Parameters verlangt eine implizite Datentypkonvertierung.
■ Das Unterprogramm gehört zu einem RPC (Remote Procedure Call). Ein RPC
ist ein Prozeduraufruf, der über einen Datenbank-Link zu einem Remote-Server
erfolgt. Weil die Parameter über das Netzwerk übertragen sind, lassen sie sich
nicht nach Referenz übergeben.
Tipp:
NOCOPY wird in den Fällen ignoriert, in denen das Unterprogramm Teil eines RPCs ist. Ändern Sie eine Anwendung so,
dass einige der Aufrufe RPCs und keine lokalen Aufrufe sind,
kann sich die Exception-Semantik ändern.
Vorteile von NOCOPY Der wichtigste Vorteil von NOCOPY ist die verbesserte
Performance. Wie das folgende Beispiel zeigt, bewährt sich das vor allem bei der Übergabe großer PL/SQL-Arrays:
–– Online verfügbar als CopyFast.sql
CREATE OR REPLACE PACKAGE CopyFast AS
–– Associative array of books.
TYPE BookArray IS
TABLE OF books%ROWTYPE;
–– Three procedures which take a
–– different ways. They each do
PROCEDURE PassBooks1(p_Parameter
PROCEDURE PassBooks2(p_Parameter
PROCEDURE PassBooks3(p_Parameter
parameter of BookArray, in
nothing.
IN BookArray);
IN OUT BookArray);
IN OUT NOCOPY BookArray);
–– Test procedure.
PROCEDURE Go;
END CopyFast;
CREATE OR REPLACE PACKAGE BODY CopyFast AS
PROCEDURE PassBooks1(p_Parameter IN BookArray) IS
BEGIN
NULL;
END PassBooks1;
PROCEDURE PassBooks2(p_Parameter IN OUT BookArray) IS
BEGIN
NULL;
348
8
Prozeduren, Funktionen und Packages erstellen
END PassBooks2;
PROCEDURE PassBooks3(p_Parameter IN OUT NOCOPY BookArray) IS
BEGIN
NULL;
END PassBooks3;
PROCEDURE Go IS
v_BookArray BookArray := BookArray(NULL);
v_Time1 NUMBER;
v_Time2 NUMBER;
v_Time3 NUMBER;
v_Time4 NUMBER;
BEGIN
–– Fill up the array with 50,001 copies of a record.
SELECT *
INTO v_BookArray(1)
FROM books
WHERE ISBN = '72230665';
v_BookArray.EXTEND(50000, 1);
–– Call each version of PassBooks, and time them.
–– DBMS_UTILITY.GET_TIME will return the current time, in
–– hundredths of a second.
v_Time1 := DBMS_UTILITY.GET_TIME;
PassBooks1(v_BookArray);
v_Time2 := DBMS_UTILITY.GET_TIME;
PassBooks2(v_BookArray);
v_Time3 := DBMS_UTILITY.GET_TIME;
PassBooks3(v_BookArray);
v_Time4 := DBMS_UTILITY.GET_TIME;
–– Output the results.
DBMS_OUTPUT.PUT_LINE('Time to pass IN: ' ||
TO_CHAR((v_Time2 - v_Time1) / 100));
DBMS_OUTPUT.PUT_LINE('Time to pass IN OUT: ' ||
TO_CHAR((v_Time3 v_Time2) / 100));
DBMS_OUTPUT.PUT_LINE('Time to pass IN OUT NOCOPY: ' ||
TO_CHAR((v_Time4 - v_Time3) / 100));
END Go;
END CopyFast;
Hinweis:
Dieses Beispiel verwendet zur Gruppierung zusammengehöriger
Prozeduren ein Package. Die Packages sind weiter unten
beschrieben. In Kapitel 6 finden Sie Informationen zu den
Collections und zum Einsatz der EXTENTS-Methode.
AnhangB enthält Informationen zu DBMS_UTILITY.
8.1
Prozeduren und Funktionen
349
Die PassBooks-Prozeduren tun nichts – die Prozedur nehmen nur einen Parameter
auf, bei dem es sich um einen Array von Büchern handelt. Der Parameter umfasst
50.001 Datensätze, ist also relativ groß. Der Unterschied zwischen den Prozeduren ist,
dass PassBooks1 den Parameter als IN, PassBooks2 als IN OUT und PassBooks3
als IN OUT NOCOPY übernimmt. Damit sollte PassBooks2 den Parameter nach
Wert und die beiden anderen sollten ihn nach Referenz übernehmen. Dies ist in den
Ergebnissen des Aufrufs von CopyFast.Go zu sehen:
SQL> BEGIN
2
CopyFast.Go;
3 END;
4 /
Time to pass IN: 0
Time to pass IN OUT: 1.27
Time to pass IN OUT NOCOPY: 0
PL/SQL procedure successfully completed.
Obwohl das konkrete Ergebnis auf Ihrem System anders aussehen kann, ist der Zeitaufwand bei der Übergabe nach Wert wesentlich höher als bei der Übergabe der INund IN OUT NOCOPY-Parameter nach Referenz.
Hinweis:
In Oracle10g wurden Änderungen am PL/SQL-Optimizer
vorgenommen, so dass nun leere Prozeduren optimiert werden.
Der Zeitunterschied bei Prozeduren kann nun in Oracle10g und
höher geringer sein, da Prozeduraufrufe entfernt werden. Es
empfiehlt sich, die Performance-Auswirkungen von NOCOPY
auf dem eigenen System und mit den eigenen Daten zu testen, um
die realistischen Zeiteinsparungen zu bestimmen.
Unterprogramme ohne Parameter
Gibt es für eine Prozedur keine Parameter, finden sich weder in der Prozedurdeklaration noch im Prozeduraufruf irgendwelche Klammern. Das gilt auch für Funktionen.
Dazu das folgende Beispiel:
–– Online verfügbar als noparams.sql
CREATE OR REPLACE PROCEDURE NoParamsP AS
BEGIN
DBMS_OUTPUT.PUT_LINE('No Parameters!');
END NoParamsP;
CREATE OR REPLACE FUNCTION NoParamsF
RETURN DATE AS
BEGIN
RETURN SYSDATE;
END NoParamsF;
350
8
Prozeduren, Funktionen und Packages erstellen
BEGIN
NoParamsP;
DBMS_OUTPUT.PUT_LINE('Calling NoParamsF on ' ||
TO_CHAR(NoParamsF, 'DD-MON-YYYY'));
END;
Hinweis:
Bei der CALL-Syntax in Oracle8i sind die Klammern optional.
Mehr dazu weiter unten.
Positionale Notation und Namensnotation
In allen bisherigen Beispielen sind die konkreten und formalen Argumente anhand
der Position zugeordnet. Nimmt man eine Deklaration wie die folgende:
–– Online verfügbar in CallMe.sql
CREATE OR REPLACE PROCEDURE CallMe(
p_ParameterA VARCHAR2,
p_ParameterB NUMBER,
p_ParameterC BOOLEAN,
p_ParameterD DATE) AS
BEGIN
NULL;
END CallMe;
und einen aufrufenden Block wie den folgenden:
–– Online verfügbar in CallMe.sql
DECLARE
v_Variable1 VARCHAR2(10);
v_Variable2 NUMBER(7,6);
v_Variable3 BOOLEAN;
v_Variable4 DATE;
BEGIN
CallMe(v_Variable1, v_Variable2, v_Variable3, v_Variable4);
END;
sind die konkreten mit den formalen Parametern anhand ihrer Position verknüpft: v_
Variable1 ist mit p_ParameterA, v_Variable2 ist mit p_ParameterB usw. verknüpft. Dies bezeichnet man als positionale Notation. Die positionale Notation wird
öfter eingesetzt und entspricht der Notation in anderen 3GLs, wie C und Java.
Alternativ können wir die Prozedur auch mit Hilfe der Namensnotation aufrufen:
–– Online verfügbar in CallMe.sql
DECLARE
v_Variable1 VARCHAR2(10);
8.1
Prozeduren und Funktionen
351
v_Variable2 NUMBER(7,6);
v_Variable3 BOOLEAN;
v_Variable4 DATE;
BEGIN
CallMe(p_ParameterA => v_Variable1,
p_ParameterB => v_Variable2,
p_ParameterC => v_Variable3,
p_ParameterD => v_Variable4);
END;
Bei der Namensnotation sind bei jedem Argument der formale und konkrete Parameter eingebunden. Das erlaubt uns, gegebenenfalls die Reihenfolge der Argumente zu
verändern. Der folgende Block ruft beispielsweise mit denselben Argumenten ebenfalls CallMe auf:
–– Online verfügbar in CallMe.sql
DECLARE
v_Variable1 VARCHAR2(10);
v_Variable2 NUMBER(7,6);
v_Variable3 BOOLEAN;
v_Variable4 DATE;
BEGIN
CallMe(p_ParameterB => v_Variable2,
p_ParameterC => v_Variable3,
p_ParameterD => v_Variable4,
p_ParameterA => v_Variable1);
END;
Falls gewünscht, können positionale Notation und Namensnotationin einem Aufruf
kombiniert werden. Das erste Argument muss anhand der Position angegeben werden, die restlichen Argumente kann man über ihren Namen angeben. Der folgende
Block illustriert diese Methode:
–– Online verfügbar in CallMe.sql
DECLARE
v_Variable1
v_Variable2
v_Variable3
v_Variable4
BEGIN
VARCHAR2(10);
NUMBER(7,6);
BOOLEAN;
DATE;
–– First 2 parameters passed by position, the second 2 are
–– passed by name.
CallMe(v_Variable1, v_Variable2,
p_ParameterC => v_Variable3,
p_ParameterD => v_Variable4);
END;
352
8
Prozeduren, Funktionen und Packages erstellen
Die Namensnotation ist ein weiteres Leistungsmerkmal in PL/SQL, das von Ada
stammt. Wann soll man mit der positionalen Notation und wann mit der Namensnotation arbeiten? Keine ist effizienter als die andere, so dass es letztlich eine Stilfrage
bleibt. Einige Unterschiede bezüglich der Stile sind in Tabelle 8-2 aufgeführt.
Tabelle 8-2:
Positionale Notation und Namensnotation im Vergleich.
Positionale Notation
Namensnotation
Steht und fällt mit einer guten Benennung
der konkreten Parameter, die zeigt, wofür
die einzelnen Parameter gedacht sind.
Zeigt eindeutig die Verbindung zwischen
konkreten und formalen Parametern.
Die Namen für die formalen und konkreten
Parameter sind unabhängig: Sie lassen sich
unabhängig voneinander ändern.
Kann schwieriger zu warten sein, weil alle
Aufrufe der Prozedur mit Hilfe der Namensnotation geändert werden müssen, wenn sich
die Namen der formalen Parameter ändern.
Kann schwieriger zu warten sein, weil alle
Aufrufe der Prozedur mit Hilfe der
positionalen Notation geändert werden
müssen, wenn sich die Reifenfolge der formalen
Parameter ändert.
Die Reihenfolge für die formalen und
konkreten Parameter ist unabhängig: Beide
lassen sich unabhängig voneinander ändern.
Knapper als die Namensnotation
Benötigt mehr Code, weil im Prozeduraufruf
sowohl die formalen als auch die konkreten
Parameter eingebunden sind. Dieses zusätzliche Kodieren dient dazu, den Zweck jedes
konkreten Parameters durch explizite Aufnahme des zugehörigen formalen Parameters zu
dokumentieren.
Parameter mit Standardwerten müssen am
Ende der Argumentliste stehen.
Erlaubt die Verwendung von Standardwerten
für formale Parameter, ungeachtet dessen,
welcher Parameter den Standardwert besitzt.
Ich persönlich arbeite mit der positionalen Notation, weil der Code knapper und kompakter ist. Wichtig ist jedoch, für den konkreten Parameter sinnvolle Namen zu wählen.
Übernimmt die Prozedur viele Argumente (z. B. mehr als zehn), ist die Namensnotation vorzuziehen, weil sich formale und konkrete Parameter besser zuordnen lassen. Prozeduren mit vielen Argumenten sind allerdings selten. Die Namensnotation bewährt
sich auch für Prozeduren mit Standardwerten (siehe nächster Abschnitt).
8.1
Prozeduren und Funktionen
353
Tipp:
Je mehr Parameter eine Prozedur besitzt, desto schwieriger ist
der Aufruf – prüfen Sie deshalb, ob alle benötigten Parameter
vorhanden sind. In solchen Fällen sollten Sie die Definition
eines Datensatztyps erwägen, in dem die Parameter als Felder
im Datensatz definiert sind. Dann können Sie für diesen
Datensatztyp einen einzigen Parameter verwenden. (Sollte die
aufrufende Umgebung nicht PL/SQL sein, können Sie vielleicht
keinen Datensatztyp einbinden). PL/SQL kennt bei der Anzahl
der Parameter keine Beschränkungen.
Standardwerte für Parameter
Ähnlich wie bei einer Variablendeklaration können die formalen Parameter für eine
Prozedur oder Funktion Standardwerte aufweisen. Hat ein Parameter einen Standardwert, muss er von der aufrufenden Umgebung nicht übergeben werden. Wird er übergeben, wird anstelle des Standardwerts der Wert des tatsächlichen Parameters verwendet. Ein Standardwert für einen Parameter bindet man mit folgender Syntax ein:
parameter_name [mode] [NOCOPY] parameter_type {:= | DEFAULT} initial_value
wobei parameter_name der Name des formalen Parameters, mode der Parametermodus (IN, OUT oder IN OUT), parameter_type der Parametertyp (entweder vor- oder
benutzerdefiniert) und initial_value der Wert ist, der dem formalen Parameter standardmäßig zugewiesen wird. Dazu verwendet man entweder := oder das Schlüsselwort
DEFAULT. Betrachten wir beispielsweise die Prozedur AddNewBook:
–– Online verfügbar in AddNewBook.sql
CREATE OR REPLACE PROCEDURE AddNewBook(
p_ISBN IN books.ISBN%TYPE,
p_Category IN books.category%TYPE := 'Oracle Server',
p_Title IN books.title%TYPE,
p_NumPages IN books.num_pages%TYPE,
p_Price IN books.price%TYPE,
p_Copyright IN books.copyright%TYPE DEFAULT
TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')),
p_Author1 IN books.author1%TYPE,
p_Author2 IN books.author2%TYPE := NULL,
p_Author3 IN books.author3%TYPE := NULL) AS
BEGIN
–– Insert a new row into the table using the supplied
–– parameters.
INSERT INTO books (isbn, category, title, num_pages, price,
copyright, author1, author2, author3)
VALUES (p_ISBN, p_Category, p_Title, p_NumPages, p_Price,
p_Copyright, p_Author1, p_Author2, p_Author3);
END AddNewBook;
354
8
Prozeduren, Funktionen und Packages erstellen
AddNewBook verfügt über vier Standardparameter: p_Category, p_Copyright, p_
Author2 und p_Author3. Die Standardwerte werden verwendet, wenn dem forma-
len Parameter im Prozeduraufruf kein konkreter Parameter zugeordnet ist. Wir können beispielsweise mit dem folgenden Block vermeiden, p_Author2 und p_Author3
zu übergeben:
–– Online verfügbar in AddNewBook.sql
BEGIN
AddNewBook('0000000000', 'Oracle Basics', 'A Really Nifty Book',
500, 34.99, 2004, 1);
END;
In diesem Fall wird für p_Author2 und p_Author3 NULL verwendet. Wir können
AddNewBook mit der benannten Notation aufrufen:
–– Online verfügbar in AddNewBook.sql
BEGIN
AddNewBook(p_ISBN => '0000000000',
p_Category => 'Oracle Basics',
p_Title => 'A Really Nifty Book',
p_NumPages => 500,
p_Price => 34.99,
p_Copyright => 2004,
p_Author1 => 1);
END;
Wird die positionale Notation verwendet, müssen alle Parameter mit Standardwerten,
die über keine zugeordneten konkreten Parameter verfügen, an das Ende der Parameterliste gesetzt werden, wie wir im obigen Beispiel im ersten Aufruf von AddNewBook
gesehen haben. Wenn wir für p_Category oder p_Copyright Standardwerte verwenden möchten, muss die Namensnotation wie folgt verwendet werden:
–– Online verfügbar in AddNewBook.sql
BEGIN
AddNewBook(p_ISBN => '0000000000',
p_Title => 'A Really Nifty Book',
p_NumPages => 500,
p_Price => 34.99,
p_Author1 => 1);
END;
Tipp:
Bei der Verwendung von Standardwerten sollten diese möglichst
die letzten Parameter in der Argumentliste sein. Damit kann man
entweder die positionale Notation oder die Namensnotation
verwenden.
8.1
Prozeduren und Funktionen
8.1.3
355
CALL-Anweisungen
Seit Oracle8i gibt es für den Aufruf von gespeicherten Unterprogrammen eine neue
SQL-Anweisung: die CALL-Anweisung, bei der mit einem PL/SQL-Wrapper sowohl
PL/SQL- als auch Java-Unterprogramme aufgerufen werden können. Die Syntax finden Sie im folgenden Diagramm:
subprogram_name ist ein eigenständiges oder in ein Package integriertes Unterprogramm. Es kann auch eine Methode des Objekttyps oder eine Remote-Datenbank
sein. Die argument_list ist eine durch Komma getrennte Argumentliste, und host_variable ist eine Hostvariable zum Abruf der Rückgabewerte von Funktionen. Die folgende SQL*Plus-Session zeigt zulässige und unzulässige Beispiele für die CALL-Anweisung. Dieses Beispiel verwendet den SQL*Plus-Befehl VARIABLE zur Deklaration
einer Hostvariablen. Weitere Informationen zu diesem Befehl und zu anderen Features von SQL*Plus finden Sie in der Oracle-Dokumentation.
–– Online verfügbar als calls.sql
SQL> CREATE OR REPLACE PROCEDURE CallProc1(p1 IN VARCHAR2 := NULL) AS
2 BEGIN
3
DBMS_OUTPUT.PUT_LINE('CallProc1 called with ' || p1);
4 END CallProc1;
5 /
Procedure created.
SQL> CREATE OR REPLACE PROCEDURE CallProc2(p1 IN OUT VARCHAR2) AS
2 BEGIN
3
DBMS_OUTPUT.PUT_LINE('CallProc2 called with ' || p1);
4
p1 := p1 || ' returned!';
5 END CallProc2;
6 /
Procedure created.
SQL> CREATE OR REPLACE FUNCTION CallFunc(p1 IN VARCHAR2)
2
RETURN VARCHAR2 AS
3 BEGIN
4
DBMS_OUTPUT.PUT_LINE('CallFunc called with ' || p1);
5
RETURN p1;
6 END CallFunc;
7 /
Function created.
SQL> – Some valid calls direct from SQL.
SQL> CALL CallProc1('Hello!');
CallProc1 called with Hello!
Call completed.
SQL> CALL CallProc1();
CallProc1 called with
Call completed.
356
8
Prozeduren, Funktionen und Packages erstellen
SQL> VARIABLE v_Output VARCHAR2(50);
SQL> CALL CallFunc('Hello!') INTO :v_Output;
CallFunc called with Hello!
Call completed.
SQL> PRINT v_Output
V_OUTPUT
–––––––––––––––––––––––––––––––––––
Hello!
SQL> CALL CallProc2(:v_Output);
CallProc2 called with Hello!
Call completed.
SQL> PRINT v_Output
V_OUTPUT
–––––––––––––––––––––––––––––––––Hello! returned!
SQL> – This is illegal
SQL> BEGIN
2
CALL CallProc1();
3 END;
4 /
CALL CallProc1();
*
ERROR at line 2:
ORA-06502: line 2, column 8:
PLS-00103: Encountered the symbol "CALLPROC1" when expecting one of the
following:
:= . ( @ % ;
The symbol ":=" was substituted for "CALLPROC1" to continue.
SQL> – But these are legal
SQL> DECLARE
2
v_Result VARCHAR2(50);
3 BEGIN
4
EXECUTE IMMEDIATE 'CALL CallProc1(''Hello from PL/SQL'')';
5
EXECUTE IMMEDIATE
6
'CALL CallFunc(''Hello from PL/SQL'') INTO :v_Result'
7
USING OUT v_Result;
8 END;
9 /
CallProc1 called with Hello from PL/SQL
CallFunc called with Hello from PL/SQL
PL/SQL procedure successfully completed.
Dieses Beispiel veranschaulicht die folgenden Punkte:
■ CALL ist eine SQL-Anweisung. Sie ist in einem PL/SQL-Block nicht gültig, jedoch
bei der Ausführung von dynamischem SQL, in diesem Fall der Anweisung
EXECUTE IMMEDIATE. (Innerhalb eines PL/SQL-Blocks können Sie das
Unterprogramm mit der PL/SQL-Syntax aufrufen). Mehr über dynamisches SQL
finden in Sie Kapitel 13.
8.2
357
Packages
■ Die Klammern werden immer benötigt, selbst wenn das Unterprogramm keine
Parameter übernimmt (oder für alle Argumente Standardwerte vorgibt).
■ Die INTO-Klausel wird nur für die Ausgabevariablen von Funktionen verwendet.
IN OUT- oder OUT-Parameter werden als Teil der argument_list angegeben.
8.1.4
Prozeduren und Funktionen im Vergleich
Prozeduren und Funktionen haben viele gemeinsame Leistungsmerkmale:
■ Beide können über den OUT-Parameter mehr als einen Wert zurückliefern.
■ Beide können einen deklarativen, ausführbaren und einen Exception HandlingBereich enthalten.
■ Beide können Standardwerte akzeptieren.
■ Beide lassen sich mit der positionalen oder der Namenskonvention aufrufen.
■ Beide können NOCOPY-Parameter akzeptieren.
Wann werden Funktionen und wann Prozeduren verwendet? Das hängt in erste Linie
davon ab, wie viele Werte das Unterprogramm zurückgeben soll und wie diese Werte
verwendet werden. Als Faustregel gilt: Bei mehr als einem Rückgabewert verwendet
man eine Prozedur. Gibt es nur einen Rückgabewert, kann eine Funktion verwendet
werden. Obwohl es zulässig ist, dass eine Funktion mehrere OUT-Parameter hat (und
damit mehr als einen Wert zurückliefert), ist das kein besonders guter Programmierstil. Funktionen können auch in einer SQL-Anweisung aufgerufen werden. Weitere
Informationen hierzu finden Sie in Kapitel 9.
8.2
Packages
Packages sind ein weiteres Ada-Konstrukt, das in das Design von PL/SQL eingeflossen
ist. Ein Package ist ein PL/SQL-Konstrukt, mit dessen Hilfe sich zusammengehörige
Objekte gemeinsam speichern lassen. Ein Package besteht aus zwei separaten Teilen:
der Spezifikation und dem Body. Jedes Element wird im Data Dictionary separat gespeichert. Im Gegensatz zu Prozeduren und Funktionen, die lokal in einem Block enthalten oder in der Datenbank gespeichert sind, kann man ein Package nur speichern;
es kann nicht lokal sein. Neben der Möglichkeit, zusammengehörige Objekte zu gruppieren, sind Packages bei Abhängigkeiten weniger restriktiv als gespeicherte Unterprogramme. Zudem können sie die Performance verbessern, was im nächsten Kapitel erörtert wird.
Ein Package ist im Wesentlichen ein benannter deklarativer Bereich. Alles, was sich im
deklarativen Teil eines Blocks befindet, kann auch in ein Package eingehen. Dazu ge-
358
8
Prozeduren, Funktionen und Packages erstellen
hören Prozeduren, Funktionen, Cursor, Typen und Variablen. Diese Objekte in ein
Package zu packen hat den Vorteil, dass man sie in anderen PL/SQL-Blöcken referenzieren kann, dadurch stellen Packages für PL/SQL auch globale Variablen (innerhalb
einer Datenbanksitzung) zur Verfügung.
8.2.1
Package-Spezifikation
Die Package-Spezifikation (auch als Package-Header bezeichnet) enthält Informationen zum Packageinhalt. Sie enthält jedoch keinen Code für Unterprogramme. Betrachten wir folgendes Beispiel:
–– Online verfügbar in InventoryOps.sql
CREATE OR REPLACE PACKAGE InventoryOps AS
–– Modifies the inventory data for the specified book.
PROCEDURE UpdateISBN(p_ISBN IN inventory.isbn%TYPE,
p_Status IN inventory.status%TYPE,
p_StatusDate IN inventory.status_date%TYPE,
p_Amount IN inventory.amount%TYPE);
–– Deletes the inventory data for the specified book.
PROCEDURE DeleteISBN(p_ISBN IN inventory.isbn%TYPE);
–– Exception raised by UpdateISBN or DeleteISBN when the specified
–– ISBN is not in the inventory table.
e_ISBNNotFound EXCEPTION;
TYPE t_ISBNTable IS TABLE OF inventory.isbn%TYPE
INDEX BY BINARY_INTEGER;
–– Returns an array containing the books with the specified status.
PROCEDURE StatusList(p_Status IN inventory.status%TYPE,
p_Books OUT t_ISBNTable,
p_NumBooks OUT BINARY_INTEGER);
END InventoryOps;
InventoryOps enthält drei Prozeduren, einen Typ und eine Exception. Die allgemei-
ne Syntax zum Erstellen eines Package-Headers wird im folgenden Diagramm dargestellt:
package_name ist der Name des Package. Die Elemente im Package (Prozedur- und
Funktionsspezifikationen, Variablen usw.) sind die gleichen wie im deklarativen Bereich eines anonymen Blocks. Für den Package-Header gelten zudem die gleichen Syntaxregeln wie für den deklarativen Bereich, außer für die Deklarationen von Funktionen und Prozeduren. Diese Regeln lauten wie folgt:
8.2
359
Packages
■ Package-Elemente können in beliebiger Reihenfolge erscheinen. In einem
deklarativen Abschnitt muss ein Objekt vor seiner Referenzierung deklariert
werden. Enthält ein Cursor beispielsweise eine Variable als Teil der WHEREKlausel, muss die Variable vor der Cursordeklaration deklariert werden.
■ Es müssen nicht alle Elementtypen vorhanden sein. Ein Package kann z. B. nur
Prozedur- und Funktionsspezifikationen enthalten, ohne dass Exceptions oder
Typen deklariert werden.
■ Alle Deklarationen für Prozeduren und Funktionen müssen Vorwärtsdeklarationen sein. Eine Vorwärtsdeklaration beschreibt das Unterprogramm und dessen
Argumente (falls vorhanden), enthält aber nicht den Code. Diese Regel unterscheidet sich vom deklarativen Bereich eines Blocks, in dem Vorwärtsdeklarationen und der konkrete Code für Prozeduren oder Funktionen enthalten sein
können. Der Code, der die Prozeduren und Funktionen des Package
implementiert, befindet sich im Package-Body.
8.2.2
Package-Body
Der Package-Body ist ein eigenes Data-Dictionary-Objekt. Es kann erst kompiliert
werden, wenn der Package-Header erfolgreich kompiliert wurde. Der Body enthält
den Code für die Vorwärtsdeklaration des Unterprogramms im Package-Header.
Weiterhin kann er zusätzliche Deklarationen enthalten, die für den Package-Body global, nicht aber in der Spezifikation sichtbar sind. Das folgende Beispiel zeigt den Package-Body für InventoryOps:
360
8
Prozeduren, Funktionen und Packages erstellen
–– Online verfügbar in InventoryOps.sql
CREATE OR REPLACE PACKAGE BODY InventoryOps AS
–– Modifies the inventory data for the specified book.
PROCEDURE UpdateISBN(p_ISBN IN inventory.isbn%TYPE,
p_Status IN inventory.status%TYPE,
p_StatusDate IN inventory.status_date%TYPE,
p_Amount IN inventory.amount%TYPE) IS
BEGIN
UPDATE inventory
SET status = p_Status, status_date = p_StatusDate, amount = p_Amount
WHERE isbn = p_ISBN;
–– Check for no books updated, and raise the exception.
IF SQL%ROWCOUNT = 0 THEN
RAISE e_ISBNNotFound;
END IF;
END UpdateISBN;
–– Deletes the inventory data for the specified book.
PROCEDURE DeleteISBN(p_ISBN IN inventory.isbn%TYPE) IS
BEGIN
DELETE FROM inventory
WHERE isbn = p_ISBN;
–– Check for no books deleted, and raise the exception.
IF SQL%ROWCOUNT = 0 THEN
RAISE e_ISBNNotFound;
END IF;
END DeleteISBN;
–– Returns an array containing the books with the specified status.
PROCEDURE StatusList(p_Status IN inventory.status%TYPE,
p_Books OUT t_ISBNTable,
p_NumBooks OUT BINARY_INTEGER) IS
v_ISBN inventory.isbn%TYPE;
CURSOR c_Books IS
SELECT isbn
FROM inventory
WHERE status = p_Status;
BEGIN
/* p_NumBooks will be the array index. It will start at
* 0, and be incremented each time through the fetch loop.
* At the end of the loop, it will have the number of rows
* fetched, and therefore the number of rows returned in
* p_Books. */
p_NumBooks := 0;
OPEN c_Books;
LOOP
8.2
Packages
361
FETCH c_Books INTO v_ISBN;
EXIT WHEN c_Books%NOTFOUND;
p_NumBooks := p_NumBooks + 1;
p_Books(p_NumBooks) := v_ISBN;
END LOOP;
CLOSE c_Books;
END StatusList;
END InventoryOps;
Der Package-Body enthält den Code für die Vorwärtsdeklarationen im Package-Header und kann zusätzliche Variablen, Cursor, Typen oder Unterprogramme enthalten.
Objekte im Header, bei denen es sich nicht um Vorwärtsdeklarationen handelt (wie
die Exception e_ISBNNotFound) können direkt im Package-Body referenziert werden.
Der Package-Body ist optional. Enthält der Package-Header keine Prozeduren oder
Funktionen (nur Variablendeklarationen, Cursor, Typen usw.), muss kein Body vorhanden sein. Diese Technik ist sehr nützlich für die Deklaration globaler Variablen, da
alle Objekte in einem Package außerhalb des Package sichtbar sind. Geltungsbereich
und Sichtbarkeit der in einem Package integrierten Elemente werden im nächsten Abschnitt erörtert.
Jede Vorwärtsdeklaration im Package-Header muss im Package-Body ausgearbeitet
sein. Die Spezifikation für die Prozedur oder Funktion muss in beiden Fällen gleich
sein. Das umfasst den Namen des Unterprogramms, die Namen der Parameter und
die Parametermodi. Der folgende Package-Header passt beispielsweise nicht zum Package-Body, weil der Body für FunctionA eine abweichende Parameterliste verwendet:
–– Online verfügbar als packageError.sql
CREATE OR REPLACE PACKAGE PackageA AS
FUNCTION FunctionA(p_Parameter1 IN NUMBER,
p_Parameter2 IN DATE)
RETURN VARCHAR2;
END PackageA;
CREATE OR REPLACE PACKAGE BODY PackageA AS
FUNCTION FunctionA(p_Parameter1 IN CHAR)
RETURN VARCHAR2;
END PackageA;
Wenn wir versuchen, PackageA wie oben anzulegen, erhalten wir für den PackageBody folgende Fehlermeldungen:
PLS-00328: A subprogram body must be defined for the forward
declaration of FUNCTIONA.
PLS-00323: subprogram or cursor 'FUNCTIONA' is declared in a
package specification and must be defined in the package
body.
362
8
8.2.3
Prozeduren, Funktionen und Packages erstellen
Packages und Geltungsbereich
Jedes im Package-Header deklarierte Objekt befindet sich im Geltungsbereich und ist
außerhalb des Package sichtbar. Dazu qualifiziert man das Objekt mit dem Packagenamen. Wir können beispielsweise InventoryOps.DeleteISBN aus dem folgenden
PL/SQL-Block aufrufen:
BEGIN
InventoryOps.DeleteISBN('78824389');
END;
Der Prozeduraufruf ist der gleiche wie für eine eigenständige Prozedur. Der einzige
Unterschied ist, dass der Packagename vorangestellt wird. In einem Package integrierte Prozeduren können Standardparameter enthalten und mit der positionalen Notation oder der Namensnotation aufgerufen werden.
Das gilt auch für benutzerdefinierte Typen, die im Package definiert sind. Um beispielsweise StatusList aufzurufen, müssen wir eine Variable vom Typ InventoryOps.t _ISBNTable deklarieren.
–– Online verfügbar als callSL.sql
DECLARE
v_BooksInStock InventoryOps.t_ISBNTable;
v_NumBooks BINARY_INTEGER;
BEGIN
–– Fill the PL/SQL table with the ISBNs of the books which
–– are in stock.
InventoryOps.StatusList('IN STOCK', v_BooksInStock, v_NumBooks);
–– And print them out.
FOR v_LoopCounter IN 1..v_NumBooks LOOP
DBMS_OUTPUT.PUT_LINE('ISBN ' || v_BooksInStock(v_LoopCounter) ||
' is in stock');
END LOOP;
END;
Im Package-Body können die Objekte im Header ohne Packagenamen referenziert
werden. So können beispielsweise die Prozeduren UpdateISBN und DeleteISBN die
Exception einfach durch e_ISBNNotFound und nicht durch InventoryOps.e_ISBNNotFound referenzieren. Es kann auch der vollständig angegebene Name verwendet werden.
8.2
Packages
363
Geltungsbereich von Objekten im Package-Body
InventoryOps.UpdateISBN und InventoryOps.StatusList validieren den
übergebenen Status nicht. Wir können dies einbauen, indem wir in den Package-Body
eine Prozedur einfügen, wie im Folgenden gezeigt wird:
–– Online verfügbar in InventoryOps2.sql
CREATE OR REPLACE PACKAGE BODY InventoryOps AS
–– Validates the supplied status and raises an error if it is
–– not IN STOCK, BACKORDERED, or FUTURE.
PROCEDURE ValidateStatus(p_Status IN inventory.status%TYPE) IS
BEGIN
IF p_Status = 'IN STOCK' OR
p_Status = 'BACKORDERED' OR
p_Status = 'FUTURE' THEN
RETURN; – No error
ELSE
RAISE_APPLICATION_ERROR(20000,
'Supplied status ' || p_Status || ' is not valid');
END IF;
END ValidateStatus;
–– Modifies the inventory data for the specified book.
PROCEDURE UpdateISBN(p_ISBN IN inventory.isbn%TYPE,
p_Status IN inventory.status%TYPE,
p_StatusDate IN inventory.status_date%TYPE,
p_Amount IN inventory.amount%TYPE) IS
BEGIN
ValidateStatus(p_Status);
UPDATE inventory
SET status = p_Status, status_date = p_StatusDate, amount = p_
Amount
WHERE isbn = p_ISBN;
–– Check for no books updated, and raise the exception.
IF SQL%ROWCOUNT = 0 THEN
RAISE e_ISBNNotFound;
END IF;
END UpdateISBN;
...
–– Returns a PL/SQL table containing the books with the specified
–– status.
PROCEDURE StatusList(p_Status IN inventory.status%TYPE,
p_Books OUT t_ISBNTable,
p_NumBooks OUT BINARY_INTEGER) IS
364
8
Prozeduren, Funktionen und Packages erstellen
v_ISBN inventory.isbn%TYPE;
CURSOR c_Books IS
SELECT isbn
FROM inventory
WHERE status = p_Status;
BEGIN
ValidateStatus(p_Status);
...
END StatusList;
END InventoryOps;
ValidateStatus ist lokal zum Package-Body deklariert. Der Geltungsbereich ist da-
her der Package-Body. Damit kann diese Prozedur auch von anderen Prozeduren im
Body aufgerufen werden (nämlich UpdateISBN und StatusList), ist aber außerhalb des Bodys nicht sichtbar.
8.2.4
In Packages integrierte Unterprogramme
überladen
Innerhalb eines Package lassen sich Prozeduren und Funktionen überladen. Das heißt,
dass es dort mehr als eine gleichnamige Prozedur oder Funktion gibt, die aber verschiedene Parameter enthalten. Diese Funktionalität ist äußerst hilfreich, da hiermit
dieselbe Operation auf Objekte verschiedenen Typs angewendet werden kann. Angenommen StatusList soll beispielsweise einen Array von Blöcken zurückgeben oder
ein geöffneter Cursor soll die Bücher mit dem angegebenen Status auswählen. Hier
können wir InventoryOps wie folgt modifizieren:
–– Online verfügbar in overload.sql
CREATE OR REPLACE PACKAGE InventoryOps AS
...
–– Returns an array containing the books with the specified status.
PROCEDURE StatusList(p_Status IN inventory.status%TYPE,
p_Books OUT t_ISBNTable,
p_NumBooks OUT BINARY_INTEGER);
TYPE c_ISBNCur IS REF CURSOR;
–– Returns an opened cursor containing the books with the specified
–– status.
PROCEDURE StatusList(p_Status IN inventory.status%TYPE,
p_BookCur OUT c_ISBNCur);
END InventoryOps;
CREATE OR REPLACE PACKAGE BODY InventoryOps AS
...
8.2
Packages
365
–– Returns an array containing the books with the specified status.
PROCEDURE StatusList(p_Status IN inventory.status%TYPE,
p_Books OUT t_ISBNTable,
p_NumBooks OUT BINARY_INTEGER) IS
v_ISBN inventory.isbn%TYPE;
CURSOR c_Books IS
SELECT isbn
FROM inventory
WHERE status = p_Status;
BEGIN
ValidateStatus(p_Status);
/* p_NumBooks will be the array index. It will start at
* 0, and be incremented each time through the fetch loop.
* At the end of the loop, it will have the number of rows
* fetched, and therefore the number of rows returned in
* p_Books. */
p_NumBooks := 0;
OPEN c_Books;
LOOP
FETCH c_Books INTO v_ISBN;
EXIT WHEN c_Books%NOTFOUND;
p_NumBooks := p_NumBooks + 1;
p_Books(p_NumBooks) := v_ISBN;
END LOOP;
CLOSE c_Books;
END StatusList;
–– Returns an opened cursor containing the books with the specified
–– status.
PROCEDURE StatusList(p_Status IN inventory.status%TYPE,
p_BookCur OUT c_ISBNCur) IS
BEGIN
ValidateStatus(p_Status);
OPEN p_BookCur FOR
SELECT isbn
FROM inventory
WHERE status = p_Status;
END StatusList;
END InventoryOps;
Die folgende SQL*Plus-Sitzung zeigt die beiden Aufrufe von InventoryOps.StatusList:
–– Online verfügbar in overload.sql
SQL> DECLARE
2
v_BooksInStock InventoryOps.t_ISBNTable;
3
v_NumBooks BINARY_INTEGER;
4
v_BookCur InventoryOps.c_ISBNCur;
5
v_ISBN inventory.isbn%TYPE;
366
8
Prozeduren, Funktionen und Packages erstellen
6 BEGIN
7
DBMS_OUTPUT.PUT_LINE('First version of StatusList:');
8
–– Fill the PL/SQL table with the ISBNs of the books which
9
–– are backordered.
10
InventoryOps.StatusList('BACKORDERED', v_BooksInStock, v_NumBooks);
11
12
–– And print them out.
13
FOR v_LoopCounter IN 1..v_NumBooks LOOP
14
DBMS_OUTPUT.PUT_LINE(' ISBN ' || v_BooksInStock(v_LoopCounter) ||
15
' is backordered');
16
END LOOP;
17
18
DBMS_OUTPUT.PUT_LINE('Second version of StatusList:');
19
–– Get an opened cursor with the ISBNs of the books which are
20
–– backordered.
21
InventoryOps.StatusList('BACKORDERED', v_BookCur);
22
23
–– And print them out.
24
LOOP
25
FETCH v_BookCur INTO v_ISBN;
26
EXIT WHEN v_BookCur%NOTFOUND;
27
DBMS_OUTPUT.PUT_LINE(' ISBN ' || v_ISBN || ' is backordered');
28
END LOOP;
29
CLOSE v_BookCur;
30 END;
31 /
First version of StatusList:
ISBN 72121203
is backordered
ISBN 78824389
is backordered
Second version of StatusList:
ISBN 72121203
is backordered
ISBN 78824389
is backordered
PL/SQL procedure successfully completed.
Das Überladen kann eine hilfreiche Technik sein, wenn die gleiche Operation mit unterschiedlichen Argumenttypen ausgeführt werden kann. Das Überladen unterliegt allerdings auch verschiedenen Restriktionen:
■ Sie können nicht zwei Unterprogramme überladen, wenn sich deren Parameter
nur durch den Namen oder Modus unterscheiden. So lassen sich beispielsweise
die beiden folgenden Prozeduren nicht überladen:
PROCEDURE OverloadMe(p_TheParameter IN NUMBER);
PROCEDURE OverloadMe(p_TheParameter OUT NUMBER);
■ Sie können nicht zwei Funktionen überladen, die sich nur im Rückgabewert
unterscheiden. So lassen sich beispielsweise die beiden folgenden Funktionen
nicht überladen:
FUNCTION OverloadMeToo RETURN DATE;
FUNCTION OverloadMeToo RETURN NUMBER;
8.2
367
Packages
■ Die Parameter von überladenen Funktionen müssen aus verschiedenen
Typfamilien stammen – die gleiche Familie kann man nicht überladen. Da im
folgenden Beispiel sowohl CHAR als auch VARCHAR2 aus der gleichen Familie
stammen, können die folgenden Prozeduren nicht überladen werden:
PROCEDURE OverloadChar(p_TheParameter IN CHAR);
PROCEDURE OverloadChar(p_TheParameter IN VARCHAR2);
■ In Oracle10gR1 können Sie aber zwei Unterprogramme überladen, wenn deren
Parameter sich nur im numerischen Datentyp unterscheiden, wie BINARY_
FLOAT und BINARY_DOUBLE. Dies ist vor allem bei mathematischen
Funktionen nützlich.
Hinweis:
Der PL/SQL-Compiler erlaubt derzeit das Erstellen von Packages
mit Unterprogrammen, die diese Regeln verletzen. Aber die Runtime
Engine kann die Referenzen nicht auflösen und meldet immer den
Fehler „PLS-307: Too many declarations of 'subprogram' match this
call“.
Objekttypen und Überladung
In Packages integrierte Unterprogramme lassen sich auch basierend auf benutzerdefinierten Objekttypen überladen. Angenommen, wir erstellen die beiden folgenden Objekttypen:
–– Online verfügbar in objectOverload.sql
CREATE OR REPLACE TYPE t1 AS OBJECT (
f NUMBER
);
CREATE OR REPLACE TYPE t2 AS OBJECT (
f NUMBER
);
Jetzt können wir ein Package und einen Package-Body mit Prozeduren anlegen, die
aufgrund des Objekttyps ihrer Parameter überladen werden:
–– Online verfügbar in objectOverload.sql
CREATE OR REPLACE PACKAGE Overload AS
PROCEDURE Proc(p_Parameter1 IN t1);
PROCEDURE Proc(p_Parameter1 IN t2);
END Overload;
CREATE OR REPLACE PACKAGE BODY Overload AS
PROCEDURE Proc(p_Parameter1 IN t1) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Proc(t1): ' || p_Parameter1.f);
END Proc;
368
8
Prozeduren, Funktionen und Packages erstellen
PROCEDURE Proc(p_Parameter1 IN t2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Proc(t2): ' || p_Parameter1.f);
END Proc;
END Overload;
Wie das folgende Beispiel zeigt, wird die korrekte Prozedur entsprechend dem Argumenttyp aufgerufen:
–– Online verfügbar in objectOverload.sql
SQL> DECLARE
2
v_Obj1 t1 := t1(1);
3
v_OBj2 t2 := t2(2);
4 BEGIN
5
Overload.Proc(v_Obj1);
6
Overload.proc(v_Obj2);
7 END;
8 /
Proc(t1): 1
Proc(t2): 2
PL/SQL procedure successfully completed.
Weitere Informationen zu Packages finden Sie in den Kapiteln 14 und 15.
8.2.5
Initialisierung von Packages
Wird ein in ein Package integriertes Unterprogramm erstmals aufgerufen oder eine
Referenz auf eine im Package integrierte Variable oder einen Typ vorgenommen, instanziert man das Package. Damit wird das Package von der Platte in den Speicher gelesen und der kompilierte Code des aufgerufenen Unterprogramms ausgeführt. An
diesem Punkt ist für alle im Package definierten Variablen Speicher reserviert. Jede Sitzung besitzt eine eigene Kopie der im Package integrierten Variablen, was sicherstellt,
dass zwei Sessions, die das gleiche Unterprogramm ausführen, unterschiedliche Speicherorte verwenden können.
In vielen Fällen muss vor der ersten Instanzierung eines Package in einer Sitzung ein
Initialisierungscode ausgeführt werden. Dazu fügen Sie in den Package-Body nach allen anderen Objekten einen Initialisierungsbereich ein. Die dazugehörige Syntax lautet:
CREATE OR REPLACE PACKAGE BODY package_name {IS | AS}
...
BEGIN
initialization_code;
END [package_name];
8.2
Packages
369
package_name ist der Name des Package und initialization_code ist der auszuführende
Code. Das folgende Package implementiert beispielsweise eine Funktion für Zufallszahlen:
–– Online verfügbar als Random.sql
CREATE OR REPLACE PACKAGE Random AS
–– Random number generator. Uses the same algorithm as the
–– rand() function in C.
–– Used to change the seed. From a given seed, the same
–– sequence of random numbers will be generated.
PROCEDURE ChangeSeed(p_NewSeed IN NUMBER);
–– Returns a random integer between 1 and 32767.
FUNCTION Rand RETURN NUMBER;
–– Same as Rand, but with a procedural interface.
PROCEDURE GetRand(p_RandomNumber OUT NUMBER);
–– Returns a random integer between 1 and p_MaxVal.
FUNCTION RandMax(p_MaxVal IN NUMBER) RETURN NUMBER;
–– Same as RandMax, but with a procedural interface.
PROCEDURE GetRandMax(p_RandomNumber OUT NUMBER,
p_MaxVal IN NUMBER);
END Random;
CREATE OR REPLACE PACKAGE BODY Random AS
/* Used for calculating the next number. */
v_Multiplier CONSTANT NUMBER := 22695477;
v_Increment
CONSTANT NUMBER := 1;
/* Seed used to generate random sequence. */
v_Seed
number := 1;
PROCEDURE ChangeSeed(p_NewSeed IN NUMBER) IS
BEGIN
v_Seed := p_NewSeed;
END ChangeSeed;
FUNCTION Rand RETURN NUMBER IS
BEGIN
v_Seed := MOD(v_Multiplier * v_Seed + v_Increment,
(2 ** 32));
RETURN BITAND(v_Seed/(2 ** 16), 32767);
END Rand;
PROCEDURE GetRand(p_RandomNumber OUT NUMBER) IS
BEGIN
–– Simply call Rand and return the value.
p_RandomNumber := Rand;
END GetRand;
370
8
Prozeduren, Funktionen und Packages erstellen
FUNCTION RandMax(p_MaxVal IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN MOD(Rand, p_MaxVal) + 1;
END RandMax;
PROCEDURE GetRandMax(p_RandomNumber OUT NUMBER,
p_MaxVal IN NUMBER) IS
BEGIN
–– Simply call RandMax and return the value.
p_RandomNumber := RandMax(p_MaxVal);
END GetRandMax;
BEGIN
/* Package initialization. Initialize the seed to the current
time in seconds. */
ChangeSeed(TO_NUMBER(TO_CHAR(SYSDATE, 'SSSSS')));
END Random;
Zum Abruf einer Zufallszahl rufen Sie einfach Random.Rand auf. Die Zufallszahlenfolge wird von der Ausgangszahl gesteuert – für eine gegebene Ausgangszahl wird stets
die gleiche Folge generiert. Um mehr Zufallszahlen zu erhalten, müssen wir die Ausgangszahl bei jeder Instanzierung des Package auf einen anderen Wert initialisieren.
Hierzu wird die ChangeSeed-Prozedur aus dem Initialisierungsbereich des Package
aufgerufen.
Hinweis:
Oracle8i enthält ein vordefiniertes Package namens DBMS_
RANDOM, das sich gleichfalls für Zufallszahlen einsetzen lässt.
Weitere Informationen zu Built-In Packages finden Sie in
Anhang B.
8.3
Zusammenfassung
In diesem Kapitel untersuchten wir drei Typen von benannten PL/SQL-Blöcken: Prozeduren, Funktionen und Packages. Wir besprachen die Syntax zum Erstellen dieser
Konstrukte und stellten die verschiedenen Arten der Parameterübergabe vor. Im folgenden Kapitel erfahren Sie mehr über die Verwendung von Prozeduren, Funktionen
und Packages. Es werden die verschiedenen Unterprogrammtypen erörtert, wie sie im
Data Dictionary gespeichert werden und wie man gespeicherte Unterprogramme aus
SQL-Anweisungen ruft. In Kapitel 10 beschäftigen wir uns mit dem vierten Typ von
benannten Blöcken: den Datenbank-Triggern.