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;

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

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

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:

• DUP_VAL_ON_INDEX
• INVALID_NUMBER
• NO_DATA_FOUND
• TOO_MANY_ROWS
• VALUE_ERROR

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 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 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

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.

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 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

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

exception-name EXCEPTION;

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!

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

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.

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

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

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.

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

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;

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;

Ö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;

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

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.

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

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.

Die Syntax lautet folgendermaßen:

EXIT {label}{WHEN condition}

exit when get_instructors%notfound;

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

• um ihn mit anderen Parametern wieder öffnen zu können
• um die vom Cursor belegten Ressourcen freizugeben.

close get_instructors;

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

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.

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.

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.

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.

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:

• Die Regel wird nur dann durchgesetzt, wenn ein Anwendungs- oder Hilfsprogramm diese Routine aufruft.
• Eine Liste der Algorithmen zu erhalten, die zur Durchsetzung von Regeln verwendet werden, ist schwierig. Sie müssen sich auf Unterlagen stützen (die unzuverlässig sein könnten) oder vor der unangenehmen Aufgabe stehen, Anwendungs-Software zu überarbeiten.

• Durchsetzung ausgeklügelter Sicherheitsrichtlinien.
• Ändern eines Spaltenwerts auf der Grundlage von Werten in anderen Spalten derselben oder einer anderen Tabelle.
• Komplexe Validierung von Spaltenwerten - so könnte es z.B. notwendig sein, daß Sie einen Spaltenwert mit dem Aggregat eines Spaltenwerts einer anderen Tabelle vergleichen müssen.

Dokumentieren der Änderungen an einem Datensatz durch Schreiben der geänderten Werte in eine andere Tabelle.

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

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

• Trigger auf Anweisungsebene enthalten nicht die Klausel FOR EACH ROW in der CREATE-TRIGGER-Anweisung.
• Trigger auf Zeilenebene enthalten die Klausel FOR EACH ROW in der CREATE-TRIGGER-Anweisung.

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.

• Bei einer INSERT-Anweisung sind die Werte, die eingefügt werden, in :new.column-name enthalten, und column-name steht für eine Spalte der Tabelle.
• Bei einer UPDATE-Anweisung ist der ursprüngliche Spaltenwert in :old.column-name; und der neue Spaltenwert in :new.column-name enthalten.
• Bei einer DELETE-Anweisung sind die Spaltenwerte der zu entfernenden Zeile in :old.column-name enthalten.

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

• Das Einfügen einer neuen Zeile in eine Tabelle mit der INSERT-Anweisung.
• Das Aktualisieren einer Reihe von Zeilen (oder keiner Zeile) durch eine UPDATE-Anweisung.
• Das Entfernen einer Reihe von Zeilen (oder keiner Zeile) mit einer DELETE-Anweisung.

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.

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:

• Sechs Trigger auf Zeilenebene für BEFORE DELETE, BEFORE INSERT, BEFORE UPDATE, AFTER DELETE, AFTER INSERT und AFTER UPDATE
• Sechs Trigger auf Anweisungsebene für BEFORE DELETE, BEFORE INSERT, BEFORE UPDATE, AFTER DELETE, AFTER INSERT und AFTER UPDATE

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.

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

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.

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

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

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

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.

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

• In tab1 ist der Wert von col1 auf 8 aktualisiert worden.
• In tab2 hat der Trigger Tab1_Update_Before eine Zeile mit dem alten Wert 7 von col1 eingefügt.
• In tab3 wurde der Trigger Tab2_Insert_Before infolge der neuen Zeile in tab2 ausgelöst und der Wert von col3 auf den in tab2 eingefügten Wert 7 gesetzt. Als tab3 aktualisiert wurde, hat der Trigger Tab_Update_Before eine weitere Zeile in tab3 mit dem Wert 27 eingefügt.

 

 
 
 

SQL> update tab1
2 set col1 = 8;
row updated.
SQL> select * from tab1;
COL1
-------
8
SQL> select * from tab2;
COL2
-------
10
7
SQL> select * from tab3;
COL3
-------
7
27

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.

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.

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

SQL> drop trigger Repair_Header_Delete_After;
Trigger dropped.

ALTER TRIGGER trigger-name DISABLE.

SQL> alter trigger Repair_Header_Delete_After disable;
Trigger altered.

ALTER TRIGGER trigger-name ENABLE.

SQL> alter trigger Repair_Header_Delete_After enable;
Trigger altered.