Weitere Programmiertechniken mit PL/SQL

Fehlerbehandlung unter PL/SQL
Der Exception-Teil
Vordefinierte Exceptions
Deklarieren einer Exception
Erfolg oder Fehlschlag: Auswerten von sqlcode und sqlerrm
Rückgabe von Fehlern mit Hilfe der Prozedur raise_application_error
Abrufen von Daten mit Hilfe eines Cursor
Deklarieren eines Cursor
Mit Cursor-gesteuerten for-Schleifen arbeiten
%found oder %notfound
Ermitteln der Zeilenanzahl mit Hilfe von %rowcount
Erzwingen von Firmenregeln mit Hilfe von Datenbank-Triggern
Erstellen eines Trigger
Spaltenwerte mit Hilfe eines Trigger auf Gültigkeit prüfen
Sicherheit mit Hilfe eines Trigger erzwingen
Setzen von Spaltenwerten mit Hilfe eines Trigger
commit- und rollback-Anweisungen in Triggern nicht erlaubt
Aufruf von gespeicherten Prozeduren in einem Trigger
Löschen, Aktivieren und Deaktivieren von Triggern

Fehlerbehandlung unter PL/SQL

Das Oracle-Handbuch Error Messages and Codes führt alle Fehlercodes und -meldungen außer denen für betriebssystemspezifische Fehler auf. Zu irgendeinem Zeitpunkt wird Ihre Anwendung wahrscheinlich auf einige dieser Fehler treffen. Unter PL/SQL werden Oracle-Fehler als Exceptions bezeichnet. Einige dieser Exceptions besitzen vordefinierte Namen, auf die in PL/SQL-Unterprogrammen Bezug genommen werden kann. Zusätzlich zu diesen vordefinierten Oracle-Exceptions können Sie anwendungsspezifische Exceptions in einem PL/SQL-Unterprogramm definieren.

Eine Methode zur Fehlerbehandlung in einem PL/SQL-Unterprogramm ist die Validierung auf einen Oracle-Fehlercode nach jeder SQL-Anweisung. Es ist bei diesem Ansatz jedoch problematisch, das entsprechende Unterprogramm nachzuvollziehen. Eine Alternative ist die Möglichkeit, daß Sie unter PL/SQL genau angeben, welche Verarbeitung bei einer bestimmten Exception stattfinden soll. Dieser Abschnitt eines PL/SQL-Unterprogramms wird der Exception-Teil eines PL/SQL-Unterprogramms genannt. Eine vordefinierte Exception wird sozusagen »ausgelöst«, wenn während der Ausführung eines PL/SQL-Unterprogramms ein Oracle-Fehler auftritt. Eine benutzerdefinierte Exception lösen Sie aus, in dem Sie in Ihrem PL/SQL-Code die RAISE-Anweisung an angebrachter Stelle aufrufen.

Nehmen wir z.B. an, Sie haben PL/SQL-Unterprogramm geschrieben, das Informationen aus einer unstrukturierten Datei in die Tabelle einer Datenbank lädt. Wenn diese eine ungültige Zahl enthält - 3.1A anstatt 3.14 - wird eine vordefinierte Exception ausgelöst, wenn das PL/SQL-Unterprogramm versucht, eine Zeile mit der ungültigen Zahl einzufügen.

Der Exception-Teil

EXCEPTION
WHEN exception-name1 THEN
PL/SQL-statements;
...
WHEN exception-nameN THEN
PL/SQL-statements;
...
{WHEN OTHERS THEN
PL/SQL-statements;}
END;
Zur Veranschaulichung enthält das Listing einen PL/SQL-Block mit einem Exception-Teil. Beachten Sie, daß der Exception-Teil zwei Exception-Handler enthält: einen für eine vordefinierte Exception - die Exception TOO_MANY_ROWS(in Zeile 11) - und einen für alle anderen Exceptions - gekennzeichnet durch das Wort OTHERS (in Zeile 13).
SQL> declare
2
2 Course_Rec Course%ROWTYPE;
3
3 begin
4
4 dbms_output.enable;
5
5 select *
6 into Course_Rec
7 from Course
8 where
9 Department_ID = 'BIO';
10
10 exception
11 when TOO_MANY_ROWS then
12 dbms_output.put_line('TOO_MANY_ROWS raised - use a cursor');
13 when OTHERS then
14 NULL;
15 end;
16 /
TOO_MANY_ROWS raised - use a cursor
Wenn Sie den Exception-Handler für OTHERS entfernen und eine Exception auslösen, für die kein Exception-Handler vorhanden ist, gibt PL/SQL eine Fehlermeldung zurück. Das folgende Listing zeigt ein Beispiel - eine Zeichenfolge mit 18 Zeichen wird einer Variablen zugewiesen, die bis zu fünf Zeichen speichern kann, was zu einem Oracle-Fehler führt.
SQL> declare
2
2 xyz varchar2(5);
3
3 begin
4
4 dbms_output.enable;
5
5 xyz := 'This will not fit!';
6
6 exception
7
7 when TOO_MANY_ROWS then
8 dbms_output.put_line('TOO_MANY_ROWS Exception Raised');
9 dbms_output.put_line('Occurred in anonymous block');
10
10 end;
11 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5
Vordefinierte Exceptions

Alle Exceptions können entweder als vordefinierte oder benutzerdefinierte Exception eingestuft werden. Vordefinierte Exceptions werden automatisch ausgelöst; z.B. resultiert der Bezug einer SQL-Anweisung auf eine nicht vorhandene Tabelle in einem Oracle-Fehler. Als Beispiel nehmen Sie an, Ihr PL/SQL-Unterprogramm enthalte eine SELECT-Anweisung, die unter bestimmten Umständen keine Zeilen zurückgibt, wodurch die Exception NO_DATA_FOUNDausgelöst wird. Vordefinierte Exceptions besitzen bezeichnende Namen. Es folgen einige vordefinierte Exceptions, die Ihnen bei der Entwicklung einer Oracle-Anwendung begegnen können:

Im folgenden Abschnitt werden die Bedingungen betrachtet, die jede dieser vordefinierte Exceptions auslösen.

Die DUP_VAL_ON_INDEX-Exception

Die DUP_VAL_ON_INDEX-Exception wird ausgelöst, wenn eine SQL-Anweisung versucht, einen gleichen Wert in einer Spalte zu erzeugen, für die ein eindeutiger Index besteht. Zur Veranschaulichung enthält das Listing einen anonymen SQL-Block, der versucht, die Course-Tabelle in der Form zu aktualisieren, daß alle Spalten den gleichen Wert für Course_ID haben und somit die Exception DUP_VAL_ON_INDEX auslöst.

SQL> declare
2
2 begin
3
3 dbms_output.enable;
4
4 update Course
5 set
6 Department_ID = 'BIO',
7 Course_ID = '101';
8
8 exception
9 when DUP_VAL_ON_INDEX then
10 dbms_output.put_line('DUP_VAL_ON_INDEX exception raised');
11 end;
12 /
DUP_VAL_ON_INDEX exception raised
PL/SQL procedure successfully completed.
Die INVALID_NUMBER-Exception

Die INVALID_NUMBER-Exception wird ausgelöst, wenn eine SQL-Anweisung eine ungültige Zahl angibt. So zeigt Listing 11.4 z.B. einen anonymen SQL-Block, der versucht, die Spalte Additional-Fees der Course-Tabelle zu aktualisieren. Die Exception wird ausgelöst, weil die Funktion To_Number versucht, die Zeichenfolge-variable Bogus_Value in eine Zahl umzuwandeln.

SQL> declare
2
2 Bogus_Value varchar2(30) := 'NOT A NUMBER';
3
3 begin
4
4 dbms_output.enable;
5
5 update Course
6 set
7 Additional_Fees = to_number(Bogus_Value);
8
8 exception
9 when INVALID_NUMBER then
10 dbms_output.put_line('INVALID_NUMBER exception raised');
11 end;
12 /
INVALD_NUMBER exception raised
PL/SQL procedure successfully completed.
Die no_data_found-Exception

