9. Einführung in PL/SQL • Motivation für PL/SQL • Aufbau von PL

9. Einführung in PL/SQL
•
•
•
•
Motivation für PL/SQL
Aufbau von PL/SQL-Programmen
Alternativen
Steuerung des Programmablaufs
• Records
• Ausnahmebehandlung
Datenbanksysteme
Prof. Dr. Stephan Kleuker
198
Einwurf
• Oracle nutzt Transaktionssteuerung (wie alle echten
Datenbanken)
• Nutzer haben Gefühl, dass sie individuell arbeiten
• Vorstellung: Nutzer arbeiten auf lokalen Kopien
• erst mit Befehl COMMIT; werden Änderungen
endgültig übernommen
• kommen mehrere Nutzer in Konflikt (gleiches Datum
bearbeiten), findet DB Lösung (z. B. ein Nutzer muss
warten)
• Fazit: Nach INSERT-Befehlen soll COMMIT; stehen
Datenbanksysteme
Prof. Dr. Stephan Kleuker
199
Erweiterung relationaler Datenbanken
• Einbettung von SQL in prozedurale oder
objektorientierte Wirtssprachen (embedded SQL);
meistens C, C++, oder Java (JDBC)
• Erweiterung von SQL um prozedurale Elemente
innerhalb der SQL-Umgebung, PL/SQL (Procedural
language extensions to SQL)
• Vorteile von PL/SQL: Bessere Integration der
prozeduralen Elemente in die Datenbank; Nutzung in
Prozeduren, Funktionen und Triggern
Datenbanksysteme
Prof. Dr. Stephan Kleuker
200
Warum Erweiterung von SQL sinnvoll ist
• keine prozeduralen Konzepte in SQL (Schleifen,
Verzweigungen, Variablendeklarationen)
• viele Aufgaben nur umständlich über
Zwischentabellen oder überhaupt nicht in SQL zu
realisieren.
– Transitive Hülle
• Programme repräsentieren anwendungsspezifisches
Wissen, das nicht in der Datenbank enthalten ist
Datenbanksysteme
Prof. Dr. Stephan Kleuker
201
Warum PL/SQL generell interessant ist
• PL/SQL erhöht die Funktionalität und Mächtigkeit
von SQL-Anfragen
• Obwohl PL/SQL Oracle-spezifisch ist, können viele
zentralen Ideen (z.B. Cursor) auch genutzt werden,
wenn sie SQL in andere Programmiersprachen
einbetten (z. B. Transact SQL für MS Server)
• Für Informatiker ist es uninteressant, welche
Programmiersprache sie können, sie müssen aber
die Fähigkeit haben, sich schnell in eine beliebige
Sprache einzuarbeiten; PL/SQL ist ein gutes
Beispiel, diese Fähigkeit zu prüfen
Datenbanksysteme
Prof. Dr. Stephan Kleuker
202
Anmerkungen zu PL/SQL in dieser Veranstaltung
• PL/SQL wird schrittweise erweitert, wir betrachten
nur Basiskonzepte (z.B. zum Erstellen von Triggern)
• PL/SQL ist eine prozedurale Sprache, die sich an
Ada anlehnt (z.B. strenge Typisierung), C-Kenntnisse
helfen aber auch
• PL/SQL hat einen „objekt-basierten“-Ansatz, erlaubt
die Aufteilung der Software in Packages
• In Oracle existieren einige Built-In-Packages, die hier
nur am Rand betrachtet werden
• Achtung: PL/SQL wird kontinuierlich erweitert, ab
und zu klappt nicht das, was einem logisch erscheint
Datenbanksysteme
Prof. Dr. Stephan Kleuker
203
Einschub: SEQUENCE und DUAL
• In Oracle gibt es keine Auto-Inkrement-Funktion, man kann
aber spezielle Zähler (Sequenzen) definieren
• CREATE SEQUENCE mi INCREMENT BY 1;
definiert eine Sequenz mi, die den Startwert 1 hat,
• durch den Aufruf mi.NEXTVAL, wird der Wert von mi zurück
gegeben und um eins erhöht
• Nutzungsbeispiel:
INSERT INTO Angestellte
VALUES(mi.NEXTVAL,‘Meier‘,30);
INSERT INTO Angestellte
VALUES(mi.NEXTVAL,‘Mücke‘,25);
• Will man Informationen über verschiedene Werte von Oracle
erfahren, kann man dazu die Dummy-Tabelle DUAL nutzen
• Beispiel: Anfrage einer Sequenz mit Erhöhung
SELECT mi.NEXTVAL FROM DUAL;
Datenbanksysteme
Prof. Dr. Stephan Kleuker
204
Einstieg ohne DB (Hello World)
CREATE OR REPLACE PROCEDURE Hello IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END Hello;
1. Zeile: Definition einer Prozedur (Parameter im
nächsten Schritt)
2.-4. Zeile: Rumpf der Prozedur mit Ausgabebefehl
Ausführung: EXECUTE Hello;
Datenbanksysteme
Prof. Dr. Stephan Kleuker
205
Anmerkung zur SW-Entwicklung
DBMS_OUTPUT.PUT_LINE('...') ist Prozedur
eines Zusatzpakets DBMS_OUTPUT und kann
bei inhaltlicher Fehlersuche hilfreich sein
Datenbanksysteme
Prof. Dr. Stephan Kleuker
206
Aufbau einer PL/SQL-Prozedur oder Funktion
CREATE OR REPLACE PROCEDURE <PName> (<Parameter>)
IS
<Variablenname> <VariablenTyp>
BEGIN
<PL/SQL-Programmteile und SQL-Anfragen>
EXCEPTION
<Behandlung von Ausnahmen (optionaler Anteil)>
END;
CREATE OR REPLACE FUNCTION <FName> (<Parameter>)
RETURN <Ergebnistyp>
IS
... (wie oben, aber mindestens ein Befehl RETURN <Ergebnis>)
Datenbanksysteme
Prof. Dr. Stephan Kleuker
207
Beispiel-Datenbank
-- einfache Verwaltungstabelle
CREATE TABLE ANGESTELLTE(
Minr NUMBER(5),
Name VARCHAR(10),
Gehalt NUMBER(4),
PRIMARY KEY (MiNr)
);
-- einfache Kontrolltabelle
CREATE TABLE PROTOKOLL(
WER VARCHAR(10),
WAS VARCHAR(10),
WO VARCHAR(15),
WEN VARCHAR(12),
WANN DATE
);
-- Zähler für Minr (ohne Test auf Obergrenze)
CREATE SEQUENCE mi INCREMENT BY 1;
Datenbanksysteme
Prof. Dr. Stephan Kleuker
208
Einfache Einfüge-Prozedur
CREATE OR REPLACE PROCEDURE EIN0
(N IN VARCHAR, G IN NUMBER) IS
BEGIN
INSERT INTO ANGESTELLTE VALUES(mi.NEXTVAL,N,G);
END;
• Prozeduren und Funktionen können Parameter haben
• für Parameter ist die Bearbeitungsart (IN OUT INOUT)
anzugeben, bei Funktionen nur IN (ist default)
• bei VARCHAR und NUMBER-Parametern darf keine Formatangabe
(Länge) übergeben werden
• In SQL-Statements können (einfache, d.h. keine Tabellen)
Variablen referenziert werden
Datenbanksysteme
Prof. Dr. Stephan Kleuker
209
Einfüge-Prozedur mit Protokoll
CREATE OR REPLACE PROCEDURE EIN1
(N IN VARCHAR, G IN NUMBER) IS
mi_Nummer NUMBER;
/* USER_USERS ist Oracle-Tabelle mit Informationen über
den aktuellen Nutzer (Name im Attribut USERNAME) */
NUTZER USER_USERS.USERNAME%TYPE;
BEGIN
SELECT mi.NEXTVAL
INTO mi_Nummer
FROM DUAL;
INSERT INTO ANGESTELLTE VALUES(mi_Nummer,N,G);
SELECT USER_USERS.USERNAME
INTO NUTZER
FROM USER_USERS;
INSERT INTO PROTOKOLL VALUES(NUTZER,'EINFUEGEN',
'ANGESTELLTE',mi_Nummer,SYSDATE);
END;
Datenbanksysteme
Prof. Dr. Stephan Kleuker
210
Ausführung
SELECT * FROM Angestellte;
SELECT * FROM Protokoll;
EXECUTE EIN1('ich',42);
SELECT * FROM Angestellte;
SELECT * FROM Protokoll;
Keine Zeilen gewählt
Keine Zeilen gewählt
anonymer Block abgeschlossen
MINR NAME
GEHALT
----- ---------- -----1 ich
42
WER
WAS
WO
WEN WANN
---------- ---------- ------------ ---- -------SKLEUKER
EINFUEGEN ANGESTELLTE 1
19.11.12
Datenbanksysteme
Prof. Dr. Stephan Kleuker
211
SQL in PL/SQL
• In PL/SQL sind grundsätzlich alle SQL DML-Befehle
(SELECT, INSERT, UPDATE, DELETE) erlaubt
• für DDL-Befehle (CREATE, DROP) muss Zusatzpaket
benutzt werden (hier nicht betrachtet)
• SELECT-Befehle müssen zusätzlich die INTO-Zeile
haben und müssen genau ein Ergebnis liefern
• Mit Tabelle.Spaltenname%TYPE kann direkt auf den
Typen einer Tabellenspalte zugegriffen werden
• Abfragen von Funktionswerten oder Sequenz-Werten
werden immer auf die Dummy-Tabelle DUAL bezogen
• USER_USERS ist eine der Systemtabellen
Datenbanksysteme
Prof. Dr. Stephan Kleuker
212
Alternativen
CREATE TABLE Gehaltsklassen(
INSERT INTO Gehaltsklassen VALUES('A',0);
klasse VARCHAR(1),
INSERT INTO Gehaltsklassen VALUES('B',0);
anzahl NUMBER(3)
INSERT INTO Gehaltsklassen VALUES('C',0);
);
CREATE OR REPLACE PROCEDURE EIN (N IN VARCHAR, G IN NUMBER) IS
BEGIN
INSERT INTO ANGESTELLTE VALUES(mi.NEXTVAL
VALUES(mi.NEXTVAL,N,G);
mi.NEXTVAL,N,G);
IF G>90
THEN UPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='A';
DBMS_OUTPUT.PUT_LINE('In Klasse A eingefuegt');
ELSIF G>60
THEN
BEGIN
UPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='B';
DBMS_OUTPUT.PUT_LINE('In Klasse B eingefuegt');
END;
ELSE
UPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='C';
END IF;
END;
Datenbanksysteme
Prof. Dr. Stephan Kleuker
213
Strukturierung des Ablaufs
• Alternativen werden durch die IF-THEN-ELSIFELSIF-...-ELSE-END IF Konstruktion beschrieben
• Wird eine Bedingung nach „undefiniert“
ausgewertet, wird in den ELSE-Zweig gesprungen
• Grundsätzlich können in PL/SQL-Rümpfen weitere
PL/SQL-Blöcke definiert werden
• weitere Blöcke dienen zur Strukturierung der
Programme und sind hilfreich bei der Reaktion auf
Ausnahmen (Fehlerfälle)
• Es gibt die Anweisung NULL; (steht für den leeren
Befehl)
• Die „üblichen“ Regeln für Sichtbarkeiten von
Variablen werden übernommen (sollten bei guter
Programmierung keine Rolle spielen)
Datenbanksysteme
Prof. Dr. Stephan Kleuker
214
Schleifen (Beispiele)
CREATE OR REPLACE PROCEDURE FREMD0
(Anzahl NUMBER, Firma VARCHAR, Gehalt NUMBER) IS
BEGIN
FOR i IN 1 .. Anzahl
LOOP
EIN(Firma || i, Gehalt);
END LOOP;
END;
CREATE OR REPLACE PROCEDURE FREMD
(Anzahl NUMBER, Firma VARCHAR, Gehalt NUMBER) IS
i INTEGER DEFAULT 1;
BEGIN
WHILE i<=Anzahl
LOOP
EIN(Firma || i, Gehalt);
i:=i+1;
END LOOP;
END;
Datenbanksysteme
Prof. Dr. Stephan Kleuker
215
Übersicht - Schleifen
• Simple LOOP: LOOP ... END LOOP;
• WHILE LOOP:
WHILE <bedingung> LOOP ... END LOOP;
• Numeric FOR LOOP:
FOR <loop_index>
IN [REVERSE] <Anfang> .. <Ende>
LOOP …
END LOOP;
• Die Variable <loop_index> wird dabei automatisch
als INTEGER deklariert.
• EXIT [WHEN <bedingung>]: LOOP Verlassen (ist
aber schlechter Programmierstil)
Datenbanksysteme
Prof. Dr. Stephan Kleuker
216
Zugriffsrechte bei Prozeduren / Funktionen
• Benutzungsrechte vergeben:
GRANT EXECUTE ON <procedure/function>
TO <user>;
• Prozeduren und Funktionen werden jeweils mit den
Zugriffsrechten des Besitzers ausgeführt
• d.h. der Nutzer kann die Prozedur/Funktion auch
dann aufrufen, wenn er kein Zugriffsrecht auf die
dabei benutzten Tabellen hat
• Rechtesteuerung in späteren VL
• Anmerkung: Sieht man PL/SQL als serverseitige
Programmierung, ist dies ein zentrales Argument für
PL/SQL
Datenbanksysteme
Prof. Dr. Stephan Kleuker
217
Records
• Ein RECORD enthält mehrere Felder, entspricht einem Tupel in
der Datenbasis:
TYPE Citytype IS RECORD(
Name City.Name%TYPE,
Country VARCHAR(4),
Province VARCHAR(32),
Population NUMBER,
Longitude NUMBER,
Latitude NUMBER
);
• Nutzung (Deklaration einer Variablen diesen Typs):
theCity Citytype;
• Semantisch verhalten sich RECORD wie struct in C (kein
direkter Vergleich, direkte Zuweisung möglich, bei Zuweisung
werden Werte kopiert)
Datenbanksysteme
Prof. Dr. Stephan Kleuker
218
Etwas Semantikanalyse (1/2)
CREATE OR REPLACE PROCEDURE RecordTest IS
TYPE T1 IS RECORD(
X NUMBER,
Y NUMBER
);
TYPE T2 IS RECORD(
X NUMBER,
Y NUMBER
);
A T1;
Ausgabe:
B T1 DEFAULT A;
A.x= 1
C T2;
A.y= 2
BEGIN
B.x=
A.x:=1;
A.y:=2;
B.y=
-- DBMS_OUTPUT.PUT_LINE(A); geht nicht
DBMS_OUTPUT.PUT_LINE('A.x= '||A.x);
DBMS_OUTPUT.PUT_LINE('A.y= '||A.y);
DBMS_OUTPUT.PUT_LINE('B.x= '||B.x);
DBMS_OUTPUT.PUT_LINE('B.y= '||B.y);
-- DBMS_OUTPUT.PUT_LINE(B.y); liefert leere Zeile!
Datenbanksysteme
Prof. Dr. Stephan Kleuker
219
Etwas Semantikanalyse (2/2)
B.x:=1;
B.y:=2;
-- IF A=B ist verboten
IF A.x=B.x AND A.y=B.y
THEN DBMS_OUTPUT.PUT_LINE('A gleich B');
ELSE DBMS_OUTPUT.PUT_LINE('A ungleich B');
END IF;
A:=B;
B.x:=2;
IF A.x=B.x AND A.y=B.y
THEN DBMS_OUTPUT.PUT_LINE('A gleich B');
ELSE DBMS_OUTPUT.PUT_LINE('A ungleich B');
END IF;
-- nicht erlaubt C:=A;
END;
Datenbanksysteme
Prof. Dr. Stephan Kleuker
Ausgabe:
A gleich B
A ungleich B
220
Ausnahmen (1/5)
• Declaration Section: Deklaration (der Namen)
benutzerdefinierter Exceptions.
[DECLARE] <exception_name> EXCEPTION;
• Exceptions können dann an beliebigen Stellen des
PL/SQL-Blocks durch RAISE ausgelöst werden.
IF <condition>
THEN RAISE <exception_name>;
• Exception Section: Definition der beim Auftreten
einer Exception auszuführenden Aktionen.
WHEN <exception_name>
THEN <PL/SQL-Statement>;
WHEN OTHERS THEN <PL/SQL-Statement>;
Datenbanksysteme
Prof. Dr. Stephan Kleuker
221
Ausnahmen (2/5)
• auslösen einer Exception
• entsprechende Aktion der WHEN-Klausel ausführen
• innersten Block verlassen (oft Anwendung von anonymen
Blöcken sinnvoll)
mit
ohne
ohne
gefangener
gefangene
Exception
Exception
Exception
PROCEDURE
PROCEDURE
PROCEDURE
BEGIN
BEGIN
BEGIN
EXCEPTION
EXCEPTION
EXCEPTION
END
END
END
Datenbanksysteme
Prof. Dr. Stephan Kleuker
222
Ausnahmen (3/5)
CREATE OR REPLACE FUNCTION noHeinz(name VARCHAR)
1
RETURN VARCHAR IS
heinz EXCEPTION; CREATE OR REPLACE PROCEDURE heinzTest IS
BEGIN
heinz EXCEPTION;
IF name='Heinz' BEGIN
2
THEN
DBMS_OUTPUT.PUT_LINE(noHeinz('Egon'));
RAISE heinz;
DBMS_OUTPUT.PUT_LINE(noHeinz('Heinz'));
ELSE
DBMS_OUTPUT.PUT_LINE(noHeinz('Udo'));
RETURN name;
EXCEPTION
END IF;
WHEN heinz THEN
END;
DBMS_OUTPUT.PUT_LINE('Ein Heinz');
/
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Wat nu?');
END;
/
Egon
4!
3
EXECUTE heinzTest;
Wat nu?
SELECT noHeinz('Heinz') FROM DUAL;
SELECT noHeinz('Heinz') FROM DUAL
FEHLER in Zeile 1:
ORA-06510: PL/SQL: Unbehandelte benutzerdefinierte Ausnahmebedingung (exception)
ORA-06512: in „SKLEUKER.NOHEINZ", Zeile 7
Datenbanksysteme
Prof. Dr. Stephan Kleuker
223
Ausnahmen (4/5)
• Es gibt viele vordefinierte Ausnahmen, die alle
abgeprüft und bearbeitet werden können, Beispiele:
– NO_DATA_FOUND
– TOO_MANY_ROWS
– INVALID_CURSOR
– ZERO_DIVIDE
– DUP_VAL_ON_INDEX
• Ausnahmen können auch direkt ausgelöst werden:
RAISE_APPLICATION_ERROR(-20101,
'Kunde nicht kreditwürdig');
• Die Werte dieser Ausnahmen müssen zwischen
-21000 und -20000 liegen, Rest ist für Oracle
Datenbanksysteme
Prof. Dr. Stephan Kleuker
224
Ausnahmen (5/5)
CREATE OR REPLACE PROCEDURE exTest IS
I INTEGER DEFAULT 0;
BEGIN
BEGIN
I:=I/I;
DBMS_OUTPUT.PUT_LINE('Nicht Erreicht');
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE(''||SQLCODE||'::'||SQLERRM);
END;
DBMS_OUTPUT.PUT_LINE(''||SQLCODE||'::'||SQLERRM);
RAISE_APPLICATION_ERROR(-20101,'keine Lust mehr');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(''||SQLCODE||'::'||SQLERRM);
IF SQLCODE=-20101
THEN
DBMS_OUTPUT.PUT_LINE('stimmt nicht');
END IF;
-1476::ORA-01476: Divisor ist Null
END;
0::ORA-0000: normal, successful completion
/
-20101::ORA-20101: keine Lust mehr
EXECUTE exTest;
stimmt nicht
Datenbanksysteme
Prof. Dr. Stephan Kleuker
225
10. Cursor und Trigger in PL/SQL
• Definition eines Cursor
• Einsatzmöglichkeiten von Cursorn
• Verschiedene Arten von Triggern
Hinweis: Oracle-Bibliothek (als eine Sammlung
herunterladbar)
Datenbanksysteme
Prof. Dr. Stephan Kleuker
226
RECORD und %ROWTYPE
CREATE OR REPLACE PROCEDURE gehaltAnpassen (
mi Angestellte.MiNr%TYPE) IS
person Angestellte%ROWTYPE;
BEGIN
SELECT *
CREATE TABLE ANGESTELLTE(
INTO person
Minr NUMBER(5),
FROM Angestellte
Name VARCHAR(10),
WHERE Angestellte.MiNr=mi;
Gehalt NUMBER(4),
UPDATE Angestellte
PRIMARY KEY (MiNr)
SET Gehalt=person.Gehalt
);
WHERE Name=person.Name;
END;
Mit %ROWTYPE erhält man einen RECORD-Typen, der sich genau
aus den Attributen der genannten Tabelle zusammensetzt. Variablen
dieses Typs können dann eine Zeile der Tabelle aufnehmen
Datenbanksysteme
Prof. Dr. Stephan Kleuker
227
Cursorbasierter DB-Zugriff
• Zeilenweiser Zugriff auf eine Relation aus einem
PL/SQL-Programm.
• Cursordeklaration in der Declaration Section:
CURSOR <cursor-name> [(<parameter-list>)]
IS
<select-statement>;
• (<parameter-list>): Parameter-Liste, nur IN als
Übergaberichtung erlaubt
• Zwischen SELECT und FROM auch PL/SQL-Variablen
und PL/SQL-Funktionen. PL/SQL-Variablen können
ebenfalls in den WHERE-, GROUP- und HAVINGKlauseln verwendet werden
Datenbanksysteme
Prof. Dr. Stephan Kleuker
228
Beispiel für Cursor-Definition
Alle Städte in dem in der Variablen theCountry
angegebenen Land:
CURSOR cities_in(theCountry Country.Code%TYPE)
IS
SELECT City.Name
FROM City
WHERE City.Country=theCountry;
Erinnerung:
Tabelle City (vereinfacht):
Datenbanksysteme
Country
Name
D
Cologne
D
Hamburg
Prof. Dr. Stephan Kleuker
229
OPEN
OPEN <cursor-name> [(<argument-list>)];
•
•
•
•
das SELECT-Statement des CURSORS wird jetzt ausgeführt
das Ergebnis ist eine virtuelle Tabelle im Speicher
auf diese Tabelle kann nur zeilenweise zugegriffen werden
die aktuelle Zeile wird durch ein FETCH-Kommando eingelesen,
wobei der CURSOR automatisch ein Feld weiter gesetzt wird
• OPEN cities_in('D');
Name
Cologne
= virtuell im Speicher
= zugreifbar
Datenbanksysteme
FETCH
Hamburg
Munich
Prof. Dr. Stephan Kleuker
230
FETCH
• FETCH <cursor-name>
INTO <record-variable>;
oder
FETCH <cursor-name> INTO <variable-list>;
• bewegt den Cursor auf die nächste Zeile des Ergebnisses der
Anfrage und kopiert diese in die angegebene Record-Variable
oder Variablenliste.
• Diese wird kann z.B. mit <cursor-name>%ROWTYPE mit dem
Record-Typ des Cursors definiert werden:
<variable> <cursor-name>%ROWTYPE;
• CLOSE <cursor-name>; schließt einen Cursor.
• nicht möglich:
OPEN cities_in ('D');
OPEN cities_in ('CH');
FETCH cities_in INTO <variable>;
ein parametrisierter Cursor, nicht eine Familie von Cursoren
Datenbanksysteme
Prof. Dr. Stephan Kleuker
231
Attribute von Cursorn
• <cursor-name>%ISOPEN : Cursor offen?
• <cursor-name>%FOUND : Solange ein Cursor bei der
letzten FETCH-Operation ein neues Tupel gefunden
hat, ist <cursor-name>%FOUND = TRUE.
• <cursor-name>%NOTFOUND: TRUE wenn man alle
Zeilen eines Cursors geFETCHt hat.
• <cursor-name>%ROWCOUNT : Anzahl der von einem
Cursor bereits gelesenen Tupel.
• Attribute nicht innerhalb eines SQL-Ausdrucks
verwendbar (gehören zu
Ablaufsteuerungsmöglichkeiten in PL/SQL)
Datenbanksysteme
Prof. Dr. Stephan Kleuker
232
Cursor – Beispiel (1/2)
-- Nutzung von WHILE
CREATE OR REPLACE PROCEDURE teureLeute (betr Number) IS
CURSOR viel (betrag Number) IS
SELECT *
FROM Angestellte
WHERE Angestellte.Gehalt>=betrag;
zeile viel%ROWTYPE;
BEGIN
OPEN viel(betr);
FETCH viel INTO zeile;
WHILE viel%FOUND
LOOP
DBMS_OUTPUT.PUT_LINE(zeile.Name||' bekommt zu
viel! (mindestens '||betr||')');
FETCH viel INTO zeile;
END LOOP;
CLOSE viel;
END;
Datenbanksysteme
Prof. Dr. Stephan Kleuker
233
Cursor FOR LOOP
• FOR <record_index> IN <cursor-name>
LOOP ... END LOOP;
• <record_index> wird dabei automatisch als
Variable vom Typ <cursor-name>%ROWTYPE
deklariert,
• <record_index> immer von einem Record-Type –
ggf. einspaltig (bei Zugriff beachten).
• es wird automatisch ein OPEN ausgeführt
• bei jeder Ausführung des Schleifenkörpers wird
automatisch ein FETCH ausgeführt,
• Schleifenkörper enthält i.a. keinen FETCH-Befehl
• am Ende wird automatisch ein CLOSE ausgeführt
• Spalten müssen explizit adressiert werden
Datenbanksysteme
Prof. Dr. Stephan Kleuker
234
Cursor – Beispiel (2/2)
-- Nutzung der CURSOR-FOR-Schleife
CREATE OR REPLACE PROCEDURE teureLeute0(betr Number)
IS
CURSOR viel (betrag Number) IS
SELECT *
FROM Angestellte
WHERE Angestellte.Gehalt>=betrag;
BEGIN
FOR person IN viel(betr)
LOOP
DBMS_OUTPUT.PUT_LINE(person.Name||' verdient
zu viel! (mindestens '||betr||')');
END LOOP;
END;
Datenbanksysteme
Prof. Dr. Stephan Kleuker
235
Aufdatieren an CURSOR-Position
CREATE OR REPLACE Procedure mehrFuerAlteLeute IS
CURSOR alt IS
SELECT *
FROM Angestellte
• Um Veränderungen an der
WHERE MiNr<100
aktuellen CURSOR-Position
FOR UPDATE;
vornehmen zu können, kann
BEGIN
die Bedingung WHERE
FOR person IN alt
CURRENT OF <CURSORLOOP
Name> genutzt werden,
UPDATE Angestellte
CURSOR muss zum ändern
SET Gehalt=Gehalt*1.1
markiert sein
WHERE CURRENT OF alt;
END LOOP;
• Beispiel: Gehaltserhöhung
END;
um 10% bei allen Leuten
deren MiNr kleiner als 100 ist
Datenbanksysteme
Prof. Dr. Stephan Kleuker
236
Trigger (1/2)
• spezielle Form von PL/SQL-Prozeduren
• werden beim Eintreten eines bestimmten
Ereignisses ausgeführt
• Spezialfall aktiver Regeln nach dem Event-ConditionAction-Paradigma
• Werden einer Tabelle (oft auch noch einer
bestimmten Spalte) zugeordnet
• Bearbeitung wird durch das Eintreten eines
Ereignisses (Einfügen, Ändern oder Löschen von
Zeilen der Tabelle) ausgelöst (Event)
Datenbanksysteme
Prof. Dr. Stephan Kleuker
237
Trigger (2/2)
• Ausführung von Bedingungen an den
Datenbankzustand abhängig (Condition).
• Action:
vor oder nach der Ausführung der entsprechenden
aktivierenden Anweisung ausgeführt.
• einmal pro auslösender Anweisung (StatementTrigger) oder einmal für jede betroffene Zeile (RowTrigger) ausgeführt.
• Trigger-Aktion kann auf den alten und neuen Wert
der gerade behandelten Zeile zugreifen.
Datenbanksysteme
Prof. Dr. Stephan Kleuker
238
Syntax von Triggern (1/2)
CREATE [OR REPLACE] TRIGGER <trigger-name>
BEFORE | AFTER
{INSERT | DELETE | UPDATE} [OF <column-list>]
[ OR {INSERT | DELETE | UPDATE}
[OF <column-list>]]
...
[ OR {INSERT | DELETE | UPDATE}
[OF <column-list>]]
ON <table>
[FOR EACH ROW]
[WHEN (<condition>)]
<pl/sql-block>;
Datenbanksysteme
Prof. Dr. Stephan Kleuker
239
Syntax von Triggern (2/2)
• BEFORE, AFTER: Trigger wird vor/nach der
auslösenden Operation ausgeführt
• OF <column> (nur für UPDATE) schränkt Aktivierung
auf angegebene Spalte ein
• Zugriff auf Zeileninhalte vor und nach der
Ausführung der aktivierenden Aktion mittels :OLD
bzw. :NEW. (Aliasing durch REFERENCING OLD AS
... NEW AS ... ).
• Schreiben in :NEW -Werte nur mit BEFORE-Trigger.
• FOR EACH ROW: Row-Trigger, sonst StatementTrigger.
• WHEN (<condition>) : zusätzliche Bedingung :OLD
und :NEW sind in <condition> erlaubt.
Datenbanksysteme
Prof. Dr. Stephan Kleuker
240
Beispiel für Trigger (1/5)
• Wenn ein Landes-Code geändert wird, pflanzt sich
diese Änderung auf die Relation Province fort:
CREATE OR REPLACE TRIGGER change_Code
BEFORE UPDATE OF Code
ON Country
FOR EACH ROW
BEGIN
UPDATE Province
SET Country = :NEW.Code
WHERE Country = :OLD.Code;
END;
Datenbanksysteme
Prof. Dr. Stephan Kleuker
241
Beispiel für Trigger (2/5)
• Wenn ein Land neu angelegt wird, wird ein Eintrag in Politics
mit dem aktuellen Jahr erzeugt:
CREATE TRIGGER new_Country
AFTER INSERT
ON Country
FOR EACH ROW
BEGIN
INSERT INTO Politics (Country,Independence)
VALUES (:NEW.Code,SYSDATE);
END;
• Hinweis: Eventuell benötigte Variablen werden zwischen „FOR
EACH ROW“ und BEGIN nach dem Schlüsselwort DECLARE
definiert
Datenbanksysteme
Prof. Dr. Stephan Kleuker
242
Beispiel für Trigger (3/5)
• Nachmittags dürfen keine Städte gelöscht werden:
CREATE OR REPLACE TRIGGER nachm_nicht_loeschen
BEFORE DELETE
ON City
BEGIN
IF TO_CHAR(SYSDATE,'HH24:MI')
BETWEEN '12:00' AND '18:00'
THEN RAISE_APPLICATION_ERROR
(-20101,'Unerlaubte Aktion');
END IF;
END;
Mit RAISE_APPLICATION_ERROR wird ein Fehler
erzeugt, der zum Abbruch führt. Der erste Parameter
muss zwischen -21000 und -20000 liegen, der zweite
ist der Fehlertext
Datenbanksysteme
Prof. Dr. Stephan Kleuker
243
Beispiel für Trigger (4/5)
Niemand darf anfänglich mehr als die „Meiers“ in der Firma verdienen
CREATE OR REPLACE TRIGGER nichtMehrAlsMeiers
BEFORE INSERT
ON Angestellte
FOR EACH ROW
DECLARE
maxMeier NUMBER;
BEGIN
SELECT MAX(Gehalt)
INTO maxMeier
FROM Angestellte
WHERE Name='Meier';
IF :NEW.Gehalt>maxMeier
THEN
RAISE_APPLICATION_ERROR(-20111,
'Nicht mehr als die Meiers');
END IF;
END;
Datenbanksysteme
Prof. Dr. Stephan Kleuker
244
Beispiel für Trigger (5/5)
Niemand darf anfänglich mehr als die „Meiers“ in der Firma verdienen
CREATE OR REPLACE TRIGGER nichtMehrAlsMeiers
AFTER INSERT
ON Angestellte
DECLARE
verboten INTEGER;
BEGIN
SELECT COUNT(*)
INTO verboten
FROM Angestellte
WHERE Angestellte.Gehalt > (SELECT MAX(Gehalt)
FROM Angestellte
WHERE Name='Meier');
IF verboten > 0
THEN
RAISE_APPLICATION_ERROR(-20111,
'Nicht mehr als die Meiers');
END IF;
END;
Datenbanksysteme
Prof. Dr. Stephan Kleuker
245
FOR EACH ROW oder nicht
CREATE TABLE Tr(
X NUMBER,
Y NUMBER
);
INSERT INTO Tr VALUES (1,3);
INSERT INTO Tr VALUES (1,4);
INSERT INTO Tr VALUES (1,5);
SELECT * FROM Tr;
CREATE TRIGGER TrOhneEach
BEFORE UPDATE ON Tr
BEGIN
DBMS_OUTPUT.PUT_LINE('TrOhneEach');
END;
/
CREATE TRIGGER TrMitEach
BEFORE UPDATE ON Tr
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('TrMitEach');
END;
/
UPDATE TR
SET Y=Y+1
WHERE X=1;
SELECT * FROM Tr;
Datenbanksysteme
X
Y
---------- ---------1
3
1
4
1
5
3 Zeilen ausgewählt.
Trigger wurde erstellt.
Trigger wurde erstellt.
TrOhneEach
TrMitEach
TrMitEach
TrMitEach
3 Zeilen wurden aktualisiert.
X
Y
---------- ---------1
4
1
5
1
6
3 Zeilen ausgewählt.
Prof. Dr. Stephan Kleuker
246
Problem der Mutating Tables (1/2)
•
•
•
•
Trigger können die Inhalte von Tabellen ändern (der
Tabelle, auf der sie definiert sind und andere),
d.h. jede Ausführung des Triggers sieht eventuell
einen anderen Datenbestand der Tabelle, auf der er
definiert ist, sowie der Tabellen, die er evtl. ändert
d.h. Ergebnis abhängig von der Reihenfolge der
veränderten Tupel
ORACLE: Betroffene Tabellen werden während der
gesamten Aktion als „mutating“ gekennzeichnet,
können nicht erneut von Triggern gelesen oder
geschrieben werden
Nachteil: Oft ein zu strenges Kriterium
Datenbanksysteme
Prof. Dr. Stephan Kleuker
247
Problem der Mutating Tables (2/2)
• Trigger soll auf Tabelle zugreifen, auf der er selber
definiert ist
– nur das auslösende Tupel soll von dem Trigger
gelesen/geschrieben werden: Verwendung eines
BEFORE-Triggers und der :NEW - und :OLD Variablen
– es sollen neben dem auslösenden Tupel auch
weitere Tupel verändert werden: Verwendung
eines Statement-orientierten Triggers
• Trigger soll auf andere Tabellen zugreifen:
Verwendung von Statement-Triggern und ggf.
Hilfstabellen
Datenbanksysteme
Prof. Dr. Stephan Kleuker
248
Beispiel (1/4): Aufgabe
• Tabelle speichert Gebote eines Mitglieds (mnr) für
eine Ware (ware) als Preis (gebot)
• Forderung: bei neuen Geboten (insert oder update
erlaubt) für die gleiche Ware muss das Gebot erhöht
werden
CREATE TABLE Gebot(
mnr INTEGER,
ware INTEGER,
gebot NUMBER(8,2),
PRIMARY KEY(mnr,ware,gebot)
);
Datenbanksysteme
Prof. Dr. Stephan Kleuker
249
Beispiel (2/4) : Trigger
CREATE OR REPLACE TRIGGER GEBOTERHOEHEN
BEFORE INSERT OR UPDATE ON GEBOT
FOR EACH ROW
DECLARE
maxi Gebot.gebot%TYPE;
BEGIN
SELECT MAX (Gebot.gebot)
INTO maxi
FROM Gebot
WHERE Gebot.mnr = :NEW.mnr
AND Gebot.ware = :NEW.ware;
IF maxi IS NOT NULL AND maxi >= :NEW.Gebot
THEN
RAISE_APPLICATION_ERROR(-20900
,'Gebot muss erhoeht werden');
END IF;
END;
Datenbanksysteme
Prof. Dr. Stephan Kleuker
250
Beispiel (3/4): Versuch der Anwendung
INSERT INTO Gebot VALUES(42,99,1.00);
1 Zeilen eingefügt.
COMMIT;
festgeschrieben.
UPDATE Gebot SET gebot = 1.01
WHERE mnr = 42 AND ware = 99;
SQL-Fehler: ORA-04091: table ICH.GEBOT is mutating,
trigger/function may not see it
ORA-06512: at "ICH.GEBOTERHOEHEN", line 4
ORA-04088: error during execution of trigger
'ICH.GEBOTERHOEHEN'
04091. 00000 - "table %s.%s is mutating,
trigger/function may not see it"
Datenbanksysteme
Prof. Dr. Stephan Kleuker
251
Beispiel (4/4): Korrektur
CREATE OR REPLACE TRIGGER GEBOTERHOEHEN
BEFORE INSERT OR UPDATE ON GEBOT
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
maxi Gebot.gebot%TYPE;
• Korrektur sinnvoll, da nur ursprüngliche Daten
gelesen und sonst nichts verändert wird
• auch PRAGMA kann zur Laufzeit scheitern
• Erinnerung: Transaktionen mit COMMIT abschließen
Datenbanksysteme
Prof. Dr. Stephan Kleuker
252