Folien ab 20.11.13

9. Einführung in PL/SQL
•
•
•
•
Motivation für PL/SQL
Aufbau von PL/SQL-Programmen
Alternativen
Steuerung des Programmablaufs
• Records
• Ausnahmebehandlung
Datenbanken
Prof. Dr.
Stephan Kleuker
215
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
Datenbanken
Prof. Dr.
Stephan Kleuker
216
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
Datenbanken
Prof. Dr.
Stephan Kleuker
217
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
Datenbanken
Prof. Dr.
Stephan Kleuker
218
Warum PL/SQL generell interessant ist
• PL/SQL erhöht die Funktionalität und Mächtigkeit von SQLAnfragen
• 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
Datenbanken
Prof. Dr.
Stephan Kleuker
219
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
Datenbanken
Prof. Dr.
Stephan Kleuker
220
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 und Inkrement 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 (ohne mi.currval)
SELECT mi.NEXTVAL FROM DUAL;
Datenbanken
Prof. Dr.
Stephan Kleuker
221
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;
Datenbanken
Prof. Dr.
Stephan Kleuker
222
Anmerkung zur SW-Entwicklung
DBMS_OUTPUT.PUT_LINE('...') ist Prozedur
eines Zusatzpakets DBMS_OUTPUT und kann bei
inhaltlicher Fehlersuche hilfreich sein
beachten: http://home.edvsz.hsosnabrueck.de/skleuker/querschnittlich/Datenbankwerkzeuge.pdf
Datenbanken
Prof. Dr.
Stephan Kleuker
223
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>)
Datenbanken
Prof. Dr.
Stephan Kleuker
224
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;
Datenbanken
Prof. Dr.
Stephan Kleuker
225
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
Datenbanken
Prof. Dr.
Stephan Kleuker
226
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;
Datenbanken
Prof. Dr.
Stephan Kleuker
227
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
WO
WAS
WEN WANN
---------- ---------- ------------ ---- -------SKLEUKER EINFUEGEN ANGESTELLTE 1
19.11.12
Datenbanken
Prof. Dr.
Stephan Kleuker
228
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
Datenbanken
Prof. Dr.
Stephan Kleuker
229
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
anzahl=anzahl+1 WHERE klasse='A';
DBMS_OUTPUT.PUT_LINE('In Klasse A eingefuegt');
eingefuegt');
ELSIF G>60
THEN
BEGIN
UPDATE Gehaltsklassen SET anzahl=anzahl+1
anzahl=anzahl+1 WHERE klasse='B';
DBMS_OUTPUT.PUT_LINE('In Klasse B eingefuegt');
eingefuegt');
END;
ELSE
UPDATE Gehaltsklassen SET anzahl=anzahl+1
anzahl=anzahl+1 WHERE klasse='C';
END IF;
END;
Datenbanken
Prof. Dr.
Stephan Kleuker
230
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/SQLBlö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)
Datenbanken
Prof. Dr.
Stephan Kleuker
231
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;
Datenbanken
Prof. Dr.
Stephan Kleuker
232
Ü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)
Datenbanken
Prof. Dr.
Stephan Kleuker
233
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
Datenbanken
Prof. Dr.
Stephan Kleuker
234
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)
Datenbanken
Prof. Dr.
Stephan Kleuker
235
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;
B T1 DEFAULT A;
C T2;
BEGIN
A.x:=1;
Ausgabe:
A.y:=2;
A.x= 1
-- DBMS_OUTPUT.PUT_LINE(A); geht nicht
DBMS_OUTPUT.PUT_LINE('A.x= '||A.x);
A.y= 2
DBMS_OUTPUT.PUT_LINE('A.y= '||A.y);
B.x=
DBMS_OUTPUT.PUT_LINE('B.x= '||B.x);
B.y=
DBMS_OUTPUT.PUT_LINE('B.y= '||B.y);
-- DBMS_OUTPUT.PUT_LINE(B.y); liefert leere Zeile!
Datenbanken
Prof. Dr.
Stephan Kleuker
236
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;
Ausgabe:
A gleich B
-- nicht erlaubt C:=A;
A ungleich B
END;
Datenbanken
Prof. Dr.
Stephan Kleuker
237
Ausnahmen (1/5)
• Declaration Section: Deklaration (der Namen)
benutzerdefinierter Exceptions.
[DECLARE] <exception_name> EXCEPTION;
• Exceptions können dann an beliebigen Stellen des PL/SQLBlocks 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>;
Datenbanken
Prof. Dr.
Stephan Kleuker
238
Ausnahmen (2/5)
•auslösen einer Exception
•entsprechende Aktion der WHEN-Klausel ausführen
•innersten Block verlassen (oft Anwendung von anonymen
Blöcken sinnvoll)
ohne
Exception
mit
gefangener
Exception
ohne
gefangene
Exception
PROCEDURE
PROCEDURE
PROCEDURE
BEGIN
BEGIN
BEGIN
EXCEPTION
EXCEPTION
EXCEPTION
END
END
END
Datenbanken
Prof. Dr.
Stephan Kleuker
239
Ausnahmen (3/5)
CREATE OR REPLACE FUNCTION noHeinz(name VARCHAR)
RETURN VARCHAR IS
1
heinz EXCEPTION;
BEGIN
CREATE OR REPLACE PROCEDURE heinzTest IS
IF name='Heinz' heinz EXCEPTION;
THEN
BEGIN
2
RAISE heinz;
DBMS_OUTPUT.PUT_LINE(noHeinz('Egon'));
ELSE
DBMS_OUTPUT.PUT_LINE(noHeinz('Heinz'));
RETURN name;
DBMS_OUTPUT.PUT_LINE(noHeinz('Udo'));
END IF;
EXCEPTION
END;
WHEN heinz THEN
/
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')
noHeinz('Heinz') FROM DUAL
FEHLER in Zeile 1:
ORAORA-06510: PL/SQL: Unbehandelte benutzerdefinierte Ausnahmebedingung
(exception)
exception) ORAORA-06512:
06512: in „SKLEUKER.NOHEINZ", Zeile 7
Datenbanken
Prof. Dr.
Stephan Kleuker
240
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
Datenbanken
Prof. Dr.
Stephan Kleuker
241
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::ORA1476::ORA-01476: Divisor ist Null
END;
0::ORA0::ORA-0000: normal, successful completion
/
-20101::ORA20101::ORA-20101: keine Lust mehr
EXECUTE exTest;
stimmt nicht
Datenbanken
Prof. Dr.
Stephan Kleuker
242
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)
Datenbanken
Prof. Dr.
Stephan Kleuker
243
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
Datenbanken
Prof. Dr.
Stephan Kleuker
244
Cursorbasierter DB-Zugriff
• Zeilenweiser Zugriff auf eine Relation aus einem PL/SQLProgramm
• 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 HAVING-Klauseln verwendet
werden
Datenbanken
Prof. Dr.
Stephan Kleuker
245
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):
Datenbanken
Country
Name
D
Cologne
D
Hamburg
Prof. Dr.
Stephan Kleuker
246
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
Name
OPEN cities_in('D');
= virtuell im Speicher
= zugreifbar
Datenbanken
Cologne
FETCH
Hamburg
Munich
Prof. Dr.
Stephan Kleuker
247
FETCH
FETCH <cursor-name>
INTO <record-variable>;
oder
FETCH <cursor-name> INTO <variable-list>;
• bewegt Cursor auf nächste Zeile des Ergebnisses der Anfrage
und kopiert diese in die angegebene Record-Variable oder
Variablenliste
• mit <cursor-name>%ROWTYPE kann auf Record-Typ
zugegriffen werden:
<variable> <cursor-name>%ROWTYPE;
• CLOSE <cursor-name>; schließt einen Cursor (wichtig !!!)
• nicht möglich:
OPEN cities_in ('D');
OPEN cities_in ('CH');
FETCH cities_in INTO <variable>;
ein parametrisierter Cursor, nicht eine Familie von Cursoren
Datenbanken
Prof. Dr.
Stephan Kleuker
248
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)
Datenbanken
Prof. Dr.
Stephan Kleuker
249
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;
Datenbanken
Prof. Dr.
Stephan Kleuker
250
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
Datenbanken
Prof. Dr.
Stephan Kleuker
251
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;
Datenbanken
Prof. Dr.
Stephan Kleuker
252
Aufdatieren an CURSOR-Position
CREATE OR REPLACE Procedure mehrFuerAlteLeute IS
CURSOR alt IS
• Um Veränderungen an der
SELECT *
aktuellen CURSOR-Position
FROM Angestellte
WHERE MiNr<100
vornehmen zu können, kann
FOR UPDATE;
die Bedingung WHERE
BEGIN
CURRENT OF <CURSORFOR person IN alt
Name> genutzt werden,
LOOP
CURSOR muss zum ändern
UPDATE Angestellte
SET Gehalt=Gehalt*1.1
markiert sein
WHERE CURRENT OF alt;
• Beispiel: Gehaltserhöhung
END LOOP;
um 10% bei allen Leuten
END;
deren MiNr kleiner als 100
ist
Datenbanken
Prof. Dr.
Stephan Kleuker
253
Trigger (1/2)
• spezielle Form von PL/SQL-Prozeduren
• werden beim Eintreten eines bestimmten Ereignisses
ausgeführt
• Spezialfall aktiver Regeln nach dem Event-Condition-ActionParadigma
• 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)
Datenbanken
Prof. Dr.
Stephan Kleuker
254
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 (Statement-Trigger)
oder einmal für jede betroffene Zeile (Row-Trigger)
ausgeführt
• Trigger-Aktion kann auf den alten und neuen Wert der
gerade behandelten Zeile zugreifen
Datenbanken
Prof. Dr.
Stephan Kleuker
255
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>;
Datenbanken
Prof. Dr.
Stephan Kleuker
256
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 Statement-Trigger.
WHEN (<condition>) : zusätzliche Bedingung :OLD und
:NEW sind in <condition> erlaubt.
Datenbanken
Prof. Dr.
Stephan Kleuker
257
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;
Datenbanken
Prof. Dr.
Stephan Kleuker
258
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
Datenbanken
Prof. Dr.
Stephan Kleuker
259
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
Datenbanken
Prof. Dr.
Stephan Kleuker
260
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;
Datenbanken
Prof. Dr.
Stephan Kleuker
261
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
nicht äquivalent zu vorher:
ON Angestellte
DECLARE
- was wenn neuer
verboten INTEGER;
Mitarbeiter Meier heißt
BEGIN
- was bei update dann insert
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;
Datenbanken
Prof. Dr.
Stephan Kleuker
262
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;
Datenbanken
Prof. Dr.
Stephan Kleuker
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.
263
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
Datenbanken
Prof. Dr.
Stephan Kleuker
264
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 Statementorientierten Triggers
• Trigger soll auf andere Tabellen zugreifen: Verwendung von
Statement-Triggern und ggf. Hilfstabellen
Datenbanken
Prof. Dr.
Stephan Kleuker
265
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)
);
Datenbanken
Prof. Dr.
Stephan Kleuker
266
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;
Datenbanken
Prof. Dr.
Stephan Kleuker
267
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"
Datenbanken
Prof. Dr.
Stephan Kleuker
268
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
Datenbanken
Prof. Dr.
Stephan Kleuker
269