Die no_data_found-Exception wird ausgelöst, wenn eine SELECT-Anweisung keine Zeilen zurückgibt:

SQL> declare
2
2 Course_Rec Course%ROWTYPE;
3
3 begin
4
4 dbms_output.enable;
5
5 select *
6 into Course_Rec
7 from Course
8 where
9 Course_ID = '777';
10
10 end;
11 /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5
Nachdem Sie für NO_DATA_FOUND (in Zeile 11) einen Exception-Handler hinzugefügt haben, gibt PL/SQL nicht länger den Fehler - no data returned - an die aufrufende Umgebung zurück:
SQL> declare
2
2 Course_Rec Course%ROWTYPE;
3
3 begin
4
4 dbms_output.enable;
5
5 select *
6 into Course_Rec
7 from Course
8 where
9 Course_ID = '777';
10
10 exception
11 when NO_DATA_FOUND then
12 dbms_output.put_line('No data returned');
13 when OTHERS then
14 NULL;
15 end;
16 /
No data returned

PL/SQL procedure successfully completed.

Die too_many_rows-Exception

In der PL/SQL-Umgebung kann eine SELECT-Anweisung nicht mehr als eine Zeile abrufen, ohne die too_many_rows-Exception auszulösen. Um von einer Abfrage eine beliebige Anzahl Zeilen abzurufen, können Sie einen Cursor einsetzen, den Sie sich wie ein Fenster auf das Abfrageergebnis vorstellen können. Das Listing zeigt an einem Beispiel, wie ein Exception-Handler für die too_many_rows-Exception benutzt wird.

SQL> declare
2
2 Course_Rec Course%ROWTYPE;
3
3 begin
4
4 dbms_output.enable;
5
5 select *
6 into Course_Rec
7 from Course
8 where
9 Department_ID = 'BIO';
10
10 exception
11 when TOO_MANY_ROWS then
12 dbms_output.put_line('TOO_MANY_ROWS raised - use a cursor');
13 when OTHERS then
14 NULL;
15 end;
16 /
TOO_MANY_ROWS raised - use a cursor

PL/SQL procedure successfully completed.

Die value_error-Exception

Die value_error-Exception wird bei einer Reihe von Situationen ausgelöst, die in Beziehung zu Fehlern beim Abschneiden und Konvertieren stehen. Im Listing  wird z.B. ein PL/SQL-Block (in der ersten Zeile beginnend) aufgezeigt, der versucht, die Zeichenfolge More than 5 characters einer Variablen zuzuweisen, die als VARCHAR2(5) deklariert wurde.

SQL> declare
2
2 xyz varchar2(5);
3
3 begin
4
4 dbms_output.enable;
5
5 xyz := 'More than 5 characters';
6
6 exception
7
7 when VALUE_ERROR then
8 dbms_output.put_line('VALUE_ERROR raised');
9
9 when OTHERS then
10 NULL;
11
11 end;
12 /
VALUE_ERROR raised
Deklarieren einer Exception

Zusätzlich zur Verarbeitung vordefinierter Exceptions können Sie auch anwendungsspezifische Exceptions definieren und in der folgenden Form deklarieren:

exception-name EXCEPTION;
Das Listing zeigt an einem Beispiel, wie eine Exception namens Life_Threatening_Fever deklariert wird, die aufgerufen wird, wenn die Körpertemperatur eines Patienten über 106° Fahrenheit (41°C) steigt.
SQL> declare
2
2 Life_Threatening_Fever exception;
3 Patient_ID Patient.Patient_ID%TYPE;
4
4 begin
5
5 dbms_output.enable;
6
6 for Patient_Rec in
7 (select Patient_ID, Body_Temp_Deg_F from Patient) loop
8
8 if Patient_Rec.Body_Temp_Deg_F > 106.0 then
9
9 Patient_ID := Patient_Rec.Patient_ID;
10 raise Life_Threatening_Fever;
11
11 end if;
12 end loop;
13
13 exception
14
14 when Life_Threatening_Fever then
15 dbms_output.put_line(Patient_ID || ' has a life ' ||
16 'threatening fever!');
17
17 end;
18 /
GG9999 has a life threatening fever!
Erfolg oder Fehlschlag: Auswerten von sqlcode und sqlerrm

sqlcode ist ein vordefiniertes Symbol, das den Oracle-Fehlerstatus der zuvor ausgeführten PL/SQL-Anweisung enthält. Wird eine SQL-Anweisung ohne Fehler ausgeführt ist sqlcode gleich 0.

sqlerrm ist ein PL/SQL-Symbol, das die mit sqlcode verbundene Fehlermeldung enthält. Wird eine SQL-Anweisung erfolgreich ausgeführt, ist sqlcode gleich 0 und sqlerrm enthält die Zeichenfolge Oracle-0000: normal, successful completion, wie sie im Listing gezeigt wird.

SQL> declare
2
2 begin
3
3 dbms_output.enable;
4
4 dbms_output.put_line('SQLCODE: ' || to_char(SQLCODE));
5 dbms_output.put_line('SQLERRM: ' || SQLERRM);
6
6 end;
7 /
SQLCODE: 0
SQLERRM: ORA-0000: normal, successful completion
Tritt tatsächlich ein Fehler auf, enthalten sqlcode und sqlerrm den jeweils zutreffenden Code und die entsprechende Meldung:
SQL> declare
2 Class_Rec Class%ROWTYPE;
3
3 begin
4
4 dbms_output.enable;
5
5 select *
6 into Class_Rec
7 from Class;
8
8 exception
9 when OTHERS then
10 dbms_output.put_line('SQLCODE: ' || to_char(SQLCODE));
11 dbms_output.put_line(SQLERRM);
12 end;
13 /
SQLCODE: -1422
ORA-01422: exact fetch returns more than requested number of rows

PL/SQL procedure successfully completed.

Rückgabe von Fehlern mit Hilfe der Prozedur raise_application_error

Im DBMS-STANDARD-Packet stellt Oracle eine Prozedur raise_application_error zur Verfügung. Diese Prozedur können Sie dazu einsetzen, anwendungsspezifische Fehlermeldungen an Aufrufende wie SQL*Plus, ein SQL-Unterprogramm oder eine Client-Anwendung zurückzugeben. Oracle reserviert Fehlercodes im Bereich -20000 bis -20999 für diese benutzerdefiniertern Fehler. Veranschaulicht wird dies im Listing durch einen PL/SQL-Block, der eine Exception Fever_Out_Of_Range deklariert. Ein Cursor For Loop (in Zeile 6) liest jede Zeile der Patiententabelle. Übersteigt die Temperatur eines Patienten 115° Fahrenheit, wird die Exception Fever_Out_Of_Range (in Zeile 8) ausgelöst. Im Exception-Teil ruft der Exception-Handler für Fever_Out_Of_Range RAISE_APPLICATION_ERROR auf und übergibt ihr den Fehlercode -20000 und eine entsprechende Meldung (in Zeile 14).

SQL> declare
2
2 Fever_Out_of_Range exception;
3 Patient_ID Patient.Patient_ID%TYPE;
4
4 begin
5
5 dbms_output.enable;
6
6 for Patient_Rec in
7 (select Patient_ID, Body_Temp_Deg_F from Patient) loop
8
8 if Patient_Rec.Body_Temp_Deg_F > 115.0 then
9
9 raise Fever_Out_of_Range;
10
10 end if;
11
11 end loop;
12
12 exception
13
13 when Fever_Out_of_Range then
14 raise_application_error (-20000,
'Fever is out of the range 65 Deg. F to 115 Deg. F');

