PL/SQL: Eine Einführung

Einführung
Datentypen in PL/SQL
Die Struktur eines PL/SQL-Blocks
Der DECLARE-Abschnitt
Der PROCEDURE-Abschnitt
Der EXCEPTION-Abschnitt
Gespeicherte Prozeduren, Pakete und Trigger

Einführung

PL/SQL ist die prozedurale Sprache von Oracle. Sie umfaßt die Standardsprache von SQL und einen weiten Bereich von Befehlen, mit denen man die Ausführung von SQL-Anweisungen entsprechend unterschiedlicher Bedingungen steuern kann. PL/SQL kann auch Laufzeitfehler behandeln. Möglichkeiten wie Schleifen und IF...THEN-Anweisungen verleihen PL/SQL die Leistungsfähigkeit von Programmiersprachen der dritten Generation. Es lassen sich interaktive, benutzerfreundliche Programme schreiben, die Werte an Variablen übergeben können. Zum Lieferumfang gehören verschiedene vordefinierte Pakete, zum Beispiel für die Anzeige von Meldungen für den Benutzer.

Schlüsselmerkmale von PL/SQL:

Datentypen in PL/SQL

Die meisten Datentypen sind weitgehend einheitlich definiert, wobei aber bei den einzelnen Implementierungen gewisse Eigenheiten hinsichtlich der Speicherung und der internen Verarbeitung zu beachten sind. Variablen, die man in PL/SQL-Blöcken deklariert, müssen gültige Datentypen aufweisen.
Oracle stellt in PL/SQL sogenannte Untertypen zu den Datentypen bereit. Beispielsweise hat der Datentyp NUMBER einen Untertyp INTEGER. Mit Untertypen läßt sich die Kompatibilität der Datentypen eines PL/SQL-Programms zu Datentypen in anderen Programmen wie zum Beispiel Cobol erreichen, insbesondere wenn man PL/SQL-Code in ein anderes Programm einbettet. Untertypen sind lediglich alternative Namen für die Datentypen von Oracle, und man muß demnach die Regeln des zugehörigen Originaltyps beachten.

Zeichenstrings

Die in PL/SQL als Zeichenstrings vorhandenen Datentypen sind allgemein für die Aufnahme von alphanumerischen Werten definiert. Beispiele für Zeichenstring-Datentypen sind Namen, Codes, Beschreibungen und Seriennummern, die Zeichen enthalten.
In Variablen vom Datentyp CHAR lassen sich Zeichenstrings fester Länge mit maximal 32767 Byte speichern, obwohl man sich in einer Tabelle kaum einen derartig großen Wert dieses Datentyps vorstellen kann.

VARCHAR2 speichert Zeichenstrings variabler Länge. Normalerweise verwendet man VARCHAR2 anstelle von CHAR, um Daten veränderlicher Länge zu speichern, beispielsweise Personennamen. Die maximale Länge von VARCHAR2 beträgt ebenfalls 32767 Byte. LONG speichert ebenfalls Strings variabler Länge, wobei die maximale Zeichenzahl 32760 Byte beträgt. In der Regel verwendet man LONG, um längere Texte (etwa Anmerkungen) zu speichern, obwohl sich VARCHAR2 genausogut dafür eignet.

Numerische Datentypen

In einer Oracle-Datenbank lassen sich im Datentyp NUMBER beliebige Zahlenwerte speichern.

Die Genauigkeit der Daten läßt sich im Typ NUMBER mit der folgenden Syntax festlegen:
NUMBER (Genauigkeit, Skala)
Untertypen: DEC, DECIMAL, DOUBLE PRECISION, INTEGER, INT, NUMERIC, REAL, SMALLINT, FLOAT
PLS_INTEGER definiert Spalten, die ganze Zahlen mit Vorzeichen (negative Zahlen) enthalten können.

Binäre Datentypen

Binäre Datentypen speichern Daten, die in einem binären Format vorliegen, beispielsweise Grafiken oder Fotos. Zu diesen Datentypen gehören RAW und LONG RAW.

Der Datentyp DATE

DATE ist der gültige Oracle-Datentyp zur Speicherung von Datumswerten. Wenn man eine Spalte als DATE definiert, gibt man keine Länge an, da die Länge des DATE-Felds implizit enthalten ist. Das Format eines Oracle-Datums lautet zum Beispiel 01.10.97 oder 01-OCT-97.

BOOLEAN

BOOLEAN speichert die Werte TRUE, FALSE und NULL. Wie DATE erfordert BOOLEAN keine Parameter, wenn man diesen Datentyp für die Definition einer Spalte oder einer Variablen angibt.

ROWID

ROWID ist eine Pseudospalte, die in jeder Tabelle einer Oracle-Datenbank vorhanden ist. ROWID wird im binären Format gespeichert und identifiziert die Zeilen einer Tabelle. Indizes verwenden ROWIDs als Zeiger auf Daten.

Die Struktur eines PL/SQL-Blocks

PL/SQL ist eine Sprache mit Blockstruktur, das heißt PL/SQL-Programme sind in logische Codeblöcke unterteilt. Innerhalb eines PL/SQL-Codeblocks lassen sich Prozesse wie Datenmanipulationen oder Abfragen formulieren. Ein PL/SQL-Block baut sich aus den folgenden Teilen auf:

