martinbulinski.de

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.

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, 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;
/