15
15 end;
16 /
declare
*
ERROR at line 1:
ORA-20000: Fever is out of the range 65 Deg. F to 115 Deg. F
ORA-06512: at line 14

Abrufen von Daten mit Hilfe eines Cursor

Wenn Sie SQL*Plus oder SQL Worksheet einsetzen, können Sie eine Abfrage stellen, ohne sich Gedanken über die Anzahl der zurückgegebenen Zeilen machen zu müssen. Es spielt keine Rolle, ob die Abfrage keine, eine oder tausend Zeilen zurückgibt. Jedoch stimmt dies so nicht für PL/SQL-Unterprogramme. Sie können keine gewöhnliche SELECT-Anweisung verwenden, um mehr als eine Zeile abzurufen. Wenn eine SELECT-Anweisung in einem PL/SQL-Unterprogramm - ob als anonymer Block, gespeicherte Prozedur oder Trigger - mehr als eine Zeile abruft, gibt Oracle eine Fehlermeldung zurück. Offensichtlich ist die Fähigkeit, mehr als eine Zeile abzurufen, so wesentlich, daß es dafür einen Mechanismus geben muß. Die von Oracle zur Erledigung dieser Aufgabe zur Verfügung gestellte Ressource ist der Cursor. Cursors werden von Oracle-Dienstprogrammen wie SQL*Plus automatisch erstellt und verwendet.
Das Listing zeigt, wie Oracle einen Fehler zurückgibt, weil die SELECT-Anweisung mehr als einen Instructor zurückgibt.

SQL> declare
2
2 Instructor_ID Instructor.Instructor_ID%type
3 Last_Name Instructor.Last_Name%type
4 First_Name Instructor.First_Name%type
5
5 begin
6
6 select Instructor_ID, Last_Name, First_Name
7 into Instructor_ID, Last_Name, First_Name
8 from Instructor
9 order by Instructor_ID
10
10 end
11 /
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 6
Sie können sich einen Cursor als ein Fenster auf die Ergebnismenge einer Abfrage vorstellen. Im allgemeinen führen Sie beim Einsatz eines Cursor vier Schritte durch:
  1. Deklarieren des Cursor. Dem Cursor wird ein Name zugewiesen, und er wird einer zu parsenden SELECT-Anweisung zugeordnet.
  2. Öffnen des Cursor. Das Oracle-RDBMS führt die dem Cursor zugeordnete Abfrage aus und bestimmt die angegebenen Zeilen (die aktive Menge).
  3. Lesen der Zeilen des Cursor. Die Werte einer jeden Zeile werden der Umgebung des PL/SQL-Unterprogramms übergeben. Die Zeilen werden einzeln übergeben.
  4. Schließen des Cursor. Alle von Oracle in Verbindung mit dem Cursor beanspruchten Ressourcen werden wieder freigegeben.
Es folgt eine gespeicherte Funktion, die feststellt, ob ein bestimmter Kurs in den Unterrichtsplan des Studenten paßt. Die Funktion - Schedule_Conflict - hat zwei Argumente: Student_ID und Clas-ID. Sie stellt Konflikte fest, indem nach anderen Kursen im gegenwärtigen Unterrichtsplan des Studenten gesucht wird, die die gleichen Zeiten wie der vorgeschlagene Kurs haben. Mit einem Cursor wird diese Abfrage durchgeführt. Das Listing enthält den kompletten Text der Funktion.
SQL> create or replace function schedule_conflict [ccc](arg_student_ID IN varchar2,
2 arg_class_ID IN varchar2)
3 return number is
4
4 conflicting_classes number := -1;
5 normal number := 0;
6
6 cursor get_other_classes is
7 select SS.Class_ID
8 from Student_Schedule SS, Class C
9 where
10 SS.Class_ID = C.Class_ID and
11 (C.Semester, C.School_Year, C.Schedule_ID) =
12 (select Semester, School_Year, Schedule_ID
13 from Class
14 where
15 Class_ID = arg_class_ID);
16
16 Conflicting_Class_ID Class.Class_ID%type;
17 status number;
18
18 begin
19
19 - Need to look at the other classes in the student's schedule
20 - for the same semester and school year.
21
21 open get_other_classes;
22
22 loop
23
23 fetch get_other_classes into Conflicting_Class_ID;
24 exit when get_other_classes%notfound;
25
25 end loop;
26
26 if get_other_classes%rowcount > 0 then
27 status := conflicting_classes;
28 else
29 status := normal;
30 end if;
31
31 close get_other_classes;
32
32 return status;
33
33 end;
34 /

Function created.

SQL> select Student_ID, Class_ID
2 from Student_Schedule
3 where
4 Student_ID = '10231311';

STUDENT_ID CLASS_ID
------------------- -----------------
104200
104500

SQL> select schedule_conflict('10231311','104200') from dual;

SCHEDULE_CONFLICT('10231311','104200')
--------------------------------------
-1

Es folgt eine schrittweise Betrachtung der einzelnen Schritte dieses Cursor. Als erstes wird in Zeile 6 der Cursor - Set_Other_Classes - als Verbindung zwischen zwei Tabellen - Student_Schedule und Class - deklariert. Zweitens wird der Cursor im ausführbaren Teil der gespeicherten Funktion (in Zeile 21) geöffnet. Drittens liest eine Schleifenanweisung Zeilen vom Cursor, bis keine Zeilen mehr empfangen werden (in Zeile 23).

Wie Sie am Ende des Listing sehen, ergibt eine Abfrage der Tabelle Student_-Schedule für Student_ID 10231311, daß dieser für zwei Kurse eingeschrieben ist: 104200 und 104500. Die Funktion wird durch die abschließende SELECT-Anweisung aufgerufen; sie überprüft, ob der Kurs 104200 für den Studenten 10231311 zu einer Überschneidung führt. Dies ist natürlich der Fall, denn der Student ist bereits für diesen Kurs eingeschrieben. Die Funktion gibt -1 zurück, was den Zustand sich überschneidender Kurse anzeigt.

Deklarieren eines Cursor

Jeder Cursor muß deklariert werden, ehe er genutzt werden kann. Einen Cursor zu deklarieren, bedeutet ihn zu benennen und die SELECT-Anweisung zu bestimmen, der der Cursor zugeordnet ist.

Die grundlegende Syntax zur Deklaration eines Cursor unter PL/SQL lautet folgendermaßen:

CURSOR cursor-name
{(parameter1 parameter1-datatype {:= default1},
...
parameterN parameterN-datatype {:= defaultN})}
IS select-stmt;
Das Listing zeigt zwei unterschiedliche Cursors - der erste Cursor hat drei Parameter ohne Standardwerte, und der zweite Cursor hat drei Parameter mit bestimmten Standardwerten.
cursor patients_with_hypertension
(patient_age number,
normal_dyastolic,
normal_systolic) is
select patient_id, age, dyastolic, systolic
from patient
where
dyastolic > normal_dyastolic * (age+200)/200 and
systolic > normal_systolic * (age+200)/200;

cursor patients_with_hypertension
(patient_age number default 55,
normal_dyastolic number default 70,
normal_systolic number default 130) is
select patient_id, age, dyastolic, systolic
from patient
where
dyastolic > normal_dyastolic * (age+200)/200 and
systolic > normal_systolic * (age+200)/200;

Wenn Sie einen der Precompiler von Oracle verwenden, - z.B. Pro*C - müssen Sie einen Cursor einsetzen, um über eine SELECT-Anweisung mehr als eine Zeile abzurufen.

Öffnen eines Cursor

Bevor Sie die Zeilen eines Cursor lesen können, müssen Sie den Cursor öffnen. Ist dieser geöffnet, wird dessen SELECT-Anweisung ausgeführt, und Oracle erstellt eine Liste der betreffenden Zeilen. Diese Zeilen werden als aktive Menge bezeichnet. Wurde der Cursor ohne Parameter deklariert, ist die Syntax recht einfach.