Die grundlegende Struktur eines PL/SQL-Blocks stellt sich wie folgt dar: Kommentare

Was wäre ein Programm ohne Kommentare? Alle Programmiersprachen bieten Befehle, mit denen man Kommentare in den Code einfügen kann. PL/SQL bildet auch hier keine Ausnahme. Die Kommentare auf den Zeilen in der oben dargestellten Blockstruktur beschreiben jeden Befehl. In PL/SQL kennzeichnet man Kommentare wie folgt:

PL/SQL unterstützt direkt die Befehle der Datenmanipulationssprache (DML) und Datenbankabfragen. Dagegen werden die Befehl der Datendefinitionssprache (DLL) nicht unterstützt. Im allgemeinen setzt man PL/SQL ein, um die Daten innerhalb der Datenbankstruktur zu manipulieren, aber nicht, um diese Strukturen zu verändern.

Der DECLARE-Abschnitt

Der DECLARE-Abschnitt in einem PL/SQL-Codeblock besteht aus Variablen, Konstanten, Cursordefinitionen und speziellen Datentypen. Als PL/SQL-Programmierer können Sie alle Variablentypen innerhalb Ihrer Codeblöcke deklarieren. Allerdings müssen Sie jeder definierten Variablen einen Datentyp zuweisen, der den Regeln von Oracle für den betreffenden Datentyp entspricht. Variablen müssen außerdem den Standards von Oracle zur Benennung genügen.

Variablenzuweisung

Variablen sind Werte, die sich innerhalb eines PL/SQL-Blocks verändern lassen. PL/SQL-Variablen ist ein gültiger Datentyp bei der Deklaration zuzuweisen. Bei Bedarf kann man Variablen initialisieren. Das folgende Beispiel definiert einen Satz von Variablen im DECLARE-Abschnitt eines Blocks:

DECLARE
owner char(10);
tablename char(30);
bytes number(10);
today date;
Der DECLARE-Abschnitt eines Blocks ist nicht selbständig ausführbar. Der Abschnitt wird mit der Anweisung DECLARE eingeleitet. Daran schließen sich die Definitionen der Variablen auf jeweils eigenen Zeilen an. Jede Variablendeklaration endet mit einem Semikolon.
Im DECLARE-Abschnitt kann man Variablen auch initialisieren. Dazu ein Beispiel:
DECLARE
Kunde char(30);
Rechnungsjahr number(2) := '97';
Mit dem Symbol := initialisiert man eine Variable im DECLARE-Abschnitt, das heißt man weist dieser Variablen einen Anfangswert zu. Eine als NOT NULL definierte Variable muß initialisiert werden.
DECLARE
Kunde char(30);
Rechnungsjahr number(2) NOT NULL := '97';
Konstantenzuweisung

Konstanten definiert man in der gleichen Weise wie Variablen. Konstante Werte sind allerdings statisch und ändern sich nicht. Im obigen Beispiel stellt Rechnungsjahr wahrscheinlich eine Konstante dar. Jede Variablendeklaration muß mit einem Semikolon enden.

Cursordefinitionen

Ein Cursor ist ein weiterer Variablentyp in PL/SQL. Bei einer Variablen denkt man gewöhnlich an einen einzelnen Wert. Ein Cursor ist eine Variable, die auf eine Datenzeile in der Ergebnismenge einer Abfrage zeigt. In einer mehrzeiligen Ergebnismenge braucht man eine Möglichkeit, um durch jeden Datensatz zu scrollen und die Daten zu analysieren. Genau dazu dient ein Cursor. Wenn der PL/SQL-Block die Ergebnismenge einer Abfrage innerhalb des Blocks bearbeitet, zeigt ein Cursor auf die zurückgegebene Zeile. Das folgende Beispiel zeigt, wie man in PL/SQL einen Cursor definiert:

DECLARE
cursor employee_cursor is
select * from employees;
Ein Cursor ist einer Sicht ähnlich. Mit einer Schleife im PROCEDURE-Abschnitt kann man durch einen Cursor scrollen. Diese Technik wird in Kürze gezeigt.

Das Attribut %TYPE

Das Attribut %TYPE liefert den Datentyp einer Variablen oder einer Datenbankspalte. Statt den Datentyp fest im PL/SQL-Block zu kodieren, läßt sich mit %TYPE die Konsistenz der Datentypen in den Codeblöcken gewährleisten.

DECLARE
cursor employee_cursor is
select emp_id, emp_name from employees;
id_num employees.emp_id%TYPE;
name employees.emp_name%TYPE;
Das Attribut %ROWTYPE

Variablen sind nicht auf einzelne Werte beschränkt. Eine Variable kann zum Beispiel mit einem definierten Cursor verbunden sein. Das Attribut %ROWTYPE liefert einen Datensatztyp zurück, der eine ganze Zeile in einer Tabelle (oder Sicht) repräsentiert. Damit kann man den Datentyp der Variablen auf die Datentypen der Spalten in der betreffenden Datenzeile des Cursors festlegen. Im folgenden Beispiel erzeugt das Attribut %ROWTYPE eine Datensatzvariable.

DECLARE
cursor employee_cursor is
select emp_id, emp_name from employee;
employee_record employee_cursor%ROWTYPE;


