Wir wollen jetzt all das, was bisher gelernt wurde, zum Erzeugen einer Prozedur nutzen.
Wir haben zwar schon einmal über das Erzeugen von Prozeduren und Funktionen gesprochen, wollen hier nun aber die Eigenarten dieser benannten PL/SQL-Blöcke näher betrachten.
Beispiel
Unsere Prozedur soll
- Parameter verarbeiten, die wir beim Aufruf übergeben
- mit Hilfe des übergebenen Arguments einen Datensatz verändern (UPDATE)
Bevor wir beginnen, brauchen wir noch die Tabelle, deren Datensätze unsere Prozedur verändern soll.
create table farbe (f_id number(5), f_name varchar2(30), f_am_lager number(3));
Jetzt packen wir drei Datensätze hinein
SQL>insert into farbe (f_id, f_name, f_am_lager) values (1,'ROT',3); SQL>insert into farbe (f_id, f_name, f_am_lager) values (2,'BLAU',1); SQL>insert into farbe (f_id, f_name, f_am_lager) values (3,'WEISS',0);
Die Aufgabe unserer Prozedur ist es, die übergebene Farbnummer zu nutzen, um den Bestand am Lager um die Menge des verkauft Parameters zu reduzieren. Wenn nicht genügend Farbe am Lager ist, muß eine Fehlermeldung zurückgegeben werden.
Beginnen wir mit dem Rahmen:
create or replace procedure f_verk
(i_f_id IN number,
i_verkauft IN number) is
begin
null;
end;
/
Wenn wir den obenstehenden Code ausführen, wird die Prozedur erzeugt. Sie tut allerdings nichts.
Das Kommando DESC, mit dem wir uns die Struktur von Tabellen anzeigen lassen können, funktioniert auch mit Prozeduren und Funktionen. In diesem Fall sehen wir die Schnittstelle, also die möglichen Ein- und Ausgabeargumente:
SQL> desc f_verk; PROCEDURE f_verk Argument Name Type In/Out Default? ------------------- -------- ---------------- i_f_ID NUMBER IN i_verkauft NUMBER IN SQL>
Um den Source-Code zu sehen, hilft ein Blick in das Data Dictionary:
Select text from user_source where name = 'F_VERK';
Die hier besprochene Herangehensweise wird auch häufig “stubbing” genannt. Ein Prozess, die einzelnen Teile einer Prozedur Schritt für Schritt hinzuzufügen. Dies macht gleichzeitig das Debuggen sehr viel einfacher.
Nun, da wir den Rahmen gesteckt haben, können wir ein wenig Programmierlogik hinzufügen. Die Prozedur braucht einen Cursor, um die Details aus der Datenbank zu selektieren.
create or replace procedure f_verk
(i_f_id IN number,
i_verkauft IN number) is
cursor c_paint(i_paint_id IN farbe.f_id%type) is
select f_am_lager
from farbe
where f_id = i_paint_id;
l_qty farbe.f_am_lager%type;
error_code number := SQLCODE;
error_mess varchar2(200) := SQLERRM;
no_stock exception;
---------------------
begin
if c_paint%isopen then
close c_paint;
end if;
open c_paint(i_f_id);
fetch c_paint into l_qty;
if l_qty < i_verkauft then
raise no_stock;
end if;
update farbe
set f_am_lager = f_am_lager - i_verkauft
where f_id = i_f_id;
---------------------
exception
when no_stock then
raise_application_error(-20001,'Nicht genug am Lager');
when others then
dbms_output.put_line(to_char(sqlcode||sqlerrm(sql_code)));
end;
/
Der Code sollte problemlos compiliert werden. Wieder, um den SourceCode zu sehen:
Select text from user_source where name = 'F_VERK';
Betrachten wir die Prozedur noch mal genauer:
- Der Cursor, den wir verwenden, ist dynamisch deklariert, d.h. wir übergeben dem Cursor einen Parameter, anders als bei statischen Cursorn.
- Die Variable, in der wir den aktuellen Lagerbestand speichern, ist als verankerte Variable deklariert, es ist verankert mit dem Datentyp der Spalte der Tabelle FARBE.
- Wir haben eine selbstdefinierte Ausnahme no_Stock deklariert. Sie wird ausgelöst, wenn der Lagerbestand geringer als die zu verkaufende Anzahl Farben ist.
- Im Ausnahmebehandlungsteil lösen wir den Fehler -20001 mit einer eigenen Fehlermeldung aus, wenn die Ausnahme no_stock vorliegt. Erinnern Sie sich, die Nummern -20001 bis -29999 sind reserviert für benutzereigene Fehlermeldungen.
- Zu guter Letzt enthält unsere Prozedur einen WHEN OTHERS Handler, dieser reagiert auf alle anderen Fehler, die auftreten können und liefert den Original ORACLE-Fehlercode an den Aufrufer zurück.
Jetzt beginnen wir zu testen: In unserer Tabelle steht
SQL> select * from farbe; F_ID F_NAME F_AM_LAGER --------- ------------------------------ -------------- 1 ROT 3 2 BLAU 1 3 WEISS 0 SQL>
Zuerst wollen wir einen Eimer roter Farbe verkaufen
SQL> execute f_verk(1,1); PL/SQL procedure successfully completed. SQL> select * from farbe; F_ID F_NAME F_AM_LAGER --------- ------------------------------ -------------- 1 ROT 2 2 BLAU 1 3 WEISS 0 SQL>
Alternativ zum direkten Ausführen aus SQL*Plus können wir den Aufruf der Prozedur natürlich auch aus einem anderen PL/SQL-Block vornehmen
SQL> begin 2 F_VERK(1,1); 3 end; 4 / PL/SQL procedure successfully completed. SQL> select * from farbe; F_ID F_NAME F_AM_LAGER --------- ------------------------------ -------------- 1 RED 1 2 BLUE 1 3 WHITE 0
Nun, da wir zwei Eimer roter Farbe verkauft haben, probieren wir
SQL> execute f_verk(1,2);
Ergebnis:
SQL> execute f_verk(1,2); begin f_verk(1,2); end; * ERROR at line 1: ORA-20001: Nicht genug am Lager ORA-06512: at "BULLI.F_VERK", line 28 ORA-06512: at line 1 SQL> select * from farbe; F_ID F_NAME F_AM_LAGER --------- ------------------------------ -------------- 1 ROT 1 2 BLAU 1 3 WEISS 0 SQL>
Wir sehen, dass unsere benutzerdefinierte Ausnahme ausgelöst wurde und die Farb-Tabelle nicht verändert wurde.
Übergabe-Argumente
Wie in vorangegangenem Beispiel zu sehen, gibt es offensichtlich verschiedene Varianten der Argumentübergabe.
In unserer Beispielprozedur wurden alle Argumente als IN-Argumente bezeichnet. Dies entspricht dem Default, hätte deshalb auch weggelassen werden können. Für Argumente gilt generell, dass sie in ihrer Größe nicht beschränkt werden dürfen. Also nicht VARCHAR2(20), sondern lediglich VARCHAR2.
Für IN-Argumente gilt, dass sie innerhalb der Prozedur nicht als Zuweisungsziel verwendet werden dürfen. Sie können also nicht verändert werden.
Die Alternative ist ein OUT-Argument: Der übergebene Wert wird dabei nicht berücksichtigt, sondern lediglich durch Zuweisung zurückgeliefert.
Wollen Sie ein Argument sowohl übergeben wie auch verändert zurückliefern, nutzt man IN OUT bei der Argument Spezifikation. Dies gilt auch für Stored Functions.
Beispiel:
create procedure increment (x in out number ) is begin x := x + 1; end; /