open my_cursor;
Wurde der Cursor mit Parameter deklariert, müssen Sie, wenn Sie den Cursor öffnen, für jeden Parameter eine PL/SQL-Variable oder einen Literalwert bereitstellen.
SQL> declare
2
2 cursor patients_with_hypertension
3 (patient_age number,
4 normal_dyastolic number) is
5 select patient_id
6 from patient
7 where
8 dyastolic > normal_dyastolic * (age+200)/200 and
9 systolic > 180;
10
10 Patient_ID Patient.Patient_ID%type;
11
11 begin
12
12 open patients_with_hypertension (45, 80);
13
13 end;
14 /
PL/SQL procedure successfully completed
Wurde der Cursor mit Parametern deklariert - und für diese Parameter Standardwerte angegeben -, ist es nicht notwendig, daß Sie für jeden Parameter eine PL/SQL-Variable oder einen Literalwert bereitstellen.
SQL> declare
2
2 cursor patients_with_hypertension
3 (patient_age number default 55,
4 normal_dyastolic number default 70,
5 normal_systolic number default 130) is
6 select patient_id
7 from patient
8 where
9 dyastolic > normal_dyastolic * (age+200)/200 and
10 systolic > normal_systolic * (age+200)/200;
11
11 Patient_ID Patient.Patient_ID%type;
12
12 begin
13
13 dbms_output.enable;
14
14 open patients_with_hypertension;
15
15 loop
16
16 fetch patients_with_hypertension
17 into Patient_ID;
18 exit when patients_with_hypertension%notfound;
19
19 dbms_output.put_line(patient_record.patient_id); 20
20 end loop;
21
21 end;
22 /
N3393
PL/SQL procedure successfully completed.
Wurde der Cursor mit Parametern deklariert - aber ohne angegebene Standardwerte für diese Parameter - müssen Sie für jeden Parameter eine PL/SQL-Variable oder einen Literalwert bereitstellen. Das Listing zeigt, wie Oracle die Anweisung zum Öffnen des Cursor zurückweist, wenn die erforderlichen Argumente nicht bereitgestellt werden.
SQL> declare
2
2 cursor patients_with_hypertension
3 (patient_age number,
4 normal_dyastolic number,
5 normal_systolic number) is
6 select patient_id
7 from patient
8 where
9 dyastolic > normal_dyastolic * (age+200)/200 and
10 systolic > 180;
11
11 Patient_ID Patient.Patient_ID%type;
12
12 begin
13
13 open patients_with_hypertension;
14
14 end;
15 /
declare
*
ERROR at line 1:
ORA-06550: line 13, column 1:
PLS-00306: wrong number or types of arguments
in call to 'PATIENTS_WITH_HYPERTENSION'
ORA-06550: line 13, column 1:
PL/SQL: SQL Statement ignored
Lesen von Zeilen eines Cursor

Ist der Cursor einmal geöffnet worden, würde die Abfrage ausgeführt, und die entsprechenden Zeilen würden identifiziert. Um diese Zeilen zu lesen, muß die FETCH-Anweisung ausgeführt werden, die den Wert jeder in der SELECT-Anweisung des Cursor angegebenen Spalte abruft und in einer PL/SQL-Variablen speichert. Üblicherweise werden Sie Zeilen innerhalb einer Schleife lesen wollen. Zur Veranschaulichung enthält das Listing einen anonymen SQL-Block (in der ersten Zeile beginnend), in dem Zeilen aus der Instructor-Tabelle gelesen werden.

SQL> declare
2
2 Instructor_ID Instructor.Instructor_ID%type;
3 Last_Name Instructor.Last_Name%type;
4 First_Name Instructor.First_Name%type;
5
5 cursor get_instructors is
6 select Instructor_ID, Last_Name, First_Name
7 from Instructor
8 order by Instructor_ID;
9
9 begin
10
10 dbms_output.enable;
11
11 open get_instructors;
12
12 loop
13
13 fetch get_instructors into
14 Instructor_ID, Last_Name, First_Name; 15 exit when get_instructors%notfound;
16
16 dbms_output.put_line(Instructor_ID);
17
17 end loop;
18
18 end;
19 /
A612
B331
B391
D201
D944
E301
E405
E491
G331
J505
L391
M101
P331
R983
S131
T149
W490
Y561
PL/SQL procedure successfully completed.
Wenn alle Zeilen gelesen worden sind, sollten Sie die EXIT-Anweisung verwenden, um die Schleife zu beenden.

Die Syntax lautet folgendermaßen:

EXIT {label}{WHEN condition}
Vier bestimmte Attribute sind dem deklarierten Cursor zugeordnet: ROWCOUNT, %FOUND, %NOTFOUND und %ISOPEN. Auf diese Attribute wird verwiesen, indem sie hinter den Namen des Cursor gesetzt werden. Um eine Schleife mit der EXIT-Anweisung zu beenden, verweisen Sie auf das %NOTFOUND-Attribut eines Cursor wie folgt:
exit when get_instructors%notfound;
Schließen eines Cursor

Aus zwei Gründen müssen Sie einen Cursor schließen:

Wenn ein PL/SQL-Programm einen Cursor nicht schließt, schließt Oracle diesen entweder durch Beenden oder ein DISCONNECT, wenn das Unterprogramm sich von der Oracle-Datenbank abmeldet. Einen Cursor zu schließen ist einfach:
close get_instructors;
Das Listing zeigt, wie einem Cursor neue Parameter zugewiesen werden, indem der Cursor geschlossen, die Parameterwerte geändert und der Cursor wieder geöffnet wird. Der Cursor patients_with_hypertension wird zur Veranschaulichung dieses Vorgangs verwendet. Als erstes wird der Cursor mit age set to 50 und normal_dyastolic set to 80 (Zeile 14) geöffnet. Die Zeilen werden in einer Schleife (Zeile 16) gelesen und der Cursor geschlossen. Dann wird der Cursor mit age equal to 40 und normal_dyastolic set to 70 (Zeile 22) erneut geöffnet. Die Zeilen werden mit anderen Ergebnissen in einer Schleife gelesen.
SQL> declare
2
2 Patient_ID Patient.Patient_ID%type;
3 Age Patient.Age%type;
4 Dyastolic Patient.Dyastolic%type;
5
5 cursor patients_with_hypertension
6 (patient_age number,
7 normal_dyastolic number) is
8 select patient_id, age, dyastolic
9 from patient
10 where
11 dyastolic > normal_dyastolic * (age+200)/200;
12
12 begin
13
13 dbms_output.enable;
14
14 open patients_with_hypertension (50, 80);
15
15 loop
16
16 fetch patients_with_hypertension
17 into Patient_ID, Age, Dyastolic;
18 exit when patients_with_hypertension%notfound;
19
19 dbms_output.put_line('With age=50, dyas=80: ' || Patient_ID); 20
20 end loop;
21
21 close patients_with_hypertension;
22
22 open patients_with_hypertension (40, 70);
23
23 loop
24
24 fetch patients_with_hypertension
25 into Patient_ID, Age, Dyastolic;
26 exit when patients_with_hypertension%notfound;
27
27 dbms_output.put_line('With age=40, dyas=70: ' || Patient_ID);
28
28 end loop;
29
29 close patients_with_hypertension;
30
30 end;
31 /
With age=50, dyas=80: N3393
With age=40, dyas=70: A2002
With age=40, dyas=70: N3393
With age=40, dyas=70: E3893
PL/SQL procedure successfully completed
Mit Cursor-gesteuerten for-Schleifen arbeiten

Als Alternative zum Öffnen, Lesen und Schließen eines Cursor bietet Oracle einen anderen Ansatz - die Cursor-gesteuerte FOR-Schleife. Mit dieser Cursor-gesteuerten FOR-Schleife deklariert Oracle implizit eine Variable - den Schleifenindex - die vom gleichen Datentyp ist wie der Datensatz des Cursor.