Das Attribut %ROWCOUNT

Das PL/SQL-Attribut %ROWCOUNT liefert die Anzahl der Zeilen, die von SQL-Anweisungen im betreffenden Block aus einem Cursor zurückgegeben wurden.

DECLARE
cursor employee_cursor is
select emp_id, emp_name from employees;
records_processed := employee_cursor%ROWCOUNT;
Achten Sie bei der Deklaration von Variablen auf Konflikte mit Tabellennamen. Deklariert man zum Beispiel eine Variable mit demselben Namen wie eine Tabelle, auf die man im PL/SQL-Code zugreifen möchte, hat die lokale Variable den Vorrang gegenüber dem Tabellennamen.

Der PROCEDURE-Abschnitt

Der PROCEDURE-Abschnitt ist der einzige obligatorische Teil eines PL/SQL-Blocks. Dieser Teil des Blocks ruft Variablen auf und manipuliert mit Cursor die Daten in der Datenbank. Der PROCEDURE-Abschnitt ist der Hauptteil eines Blocks und enthält Bedingungsanweisungen und SQL-Befehle.

BEGIN...END

In einem Block kennzeichnet die Anweisung BEGIN den Anfang einer Prozedur. Analog dazu markiert die END-Anweisung das Ende der Prozedur. Das folgende Beispiel zeigt die Grundstruktur des PROCEDURE-Abschnitts:

Befehle zur Cursor-Steuerung

Nachdem Sie gelernt haben, wie man Cursor in einem PL/SQL-Block definiert, müssen Sie wissen, wie man auf die definierten Cursor zugreift. Dieser Abschnitt erläutert die grundlegenden Befehle zur Cursor-Steuerung: DECLARE, OPEN, FETCH und CLOSE.

DECLARE

Weiter vorn in der heutigen Lektion wurde gezeigt, wie man einen Cursor im DECLARE-Abschnitt eines Blocks definiert. Die DECLARE-Anweisung gehört zur Liste der Cursor-Steuerbefehle.

OPEN

Genau wie man ein Buch nicht lesen kann, bevor man es öffnet, muß man einen definierten Cursor erst öffnen und kann erst dann darauf zugreifen. Einen Cursor öffnet man mit dem Befehl OPEN, wie es das folgende Beispiel zeigt:

FETCH

Der Befehl FETCH füllt eine Variable mit den Werten aus einem Cursor. Hier zwei Beispiele für den Einsatz von FETCH: das erste füllt eine Aggregatvariable, das andere Beispiel zwei einzelne Variablen.

DECLARE
cursor employee_cursor is
select emp_id, emp_name from employees;
employee_record employee_cursor%ROWTYPE;
BEGIN
open employee_cursor;
loop
fetch employee_cursor into employee_record;
end loop;
close employee_cursor;
END
Das obige Beispiel holt die aktuelle Zeile des Cursors in die Aggregatvariable employee_record. Der Cursor wird in einer Schleife durchlaufen. Ansonsten führt dieses Beispiel keine sinnvollen Aktionen aus.
DECLARE
cursor employee_cursor is
select emp_id, emp_name from employees;
id_num employees.emp_id%TYPE;
name employees.emp_name%TYPE;
BEGIN
open employee_cursor;
loop
fetch employee_cursor into id_num, name;
end loop;
close employee_cursor;
END
CLOSE

Wenn man die Arbeit mit einem Cursor in einem Block beendet hat, sollte man den Cursor schließen. Der entsprechende Befehl lautet CLOSE.

Nachdem ein Cursor geschlossen wurde, existiert die Ergebnismenge einer Abfrage nicht mehr. Man muß den Cursor erneut öffnen, um auf die zugehörige Datenmenge zuzugreifen.

Bedingungsanweisungen

Nun kommen wir zur eigentlichen Würze von PL/SQL. Mit den Bedingungsanweisungen können Sie die Verarbeitung der SQL-Anweisungen beeinflussen. Die PL/SQL-Bedingungsanweisungen findet man so oder in ähnlicher Form in den meisten Sprachen der dritten Generation wieder.

IF...THEN

Die Bedingungsanweisung IF...THEN dürfte den meisten Programmierern am vertrautesten sein. Die IF...THEN-Anweisung schreibt die Ausführung bestimmter Aktionen vor, wenn die angegebenen Bedingungen erfüllt sind. Die Struktur einer IF...THEN-Anweisung sieht folgendermaßen aus:

Prüft man auf zwei Bedingungen, kann man die Anweisung wie folgt schreiben: Bei mehr als zwei Bedingungen läßt sich die Anweisung folgendermaßen formulieren:


Schleifen (LOOP)

In einem PL/SQL-Block kann man Anweisung in Schleifen solange wiederholen, bis die angegebene Bedingung erfüllt ist. Man unterscheidet drei Arten von Schleifen.
Als einfachste Form der Schleifenanweisungen realisiert LOOP eine Endlosschleife, die man häufig für das Durchlaufen eines Cursors einsetzt. Damit eine derartige Schleife terminiert, muß man einen Austrittspunkt spezifizieren. Zum Beispiel soll beim Durchlaufen eines Cursors die Schleife nach der Verarbeitung der letzten Zeile im Cursor verlassen werden:

BEGIN
open employee_cursor;
LOOP
FETCH employee_cursor into employee_record;
EXIT WHEN employee_cursor%NOTFOUND;
statement1;
.
.
.
END LOOP;
close employee_cursor;
END;
Das Cursor-Attribut %NOTFOUND liefert das Ergebnis TRUE, wenn die letzte FETCH-Operation keine Zeile aus dem Cursor zurückgegeben hat. Im Beispiel wird damit die Schleife verlassen, wenn der Cursor keine Daten mehr enthält. Gibt man diese Anweisung nicht in der Schleife an, läuft die Schleife auf immer und ewig.
Eine WHILE-Schleife führt Befehle aus, solange eine bestimmte Bedingung TRUE ist. Sobald die Bedingung nicht mehr TRUE ist, gibt die Schleife die Steuerung des Programmablaufs an die nächste Anweisung nach der Schleife ab.
DECLARE
cursor payment_cursor is
select cust_id, payment, total_due from payment_table;
cust_id payment_table.cust_id%TYPE;
payment payment_table.payment%TYPE;
total_due payment_table.total_due%TYPE;
BEGIN
open payment_cursor;
WHILE payment < total_due LOOP
FETCH payment_cursor into cust_id, payment, total_due;
EXIT WHEN payment_cursor%NOTFOUND;
insert into underpay_table
values (cust_id, 'STILL OWES');
END LOOP;
close payment_cursor;
END;
Im vorherigen Block läßt sich auch die FOR-Schleife einsetzen, um implizit die aktuelle Zeile des Cursors in die definierten Variablen zu holen.
DECLARE
cursor payment_cursor is
select cust_id, payment, total_due from payment_table;
cust_id payment_table.cust_id%TYPE;
payment payment_table.payment%TYPE;
total_due payment_table.total_due%TYPE;
BEGIN
FOR pay_rec IN payment_cursor LOOP
IF pay_rec.payment < pay_rec.total_due THEN
insert into underpay_table
values (pay_rec.cust_id, 'STILL OWES');
END IF;
END LOOP;
END;
Dieses Beispiel durchläuft den Cursor mit einer FOR-Schleife. Die FOR-Schleife schließt in Verbindung mit einem Cursor die Befehle OPEN, FETCH und CLOSE ein, so daß man auf diese Befehle verzichten kann. Beachten Sie, daß auch das Attribut %NOTFOUND fehlt. Dieses Attribut ist implizit in der FOR-Schleife enthalten. Demzufolge liefert dieses und das vorherige Beispiel die gleichen Ergebnisse.

Der EXCEPTION-Abschnitt

Der EXCEPTION-Abschnitt ist ein optionaler Teil eines PL/SQL-Blocks. Wenn dieser Abschnitt fehlt und Fehler auftreten, wird der Block beendet. Bestimmte Fehler rechtfertigen aber nicht die unmittelbare Beendigung eines Blocks, so daß man mit dem EXCEPTION-Abschnitt spezielle Fehler oder benutzerdefinierte Ausnahmen in einer ordnungsgemäßen Weise behandeln kann. Exceptions (deutsch: Ausnahmen) können benutzerdefiniert sein, obwohl viele Exceptions bereits durch Oracle vordefiniert sind.

Exceptions auslösen

Exceptions werden in einem Block mit dem Befehl RAISE ausgelöst. Dabei kann der Programmierer explizit Exceptions auslösen, während interne Datenbankfehler automatisch - oder implizit - vom Datenbankserver ausgelöst werden.

Dieser Block zeigt die Grundlagen für das explizite Auslösen einer Exception. Zuerst wird der Exceptionname mit der Anweisung EXCEPTION deklariert. Im PROCEDURE-Abschnitt löst ein RAISE-Befehl die Exception aus, wenn eine gegebene Bedingung erfüllt ist. Die RAISE-Operation referenziert dann den EXCEPTION-Abschnitt des Blocks, wo die entsprechenden Anweisungen zur Fehlerbehandlung (oder andere Aktionen) stehen.

Exceptions behandeln

Das obige Beispiel hat eine Exception im EXCEPTION-Abschnitt des Blocks behandelt. In PL/SQL kann man problemlos auf Fehler reagieren und mit Hilfe von Exceptions den PL/SQL-Block entweder mit einem bekannten Fehler fortsetzen oder auf sanfte Weise beenden.

Dieses Beispiel zeigt, wie der EXCEPTION-Block aussehen könnte, wenn man mehrere Exceptions zu behandeln hat. Das Beispiel erwartet zwei Exceptions (Exception1 und Exception2), wenn dieser Block gestartet wird. Die Anweisung WHEN OTHERS bewirkt die Ausführung von Anweisung3, wenn keine anderen Exceptions auftreten, während der Block abgearbeitet wird. Die Klausel WHEN OTHERS erlaubt die Kontrolle über alle Fehler, die innerhalb des Blocks auftreten.

Einen PL/SQL-Block ausführen

PL/SQL-Anweisungen werden normalerweise mit einem Host-Editor erzeugt und wie normale SQL-Skript-Dateien ausgeführt. In PL/SQL ist jede Anweisung in einem Block mit einem Semikolon abzuschließen - angefangen bei Variablenzuweisungen bis zu Befehlen der Datenmanipulation. Der Schrägstrich (/) ist hauptsächlich in SQL-Skript-Dateien zu finden, PL/SQL beendet mit dem Schrägstrich aber auch einen Block in einer Skript-Datei. Ein PL/SQL-Block läßt sich am einfachsten mit dem Befehl START ausführen, den man auch als STA oder @ abkürzen kann.