SQL> declare
2
2 Instructor_ID Instructor.Instructor_ID%type;
3 Last_Name Instructor.Last_Name%type;
4 First_Name Instructor.First_Name%type;
5
5 cursor Get_Associate_Profs is
6 select Instructor_ID, Last_Name, First_Name
7 from Instructor
8 where Position = 'ASSOCIATE PROFESSOR'
9 order by Instructor_ID;
10
10 begin
11
11 dbms_output.enable;
12
12 for Get_Associate_Profs_Rec in Get_Associate_Profs loop
13
13 dbms_output.put_line('Last name: ' ||
ÂGet_Associate_Profs_Rec.Last_Name);
14
14 end loop;
15
15 end;
16 /
Last name: NILAND
Last name: DANIELS
Last name: RESTON
Last name: JASON
Last name: ANGELO
Last name: CHERNOW
Last name: YOUNG

PL/SQL procedure successfully completed.

Der FOR nachgestellte Name ist der implizit deklarierte Schleifenindex. Im Listing sehen Sie jedoch, daß auf den Schleifenindex - Get_Associate_Profs_Rec - außerhalb der Schleifenanweisung nicht Bezug genommen werden kann.
SQL> declare
2
2 Instructor_ID Instructor.Instructor_ID%type;
3 Last_Name Instructor.Last_Name%type;
4 First_Name Instructor.First_Name%type;
5
5 cursor Get_Associate_Profs is
6 select Instructor_ID, Last_Name, First_Name
7 from Instructor
8 where Position = 'ASSOCIATE PROFESSOR'
9 order by Instructor_ID;
10
10 begin
11
11 dbms_output.enable;
12
12 for Get_Associate_Profs_Rec in Get_Associate_Profs loop
13
13 dbms_output.put_line('Last name: ' ||
ÂGet_Associate_Profs_Rec.Last_Name);
14
14 end loop;
15
15 Last_Name := Get_Associate_Profs_Rec.Last_Name;
16
16 end;
17 /
declare
*
ERROR at line 1:
ORA-06550: line 15, column 14:
PLS-00201: identifier 'GET_ASSOCIATE_PROFS_REC.LAST_NAME'
[ccc]must be declared
ORA-06550: line 15, column 1:
PL/SQL: Statement ignored.
%found oder %notfound

In den vorangegangenen Beispielen wurde das Attribut %NOTFOUND dazu verwendet, festzustellen, ob eine FETCH-Anweisung eine Zeile einlas oder nicht. Nachdem alle Zeilen der aktiven Gruppe eingelesen wurden und die letzte FETCH-Anweisung keine mehr einlesen kann, ergibt die Auswertung von %NOTFOUND TRUE.
Vor dem Aufruf der FETCH-Anweisung gibt %NOTFOUND NULL zurück. Enthält Ihr SQL-Programm eine Schleife, in der die FETCH-Anweisung nicht aufgerufen wird, sollten Sie überlegen, ob Sie nicht eine Validierung von %NOTFOUND auf den Wert NULL vornehmen.

Ermitteln der Zeilenanzahl mit Hilfe von %rowcount

Sie benötigen keinen Zähler, um die Anzahl der aus einem Cursor eingelesenen Zeilen zu verfolgen. Statt dessen verweisen Sie auf das Attribut %ROWCOUNT des Cursor. Wie Sie in Zeile 13 des Listing sehen können, gibt %ROWCOUNT die laufende Anzahl der eingelesenen Zeilen zurück.

SQL> declare
2
2 Instructor_ID Instructor.Instructor_ID%type;
3 Last_Name Instructor.Last_Name%type;
4 First_Name Instructor.First_Name%type;
5
5 cursor Get_Associate_Profs is
6 select Instructor_ID, Last_Name, First_Name
7 from Instructor
8 where Position = 'ASSOCIATE PROFESSOR'
9 order by Instructor_ID;
10
10 begin
11
11 dbms_output.enable;
12
12 for Get_Associate_Profs_Rec in Get_Associate_Profs loop
13
13 dbms_output.put_line ('Rowcount: ' || [ccc]Get_Associate_Profs%rowcount);
14
14 end loop;
15
15 end;
16 /
Rowcount: 1
Rowcount: 2
Rowcount: 3
Rowcount: 4
Rowcount: 5
Rowcount: 6
Rowcount: 7

PL/SQL procedure successfully completed.

Anstatt eine Schleife zu verlassen, wenn der Cursor keine Zeilen mehr enthält, können Sie, wie im Listing gezeigt, für das Erreichen einer bestimmten Anzahl in %ROWCOUNT eine Abbruchbedingung festlegen (siehe Zeile 13).
SQL> declare
2
2 Instructor_ID Instructor.Instructor_ID%type;
3 Last_Name Instructor.Last_Name%type;
4 First_Name Instructor.First_Name%type;
5
5 cursor Get_Associate_Profs is
6 select Instructor_ID, Last_Name, First_Name
7 from Instructor
8 where Position = 'ASSOCIATE PROFESSOR'
9 order by Instructor_ID;
10
10 begin
11
11 dbms_output.enable;
12
12 for Get_Associate_Profs_Rec in Get_Associate_Profs loop
13
13 exit when Get_Associate_Profs%rowcount >= 5;
14 dbms_output.put_line ('Rowcount: ' || [ccc]Get_Associate_Profs%rowcount);
15
15 end loop;
16
16 end;
17 /
Rowcount: 1
Rowcount: 2
Rowcount: 3
Rowcount: 4
PL/SQL procedure successfully completed.
In den beiden letzten Listings sehen Sie, wie Sie %ROWCOUNT einsetzen können, um die Anzahl der vom Cursor eingelesenen Zeilen zu verfolgen.

Erzwingen von Firmenregeln mit Hilfe von Datenbank-Triggern

Mit dem Einsatz des bisher über SQL und PL/SQL Gelernten ist es an der Zeit, die Welt der Datenbank-Trigger zu erkunden. In einem Informationssystem, das eine nicht-relationale Datenbank zur Grundlage hat, werden die Firmenregeln der Organisation durch Anwendungs-Software umgesetzt. So könnte z.B. eine Firmenregel lauten, daß eine Bestellung über eine benötigte Menge ins System eingegeben wird, sobald eine manuelle Inventur für einen Artikel einen Bestand ergibt, der unter der Lagermenge dieses Artikels liegt. Diese Regel würden Sie üblicherweise durchsetzen, indem Sie unter Cobol oder einer anderen Programmiersprache eine Routine schreiben würden, die an entsprechender Stelle im Programm aufgerufen würde. Diese Methode birgt jedoch einige Probleme:

Oracle, wie auch andere moderne RDBMS, stellen Mechanismen - Datenbank-Trigger - zur Verfügung, die die Aufgabe erleichtern, Firmenregeln einer Organisation durchzusetzen. Ein Datenbank-Trigger besteht aus einer Gruppe von PL/SQL-Anweisungen, die dann ausgeführt werden, wenn eine SQL-Anweisung - eine DELETE-, UPDATE- oder INSERT-Anweisung - auf eine Tabelle angewendet wird. Einen Datenbank-Trigger können Sie zur Erledigung folgender Aufgaben einsetzen:
Erstellen eines Trigger

Die Oracle-Anweisung CREATE TRIGGER erstellt (oder ersetzt) einen Trigger, der ausgelöst wird, wenn in einer Tabelle ein bestimmtes Ereignis auftritt. Die Syntax für die CREATE-TRIGGER-Anweisung lautet wie folgt:

CREATE [OR REPLACE] TRIGGER trigger-name {BEFORE | AFTER}
triggering-event ON table-name
[FOR EACH ROW]
[WHEN (condition)]
PL/SQL-block
Trigger auf Anweisungsebene und auf Zeilenebene