Die PL/SQL-Skriptdatei führen Sie folgendermaßen aus:
SQL> start proc1
oder
SQL> sta proc1
oder
SQL> @proc1
PL/SQL-Skript-Dateien lassen sich auch aus anderen PL/SQL-Dateien, Shell-Skripts oder Programmen aufrufen.

Ausgaben anzeigen

Insbesondere bei der Behandlung von Exceptions möchte man die Benutzer über die ablaufenden Vorgänge informieren. In der Ausgabe lassen sich einerseits Daten anzeigen, andererseits aber auch eigene Fehlermeldungen, die für den Benutzer sicherlich nützlicher sind als kryptische Fehlernummern. Sollte die Fehlermeldung zu kompliziert sein, können Sie den Benutzer auch bitten, sich mit dem Datenbank-Administrator in Verbindung zu setzen.
PL/SQL bietet zwar keine direkten syntaktischen Mittel für eine Anzeige, erlaubt aber den Aufruf eines Pakets aus einem Block heraus, um diese Funktion zu realisieren. Das betreffende Paket heißt DBMS_OUTPUT.

EXCEPTION
WHEN zero_divide THEN
DBMS_OUTPUT.put_line('Fehler: Division durch 0. Bitte DBA kontaktieren.');
ZERO_DIVIDE ist eine von Oracle vordefinierte Exception. Die gebräuchlichsten Fehler, die während der Programmabarbeitung auftreten, sind als Exceptions vordefiniert und werden implizit ausgelöst (das heißt Sie brauchen den Fehler nicht im PROCEDURE-Abschnitt des Blocks auslösen).

Wenn Sie den Block mit

SQL> @block1
ausführen und die Exception während der Abarbeitung des Blocks auftritt, erhalten Sie die Fehlermeldung:
Fehler: Division durch 0. Bitte DBA kontaktieren.

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

Sieht diese Meldung nicht freundlicher aus als:
SQL> @block1
begin
*

FEHLER in Zeile 1:
ORA-01476: Divisor ist Null
ORA-06512: in Zeile 20

Transaktionssteuerung in PL/SQL

Mit den Befehlen zur Transaktionssteuerung COMMIT, ROLLBACK und SAVEPOINT kann der Programmierer steuern, wann und wie oft Transaktionen tatsächlich in die Datenbank zu übernehmen und wann sie rückgängig zu machen sind.

Das Gute an PL/SQL ist, daß sich die Befehle zur Transaktionssteuerung automatisieren lassen. Die manchmal lästige Überwachung umfangreicher Transaktionen entfällt damit.

Beispiele für Tabellen und Daten

Um PL/SQL-Blöcke zu erstellen, verwenden wir an dieser Stelle zwei Tabellen. ZAHLUNGEN speichert den Namen des Kunden, wieviel er bezahlt hat und wie hoch seine Restschuld ist. ZAHLUNG_STATUS enthält noch keinerlei Daten. Diese werden in die Tabelle entsprechend den Bedingungen in der Tabelle ZAHLUNGEN eingefügt.

SQL> SELECT *
2 FROM ZAHLUNGEN;

KUN ZAHLG SCHULD
--- --------- ---------
ABC 90,5 150,99
AAA 79 79
BBB 950 1000
CCC 27,5 27,5
DDD 350 500,95
EEE 67,89 67,89
FFF 555,55 455,55
GGG 122,36 122,36
HHH 26,75 0

9 Zeilen ausgewählt.

SQL> DESCRIBE ZAHLUNG_STATUS

Name Null? Type
------------------------------- -------- ----
KUND_NR NOT NULL CHAR(3)
STATUS NOT NULL VARCHAR2(15)
SOLL NUMBER(8,2)
HABEN NUMBER(8,2)


Ein einfacher PL/SQL-Block

Das als Beispiel verwendete PL/SQL-Skript (block1.sql) sieht folgendermaßen aus:

set serveroutput on
BEGIN
DECLARE
AmtZero EXCEPTION;
cCustId ZAHLUNGEN.KUND_NR%TYPE;
fPayment ZAHLUNGEN.ZAHLG%TYPE;
fTotalDue ZAHLUNGEN.SCHULD%TYPE;
cursor ZAHLG_CURSOR is
select KUND_NR, ZAHLG, SCHULD
from ZAHLUNGEN;
fOverPaid number(8,2);
fUnderPaid number(8,2);
BEGIN
open ZAHLG_CURSOR;
loop
fetch ZAHLG_CURSOR into
cCustId, fPayment, fTotalDue;
exit when ZAHLG_CURSOR%NOTFOUND;
if ( fTotalDue = 0 ) then
raise AmtZero;
end if;
if ( fPayment > fTotalDue ) then
fOverPaid := fPayment - fTotalDue;
insert into ZAHLUNG_STATUS (KUND_NR, STATUS, HABEN)
values (cCustId, 'Zuviel bezahlt', fOverPaid);
elsif ( fPayment < fTotalDue ) then
fUnderPaid := fTotalDue - fPayment;
insert into ZAHLUNG_STATUS (KUND_NR, STATUS, SOLL)
values (cCustId, 'Restschuld', fUnderPaid);
else
insert into ZAHLUNG_STATUS
values (cCustId, 'Bezahlt', null, null);
end if;
end loop;
close ZAHLG_CURSOR;
EXCEPTION
when AmtZero then
DBMS_OUTPUT.put_line('Fehler: Betrag ist 0. Bitte Supervisor kontaktieren.');
when OTHERS then
DBMS_OUTPUT.put_line('Unbekannter Fehler. Bitte DBA konsultieren.');
END;
END;
/
Der DECLARE-Abschnitt definiert sechs lokale Variablen sowie einen Cursor namens ZAHLG_CURSOR. Der PROCEDURE-Abschnitt beginnt mit der zweiten BEGIN-Anweisung. Als erstes wird hier der Cursor geöffnet und eine Schleife gestartet. Der FETCH-Befehl übergibt die aktuellen Werte im Cursor in die Variablen, die im DECLARE-Abschnitt definiert wurden. Solange die Schleife Datensätze im Cursor findet, vergleicht die Anweisung den von einem Kunden bezahlten Betrag mit der gesamten Restschuld. Mehrzahlungen und Zahlungsverzug werden entsprechend der gezahlten Beträge berechnet. Dann übernimmt das Programm die berechneten Beträge in die Tabelle ZAHLUNG_STATUS. Die Schleife terminiert, und der Cursor wird geschlossen. Der EXCEPTION-Abschnitt behandelt Fehler, die während der Verarbeitung auftreten können.

Sehen wir uns nun an, was beim Start des PL/SQL-Skripts passiert.

SQL> @block1

Fehler: Betrag ist 0. Bitte Supervisor kontaktieren.

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

Diese Meldung zeigt, daß in der Spalte für die Gesamtschuld ein falscher Wert aufgetreten ist. Damit können Sie den Betrag korrigieren und das Skript erneut starten.
SQL> UPDATE ZAHLUNGEN
2 SET SCHULD = 26.75
3 WHERE KUND_NR = 'HHH';

1 Zeile wurde aktualisiert.

SQL> COMMIT;

Transaktion mit COMMIT abgeschlossen.

SQL> TRUNCATE TABLE ZAHLUNG_STATUS;

Tabelle mit TRUNCATE geleert.

Dieses Beispiel löscht den Inhalt der Tabelle ZAHLUNG_STATUS mit TRUNCATE TABLE. Der nächste Start des PL/SQL-Skripts füllt die Tabelle erneut. Die Anweisung TRUNCATE TABLE können Sie auch in Ihren PL/SQL-Block aufnehmen.
SQL> @block1

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

Nun können Sie aus der Tabelle ZAHLUNG_STATUS auswählen und die Zahlungsmoral für jeden Kunden anzeigen.
SQL> SELECT *
2 FROM ZAHLUNG_STATUS
3 ORDER BY STATUS;
KUN STATUS SOLL HABEN
--- --------------- --------- ---------
AAA Bezahlt
CCC Bezahlt
GGG Bezahlt
HHH Bezahlt
EEE Bezahlt
ABC Restschuld 60,49
DDD Restschuld 150,95
BBB Restschuld 50
FFF Zuviel bezahlt 100

9 Zeilen ausgewählt.

In die Tabelle ZAHLUNG_STATUS wird für jede Datenzeile, die in der Tabelle ZAHLUNGEN enthalten ist, eine Zeile eingefügt. Wenn der Kunde mehr als den geforderten Betrag bezahlt hat, kommt die Differenz in die Spalte HABEN. Hat der Kunde weniger als den Rechnungsbetrag bezahlt, erfolgt ein Eintrag in die Spalte SOLL. Bei vollständiger Bezahlung bleiben beide Spalten leer.

Ein anderes Programm

Dieses Beispiel arbeitet mit einer Tabelle namens LOHN:

SQL> DESC LOHN

Name Null? Type
------------------------------- -------- ----
NAME VARCHAR2(20)
ART VARCHAR2(3)
TARIF NUMBER(8,2)
SEIT DATE
TARIF_ALT NUMBER(8,2)


Sehen Sie sich zunächst die Daten an:

SQL> SELECT *
2 FROM LOHN
3 ORDER BY ART, TARIF DESC;

NAME ART TARIF SEIT TARIF_ALT
-------------------- --- --------- -------- ---------
CHRISSY ZOES GEH 50000 01.01.97
CLODE EVANS GEH 42150 01.03.97
JOHN SMITH GEH 35000 15.06.96
KEVIN TROLLBERG GEH 27500 15.06.96
SANDRA SAMUELS STD 12,5 01.01.97
ROBERT BOBAY STD 11,5 15.05.96
KEITH JONES STD 10 31.10.96
SUSAN WILLIAMS STD 9,75 01.05.97

8 Zeilen ausgewählt.

Die Situation: Der Umsatz ist gestiegen. Alle Mitarbeiter, die in den letzten sechs Monaten keine Gehaltserhöhung erhalten haben, werden heute damit bedacht. Alle in Frage kommenden stundenweise Beschäftigten erhalten eine 4prozentige Steigerung, und alle in Frage kommenden Gehaltsempfänger eine 5prozentige Erhöhung.