Ein Datenbank-Trigger gehört in eine der beiden folgenden Kategorien:

Ein Trigger auf Anweisungsebene wird durch das auslösende Ereignis nur einmal ausgelöst und hat keinen Zugang zu den Spaltenwerten der einzelnen vom Trigger betroffenen Zeilen. Ein Trigger auf Zeilenebene wird für jede vom Trigger betroffene Zeile ausgelöst und kann auf die Ausgangswerte und neuen Werte der durch eine SQL-Anweisung bearbeiteten Spalte zugreifen.
Im allgemeinen verwenden Sie Trigger auf Anweisungsebene zur Bearbeitung von Informationen über die SQL-Anweisung, die den Trigger ausgelöst hat - z.B. wer ihn ausgelöst hat und wann er ausgelöst wurde. Typischerweise verwenden Sie einen Trigger auf Zeilenebene, wenn Sie die Spaltenwerte einer Zeile kennen müssen, um eine Firmenregel umzusetzen.

Zugriff auf Spaltenwerte im Trigger-Rumpf

Innerhalb eines Trigger-Rumpfs kann ein Trigger auf Zeilenebene auf den Spaltenwert einer Zeile verweisen, der zum Zeitpunkt der Auslösung des Trigger bestand. Diese Werte hängen davon ab, welche SQL-Anweisung den Trigger ausgelöst hat.

Auslösen von Ereignissen

Wenn Sie einen Trigger erstellen, legen Sie fest, welches Ereignis ihn auslösen wird. Die drei möglichen Ereignisse sind:

Außerdem lassen sich diese drei Trigger-auslösenden Ereignisse derart kombinieren, daß ein Trigger immer dann ausgelöst wird, wenn eine INSERT-, UPDATE- oder DELETE-Anweisung ausgeführt wird, wie es im Listing gezeigt wird.
SQL> create or replace trigger Block_Trade_After_All After
2 insert or update or delete on Tab1
3 for each row
4
4 declare
5
5 begin
6
6 insert into Tab11
7 (col11)
8 values
9 (11);
10
10 end;
11 /
Trigger created.
before- und after-Trigger

Ein BEFORE-Trigger auf Zeilenebene wird vor Ausführung des Trigger-auslösenden Ereignisses ausgelöst. Somit können Sie einen BEFORE-Trigger auf Zeilenebene zur Änderung von Spaltenwerten einer Zeile einsetzen. Ein AFTER-Trigger auf Zeilenebene wird nach Auftreten des Trigger-auslösenden Ereignisses ausgelöst. Mit einem AFTER-Trigger auf Zeilenebene können Sie keine Spaltenwerte ändern.

Mögliche Trigger für eine Tabelle

Auf der Grundlage aller in einer CREATE-TRIGGER-Anweisung möglichen Permutationen, kann eine Tabelle bis zu 12 verschiedene Trigger aufweisen:

Wenn Sie den Einsatz eines Entity-Relationship-Modeling-Tools für den Datenbankentwurf planen - was ratsam ist - werden Sie feststellen, daß die meisten von ihnen automatisch Datenbank-Trigger auf Basis der von Ihnen definierten Primär- und Fremdschlüssel generieren. Einige Tools, wie ERwin von LogicWorks, erstellen Trigger entweder direkt durch eine Oracle-Verbindung oder speichern Trigger in einer Skriptdatei. Wenn Sie die letzte Methode wählen, können Sie das Erstellungsskript für den Trigger verändern, indem Sie jedem generierten Trigger anwendungsspezifische Firmenregeln hinzufügen.
Nur weil Sie alle 12 Trigger-Arten für eine Tabelle erstellen können, heißt dies nicht, daß Sie sie auch alle verwenden müssen. Tatsächlich sollten Sie bei der Erstellung von Triggern für Ihre Tabellen ein gutes Urteilsvermögen beweisen.
Ab Version 7.1 unterstützt Oracle mehrere gleichartige Trigger für ein und dieselbe Tabelle. Installationen von Oracle 7.0 hingegen, die Snapshots verwendeten, konnten keine AFTER-ROW-Trigger für eine Mastertabelle erstellen, weil die Snapshot-Logs AFTER-ROW-Trigger für die gleiche Tabelle verwendeten. Wenn Sie nicht gerade vorhaben, einen AFTER-ROW-Trigger für eine Tabelle, auf die von Snapshot Bezug genommen wird, zu verwenden, sollten Sie es vermeiden, mehrere gleichartige Trigger für eine bestimmte Tabelle zu definieren - zu leicht entstehen Fehler beim Entwurf, und es leidet die Übersichtlichkeit.

Spaltenwerte mit Hilfe eines Trigger auf Gültigkeit prüfen

Als DBA (Datenbankadministrator) einer Kreditkartengesellschaft sind Sie für die Umsetzung der Kreditrichtlinien durch Datenbank-Trigger verantwortlich. Untersuchungen des Unternehmens haben ergeben, daß die Wahrscheinlichkeit eines Kreditkartenbetrugs mehr als 80 Prozent beträgt, wenn innerhalb von drei Tagen auf einem einzelnen Konto Belastungen von mehr als $ 1000 zusammenkommen. Der Betriebsleiter möchte, daß alle Konten, auf die diese Kriterien zutreffen, in einer gesonderten Tabelle aufgeführt werden, in der sie dann genau untersucht werden können.
Zur Erledigung dieser Aufgabe erstellen Sie den Trigger für die Tabelle Credit_Card_Log, der vor dem Einfügen einer Zeile ausgelöst wird. Der Trigger schaut, ob der Gesamtbetrag der Belastungen der letzten drei Tage für eine bestimmte Kartennummer $ 1000 übersteigt und führt in diesem Fall ein INSERT für die Tabelle Credit_Charge_Attempt_Log aus, in der dieser Datensatz dann von Kreditsachbearbeitern untersucht werden kann.

SQL> create or replace trigger Credit_Charge_Log_Ins_Before before 2 insert on Credit_Charge_Log
3 for each row
4
4 declare
5
5 total_for_past_3_days number;
6
6 begin
7
7 - Check the credit charges for the past 3 days.
8 - If they total more than $1000.00, log this entry
9 - in the Credit_Charge_Attempt_Log for further handling.
10
10 select sum(amount)
11 into total_for_past_3_days
12 from Credit_Charge_Log
13 where
14 Card_Number = :new.Card_Number and
15 Transaction_Date >= sysdate-3;
16
16 if total_for_past_3_days > 1000.00 then
17
17 insert into Credit_Charge_Attempt_Log
18 (Card_Number, Amount, Vendor_ID, Transaction_Date)
19 values
20 (:new.Card_Number, :new.Amount,
:new.Vendor_ID, :new.Transaction_Date);
21
21 end if;
22
22 end;
23 /
Trigger created.


Um den Trigger so einzustellen, daß er ausgelöst wird, initialisieren Sie die Tabelle Credit_Card_Log mit einigen Zeilen, wie es im Listing gezeigt wird.