Heute ist:

SQL> SELECT SYSDATE
2 FROM DUAL;

SYSDATE
--------
12.12.97

Bevor wir den nächsten PL/SQL-Block untersuchen, führen wir eine manuelle Auswahl aus der Tabelle LOHN durch und zeigen die Mitarbeiter an, die eine Gehaltserhöhung erhalten sollten.
SQL> SELECT NAME, ART, TARIF, SEIT,
2 'JA' ANSPRUCH
3 FROM LOHN
4 WHERE SEIT < SYSDATE - 180
5 UNION ALL
6 SELECT NAME, ART, TARIF, SEIT,
7 'NEIN' ANSPRUCH
8 FROM LOHN
9 WHERE SEIT >= SYSDATE - 180
10 ORDER BY 2, 3 DESC;
 

NAME ART TARIF SEIT ANSP
-------------------- --- --------- -------- ----
CHRISSY ZOES GEH 50000 01.08.97 NEIN
CLODE EVANS GEH 42150 01.10.97 NEIN
JOHN SMITH GEH 35000 15.01.97 JA
KEVIN TROLLBERG GEH 27500 15.01.97 JA
SANDRA SAMUELS STD 12,5 01.08.97 NEIN
ROBERT BOBAY STD 11,5 15.12.96 JA
KEITH JONES STD 10 31.05.97 JA
SUSAN WILLIAMS STD 9,75 01.12.97 NEIN

8 Zeilen ausgewählt.

Die Spalte ANSPRUCH kennzeichnet die Mitarbeiter, die für eine Erhöhung in Frage kommen. Das PL/SQL-Skript sieht folgendermaßen aus:
set serveroutput on
BEGIN
DECLARE
UnknownPayType exception;
cursor LOHN_CURSOR is
select NAME, ART, TARIF, SEIT,
sysdate, rowid
from LOHN;
IndRec LOHN_CURSOR%ROWTYPE;
cOldDate date;
fNewPay number(8,2);
BEGIN
open LOHN_CURSOR;
loop
fetch LOHN_CURSOR into IndRec;
exit when LOHN_CURSOR%NOTFOUND;
cOldDate := sysdate - 180;
if (IndRec.ART = 'GEH') then
fNewPay := IndRec.TARIF * 1.05;
elsif (IndRec.ART = 'STD') then
fNewPay := IndRec.TARIF * 1.04;
else
raise UnknownPayType;
end if;
if (IndRec.SEIT < cOldDate) then
update LOHN
set TARIF = fNewPay,
TARIF_ALT = IndRec.TARIF,
SEIT = IndRec.sysdate
where rowid = IndRec.rowid;
commit;
end if;
end loop;
close LOHN_CURSOR;
EXCEPTION
when UnknownPayType then
dbms_output.put_line('=======================');
dbms_output.put_line('Fehler: Programm abgebrochen.');
dbms_output.put_line('Unbekannte Zahlungsart für: ' || IndRec.Name);
when others then
dbms_output.put_line('Fehler bei Verarbeitung. Bitte DBA kontaktieren.');
END;
END;
/
Sind Sie sicher, daß Sie vier Mitarbeitern eine Gehaltserhöhung geben wollen? (Die letzte SELECT-Anweisung zeigt vier JA-Werte in der Spalte ANSPRUCH.) Warum eigentlich nicht? Die entsprechende Anhebung können Sie zuweisen, indem Sie das PL/SQL-Skript namens BLOCK2.SQL ausführen:
SQL> @block2

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

Mit einer einfachen Auswahl können Sie sich davon überzeugen, daß die Änderungen an TARIF der in Frage kommenden Personen vorgenommen wurden:
SQL> SELECT *
2 FROM LOHN
3 ORDER BY ART, TARIF DESC;

NAME ART TARIF SEIT TARIF_ALT
-------------------- --- --------- -------- ---------
CHRISSY ZOES GEH 50000 01.08.97
CLODE EVANS GEH 42150 01.10.97
JOHN SMITH GEH 36750 13.12.97 35000
KEVIN TROLLBERG GEH 28875 13.12.97 27500
SANDRA SAMUELS STD 12,5 01.08.97
ROBERT BOBAY STD 11,96 13.12.97 11,5
KEITH JONES STD 10,4 13.12.97 10
SUSAN WILLIAMS STD 9,75 01.12.97

8 Zeilen ausgewählt.

Vier Mitarbeiter haben eine Gehaltserhöhung erhalten. Ein Vergleich mit der Ausgabe der anfänglichen SELECT-Anweisung zeigt die Änderungen. Der aktuelle Tarif spiegelt die Gehaltserhöhung wider. Der alte Tarif wurde in die Spalte TARIF_ALT übertragen und das Datum für das Inkrafttreten des neuen Tarifs (Spalte SEIT) auf das heutige Datum aktualisiert. Bei den nicht für eine Gehaltserhöhung in Frage kommenden Personen hat das Programm keine Änderungen vorgenommen.
Moment mal - es gab noch keine Möglichkeit, die Wirkung der definierten Exception zu sehen. Um den EXCEPTION-Abschnitt zu testen, fügt man eine ungültige Zahlungsweise (Spalte ART) in die Tabelle LOHN ein.
SQL> insert into LOHN values
2 ('JEFF JENNINGS','WOE',71.50,'01.08.97',null);