SQL> select * from credit_charge_log;
CARD_NUMBER AMOUNT VENDOR_I TRANSACTI
------------------ ------ -------- ---------
8343124443239383 128.33 12345678 19-JUN-95
9453128834232243 83.12 98765432 18-JUN-95
4644732212887321 431.1 18181818 19-JUN-95
0944583312453477 211.94 09090909 18-JUN-95
0944583312453477 413.81 08080808 18-JUN-95
0944583312453477 455.31 91919191 19-JUN-95
0944583312453477 225 12341234 20-JUN-95
0944583312453477 512.22 12341234 20-JUN-95
rows selected.
Vor dem Einfügen einer Zeile in die Tabelle für die Kartennummer 0944583312453477 wird der Trigger ausgelöst. Die Tabelle wird abgefragt, ob die Belastungen für die Kartennummer innerhalb der letzten drei Tage $ 1000 übersteigt. Ist dies der Fall, wird der Tabelle Credit_Charge_Attempt_Log eine Zeile hinzugefügt, wie Sie es im Listing (Zeile 1) sehen können. Weil die Kartennummer 0944583312453477 innerhalb der letzten drei Tage mit mehr als $ 1000 belastet wurde, fügt der Trigger eine Zeile in die Tabelle Credit_Charge _Attempt_Log ein.
SQL> insert into Credit_Charge_Log
2 (Card_Number, Amount, Vendor_ID, Transaction_Date)
3 values
4 ('0944583312453477', 128.28, '43214321', '20-JUN-95');
row created.
SQL> select * from Credit_Charge_Attempt_Log;
CARD_NUMBER AMOUNT VENDOR_I TRANSACTI
------------------ ------ -------- ---------
0944583312453477 128.28 43214321 20-JUN-95
Sicherheit mit Hilfe eines Trigger erzwingen

Nachfolgend sehen Sie ein Beispiel, wie Sie mit Hilfe eines Trigger Sicherheitsrichtlinien erzwingen können. Die Datenbank der Acme Corporation ist so angelegt, daß bei einer erfolgten Auslieferung der Tabelle Shipment eine Zeile hinzugefügt werden muß. Diese Tabelle Shipment enthält eine Spalte Manual_Check, in der angegeben ist, ob der Angestellte im Auslieferungslager telefonisch die Richtigkeit der Lieferungsanforderung bestätigen muß. Zur Reduzierung von Unterschlagungen lautet die Richtlinie der Firma, daß der Angestellte im Auslieferungslager jede Lieferungsanforderung bestätigen muß, die nach Ende der regulären Arbeitszeit eingeht - 17.00 Uhr.
Als DBA sind Sie für die Umsetzung dieser Richtlinie verantwortlich. Wie im Listing zu sehen ist, können Sie einen Trigger Shipment_Ins_Before erstellen, der vor der Ausführung einer INSERT-Anweisung auf die Tabelle Shipment ausgelöst wird. Der Trigger-Rumpf besteht aus einer einzigen PL/SQL-Anweisung - der Zuweisung von Y der Spalte Manual_Check. Zusätzlich entscheiden Sie sich für die Verwendung einer WHEN-Klausel, damit der Trigger nur nach 17.00 Uhr ausgelöst wird.

SQL> create or replace trigger Shipment_Ins_Before before
2 insert on Shipment
3 for each row
4 when (to_number(to_char(sysdate,'HH24')) > 17)
5
5 declare
6
6 begin
7
7 :new.Manual_Check := 'Y';
8
8 end;
9 /
Trigger created.
Nachdem der Trigger erstellt wurde, können Sie ihn testen. Wie aus dem folgenden Listing ersichtlich, ist die aktuelle Zeit nach 17.00 Uhr - es ist 19.00 Uhr. Wird der Tabelle Shipment eine Zeile hinzugefügt, wird die Spalte Manual_Check wie beabsichtigt auf Y gesetzt.
SQL> select to_char(sysdate,'HH24') from dual;
TO_CHAR(SYSDATE,'HH24')
-------------------------------------------------------------
SQL> insert into Shipment
2 (Shipment_ID, Product_Code, Quantity, Customer_ID)
3 values
4 ('SHIP1001', 'PROD123', 100, 'CUST999');
row created.
SQL> select * from Shipment;
SHIPMENT_ID PRODUCT_CODE QUANTITY CUSTOMER_ID M ENTERED_BY
----------- ------------ -------- ----------- - ----------
SHIP1001 PROD123 100 CUST999 Y
Setzen von Spaltenwerten mit Hilfe eines Trigger

Eine weitere Einsatzmöglichkeit für einen Trigger ist das Setzen eines Spaltenwerts auf einen bestimmten Wert, ehe sich eine SQL-Anweisung auswirkt. Das folgende Szenario demonstriert diesen Vorgang. Nehmen Sie an, eine Tabelle namens Block_Trade_Log wird zur Auflistung des Handels mit Wertpapieren an der NASDAQ (National Association of Securities Dealers Automated Quotations) verwendet. Die Tabelle enthält: das Zeichen der Aktie, den Verkaufspreis, die Anzahl der gehandelten Wertpapiere, wann der Handel erfolgte, ob die Wertpapiere ge- oder verkauft wurden und den aktuellen Durchschnittswert des Wertpapiers für die letzten drei Tage. Beim Einfügen einer Zeile in diese Tabelle wird ein Trigger verwendet, um den Wert für Running_Avg_3_Days zu setzen. Das Listing zeigt, wie dieser Trigger erzeugt wird.

create or replace trigger Block_Trade_Log_BI before
insert on Block_Trade_Log
for each row
declare
Running_Avg number;
begin
select avg(price)
into Running_Avg
from Block_Trade_Log
where
Stock_Symbol = :new.Stock_Symbol and
Timestamp >= SYSDATE-3;
:new.Running_Avg_3_Days := Running_Avg;
end;
/
Beachten Sie, daß der Wert für Running_Avg_3_Days gesetzt wird, indem der Wert :new.Running_Avg_3_Days := Running_Avg; zugewiesen wird. Vergessen Sie nicht, daß auf die tatsächlich in einer Tabelle gespeicherten Werte mit :new Bezug genommen wird, wenn das Trigger-auslösende Ereignis eine INSERT-Anweisung ist.
Das folgende Listing zeigt den Inhalt der Tabelle Block_Trade_Log. Beachten Sie die beiden Aktien mit dem Zeichen QQQQQ; eine zu $ 102,125 und die andere zu $ 103,5. Wird für diese Aktie eine weitere Zeile in die Tabelle Block_Trade_Log eingefügt, wird der Trigger ausgelöst und der aktuelle Durchschnittswert der letzten drei Tage - 102,8125 - dieser Aktie berechnet und der Spalte Running_Avg_3_Days zugewiesen.
SQL> select * from block_trade_log;
STOCK_ PRICE BLOCKS_TRADED B RUNNING_AVG_3_DAYS TIMESTAMP
------ -------- ------------- - ------------------ ---------
QQQQQ 102.125 100 B 19-JUN-95
QQQQQ 103.5 100 S 19-JUN-95
VVVVV 55.75 3000 S 19-JUN-95
VVVVV 55.5 1000 B 20-JUN-95
SQL> insert into block_trade_log
2 (Stock_Symbol, Price, Blocks_Traded, Bought_Sold, Timestamp)
3 values
4 ('&stock',&price,&numblocks,'&BS','&date')
5 ;
Enter value for stock: QQQQQ
Enter value for price: 104.25
Enter value for numblocks: 300
Enter value for bs: B
Enter value for date: 20-JUN-95
old 4: ('&stock',&price,&numblocks,'&BS','&date')
new 4: ('QQQQQ',104.25,300,'B','20-JUN-95')
row created.
SQL> select * from block_trade_log;
STOCK_ PRICE BLOCKS_TRADED B RUNNING_AVG_3_DAYS TIMESTAMP
------ -------- ------------- - ------------------ ---------
QQQQQ 102.125 100 B 19-JUN-95
QQQQQ 103.5 100 S 19-JUN-95
VVVVV 55.75 3000 S 19-JUN-95
VVVVV 55.5 1000 B 20-JUN-95
QQQQQ 104.25 300 B 102.8125 20-JUN-95


Kaskadierende Trigger

Das Zusammenwirken von Triggern kann recht komplex sein. So können Sie z.B. einen Trigger erstellen, der bei seiner Auslösung einen weiteren Trigger auslöst. Derartige Trigger werden als kaskadierende Trigger bezeichnet. Um das Konzept kaskadierender Trigger zu erläutern, schauen Sie sich die folgenden drei einfachen Tabellen im Listing an - tab1, tab2 und tab3. Zu Beginn hat jede Tabelle eine einzige Zeile.