1 Zeile wurde erstellt.

Nun kommt der Moment der Wahrheit:
SQL> @block2
=======================
Fehler: Programm abgebrochen.
Unbekannte Zahlungsart für: JEFF JENNINGS

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

Die Fehlermeldung weist darauf hin, daß die Zahlungsweise bei JEFF JENNINGS einen Wert enthält, der von GEH oder STD abweicht. Die Exception wurde demnach mit einer Fehlermeldung behandelt.

Gespeicherte Prozeduren, Pakete und Trigger

Mit Hilfe von PL/SQL kann man gespeicherte Objekte erzeugen, um das ständige Eingeben gleichbleibenden Codes zu vermeiden. Prozeduren sind Codeblöcke, die eine bestimmte Aufgabe ausführen. Verwandte Prozeduren lassen sich in einem sogenannten Paket speichern. Ein Trigger ist ein Datenbankobjekt, das bei anderen Transaktionen zum Einsatz kommt. Beispielsweise kann man einen Trigger auf der Tabelle AUFTRAEGE einrichten, der Daten in eine Tabelle PROTOKOLL einfügt, wenn die Tabelle AUFTRAEGE Daten erhält. Die nächsten drei Abschnitte zeigen die grundlegende Syntax dieser Objekte.

PROCEDURE

PACKAGE TRIGGER Das folgende Beispiel fügt über einen Trigger eine Datenzeile in eine Transaktionstabelle ein, wenn die Tabelle LOHN aktualisiert wird. Die Tabelle TRANSAKTION hat folgendes Aussehen:
SQL> describe TRANSAKTION

Name Null? Type
------------------------------- -------- ----
AKTION VARCHAR2(10)
NAME VARCHAR2(20)
TARIF_ALT NUMBER(8,2)
TARIF_AKT NUMBER(8,2)
SEIT DATE

Als Beispiel verwenden wir die folgende Datenzeile:
SQL> select *
2 from LOHN
3 where name = 'JEFF JENNINGS';

NAME ART TARIF SEIT TARIF_ALT
-------------------- --- --------- -------- ---------
JEFF JENNINGS WOE 71,5 01.08.97

1 Zeile wurde ausgewählt.

Erzeugen Sie nun einen Trigger:
SQL> CREATE TRIGGER LOHN_TRIGGER
2 AFTER UPDATE ON LOHN
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO TRANSAKTION VALUES
6 ('ART AEND', :new.name, :old.tarif,
7 :new.tarif, :new.seit);
8 END;
9 /

Trigger wurde erstellt.

Im letzten Schritt aktualisieren wir die Tabelle LOHN, um den Trigger auszulösen.
SQL> update LOHN
2 set TARIF = 15.50,
3 SEIT = sysdate
4 where name = 'JEFF JENNINGS';

1 Zeile wurde aktualisiert.

SQL> select *
2 from LOHN
3 where name = 'JEFF JENNINGS';

NAME ART TARIF SEIT TARIF_ALT
-------------------- --- --------- -------- ---------
JEFF JENNINGS WOE 15,5 13.12.97

1 Zeile wurde ausgewählt.

SQL> select *
2 from TRANSAKTION;

AKTION NAME TARIF_ALT TARIF_AKT SEIT
---------- -------------------- --------- --------- --------
ART AEND JEFF JENNINGS 71,5 15,5 13.12.97

1 Zeile wurde ausgewählt.

In der Tabelle LOHN enthält die Spalte TARIF_ALT den Wert NULL, während in der Tabelle TRANSAKTION in der Spalte TARIF_ALT ein Wert erscheint. Das ist nicht ganz so kompliziert, wie es auf den ersten Blick scheinen mag. Die Tabelle LOHN braucht keinen Eintrag für TARIF_ALT, da ein Stundenlohn von 71.50 offensichtlich einen fehlerhaften Betrag darstellt. Statt dessen haben wir den Wert für TARIF_ALT in die Tabelle TRANSAKTION eingefügt, da die Aktualisierung eine Transaktion darstellt und die Tabelle TRANSAKTION alle Transaktionen bezüglich der Tabelle LOHN protokollieren soll.

Wenn Sie sich in der Netzwerktechnologie auskennen, sind Ihnen sicherlich die Ähnlichkeiten der gespeicherten Prozeduren bei PL/SQL und Java aufgefallen. Es sei aber auf einige Unterschiede hingewiesen. PL/SQL ist eine Erweiterung von Standard-SQL und implementiert die Befehle einer prozeduralen Sprache. Java ist eine modernere Sprache als PL/SQL und erlaubt dem Programmierer, komplexere Programme zu schreiben, als es mit PL/SQL möglich ist. PL/SQL basiert auf der Datenbank-orientierten Funktionalität von SQL; Java zielt mehr auf CPU-intensive Programme ab. Die meisten prozeduralen Sprachen wie zum Beispiel PL/SQL sind speziell auf die jeweilige Plattform ausgerichtet. Die Weiterentwicklung der prozeduralen Sprachen wird ein höheres Niveau der Standardisierung über verschiedene Plattformen hinweg bringen.

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