create table tab1
(col1 number);
create table tab2
(col2 number);
create table tab3
(col3 number);
SQL> select * from tab1;
COL1
-------
7
SQL> select * from tab2;
COL2
-------
10
SQL> select * from tab3;
COL3
-------
13
Erstellen Sie für Tabelle tab1 einen BEFORE-UPDATE-Trigger auf Zeilenebene, der den alten Wert der Spalte col1 aus der Tabelle tab1 in die Tabelle tab2 einfügt, wie es in Listing 11.34 gezeigt wird. Für die Tabelle tab2 erstellen Sie einen BEFORE-INSERT-Trigger auf Zeilenebene, der die Tabelle tab3 aktualisiert und den Wert der Spalte col3 auf den neuen Wert der Spalte col2 setzt. Schließlich erstellen Sie für die Tabelle tab3 einen AFTER-UPDATE-Trigger auf Anweisungsebene, der in Tabelle tab3 eine Zeile mit dem Wert 27 der Spalte col3 setzt.
SQL> create or replace trigger tab1_Update_Before before
2 update on tab1
3 for each row
4
4 declare
5
5 begin
6
6 insert into tab2
7 (col2)
8 values
9 (:old.col1);
10
10 end;
11 /
Trigger created.
SQL> create or replace trigger tab2_Insert_Before before
2 insert on tab2
3 for each row
4
4 declare
5
5 begin
6
6 update tab3
7 set
8 col3 = :new.col2;
9
9 end;
10 /
Trigger created.
SQL> create or replace trigger tab3_Update_After after
2 update on tab3
3
3 declare
4
4 begin
5
5 insert into tab3
6 (col3)
7 values
8 (27);
9
9 end;
10 /
Trigger created.
Eine Tabelle ändert sich, wenn ihr Inhalt durch eine INSERT-, UPDATE- oder DELETE-Anweisung verändert wird und diese Änderungen noch nicht festgeschrieben sind. Ein Trigger auf Zeilenebene kann Inhalte einer sich verändernden Tabelle weder lesen noch verändern, weil eine sich ändernde Tabelle sich in einem nicht definierten Zustand befindet. Die einzige Ausnahme stellt ein BEFORE-INSERT-Trigger auf Zeilenebene dar, weil eine Tabelle mit einem Fremdschlüssel Spaltenwerte der Tabelle mit dem Primärschlüssel verändern kann. Um mehr über sich verändernde Tabellen zu erfahren, schauen Sie im online verfügbaren Kapitel 8, des Oracle7 Server Application Developer's Guide, nach. Wenn Sie die Oracle-Dokumentation auf Ihren Windows-95-PC installiert haben, finden Sie sie unter C:\Orawin95\Doc\AS2536_1.PDF.

Was geschieht nun, wenn in tab1 eine Zeile aktualisiert wird? Wie im Listing zu sehen ist, finden die folgenden Veränderungen statt:

Standardmäßig ist die Anzahl kaskadierender Trigger auf 32 begrenzt. Denken Sie jedoch immer daran, daß Ihr Verständnis für die Auswirkungen der Anweisungen INSERT, UPDATE und DELETE in einem umgekehrt proportionalen Verhältnis zur Anzahl der dieser SQL-Anweisung zugeordneten kaskadierenden Trigger steht. Mit anderen Worten, machen Sie es nicht unnötig kompliziert.

commit- und rollback-Anweisungen in Triggern nicht erlaubt

In einem Datenbank-Trigger können Sie keine COMMIT- oder ROLLBACK-Anweisung ausführen.
Als Rollback wird der Prozeß des Verwerfens oder Rückgängigmachens aller an der Datenbank von einem Benutzer seit Ausführung der letzten COMMIT-Anweisung oder dem Beginn der Datenbank-Arbeitssitzung durchgeführten Änderungen bezeichnet.

Außerdem kann ein Trigger keine gespeicherte Prozedur, Funktion oder vordefiniertes Unterprogramm aufrufen, die eine ROLLBACK- oder COMMIT-Anweisung ausführen. Oracle besitzt diese Einschränkung aus einem wichtigen Grund. Trifft ein Trigger auf einen Fehler, sollten alle von diesem Trigger verursachten Änderungen an der Datenbank rückgängig gemacht werden. Hätte der Trigger aber einen Teil der Änderungen an der Datenbank festgeschrieben, wäre Oracle nicht in der Lage, die gesamte Transaktion rückgängig zu machen.

Aufruf von gespeicherten Prozeduren in einem Trigger

Sie können eine gespeicherte Prozedur oder Funktion, ob eigenständig oder als Teil eines Pakets, aus dem PL/SQL-Block eines Datenbank-Trigger aufrufen. Als Beispiel folgt eine Version des Trigger Block_Trade_Log_BI, die ursprünglich aus einem vorstehenden Listing stammt und die gespeicherte Funktion Get_3_Day_Running_Avg aufruft. Der Trigger basiert auf der Tabelle Block_Trade_Log aus der vorstehenden Lektion. Das folgende Listing enthält die gespeicherte Funktion, auf die der Trigger Bezug nehmen wird.

SQL> create or replace function Get_3_Day_Running_Avg
2 (Stock_Symb in varchar2)
3 return number is
4
4 Running_Avg number;
5
5 begin
6
6 select avg(price)
7 into Running_Avg
8 from Block_Trade_Log
9 where
10 Stock_Symbol = Stock_Symb and
11 Timestamp >= SYSDATE-3;
12
12 return Running_Avg;
13
13 end;
14 /
Function created.
Das nachstehende Listing enthält eine modifizierte Version von Block_Trade_Log_BI, die die gespeicherte Funktion Get_3_Day_Running_Avg aufruft.
SQL> create or replace trigger Block_Trade_Log_BI before
2 insert on Block_Trade_log
3 for each row
4
4 declare
5
5 Running_Avg number;
6
6 begin
7
7 :new.Running_Avg_3_Days :=
[ccc]Get_3_Day_Running_Avg (:new.Stock_Symbol);
8
8 end;
9 /
Trigger created.
Löschen, Aktivieren und Deaktivieren von Triggern

Haben Sie sich entschlossen, daß Sie einen bestimmten Trigger wirklich nicht einsetzen wollen, können Sie ihn mit der folgenden Anweisung löschen:

DROP TRIGGER trigger-name
Um z.B. den Trigger DELETE AFTER für die Tabelle Repair Header zu löschen, geben Sie folgende Anweisung in SQL*Plus ein:
SQL> drop trigger Repair_Header_Delete_After;
Trigger dropped.
Manchmal ist das Löschen eines Trigger eine zu drastische Maßnahme. Statt dessen möchten Sie ihn vielleicht nur zeitweilig außer Kraft setzen. Sie können einen Trigger solange deaktivieren, bis dessen Einsatz wieder sinnvoll ist. Um einen Trigger vorübergehend zu deaktivieren, verwenden Sie die ALTER-TRIGGER-Anweisung
ALTER TRIGGER trigger-name DISABLE.
Das folgende Beispiel deaktiviert den Trigger Repair_Header_Delete_After;
SQL> alter trigger Repair_Header_Delete_After disable;
Trigger altered.
Um einen deaktivierten Trigger zu aktivieren, verwenden Sie die Anweisung
ALTER TRIGGER trigger-name ENABLE.
So können Sie z.B. den Trigger Repair_Header_Delete_After durch folgenden Befehl aktivieren:
SQL> alter trigger Repair_Header_Delete_After enable;
Trigger altered.
home
... noch 'ne Quelle (SQL in 21 Tagen)

Ein Imprint des Markt&Technik Buch- und Software-Verlag GmbH.
Elektronische Fassung des Titels: Oracle in 21 Tagen, ISBN: 3-8272-2017-3