Aufbau von PL/SQL-Programmen • Steuerung des...

55
Prof. Dr. Stephan Kleuker 221 Datenbanken 9. Einführung in PL/SQL Motivation für PL/SQL Aufbau von PL/SQL-Programmen • Alternativen Steuerung des Programmablaufs • Records • Ausnahmebehandlung

Transcript of Aufbau von PL/SQL-Programmen • Steuerung des...

Page 1: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

221Datenbanken

9. Einführung in PL/SQL

• Motivation für PL/SQL

• Aufbau von PL/SQL-Programmen

• Alternativen

• Steuerung des Programmablaufs

• Records

• Ausnahmebehandlung

Page 2: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

222Datenbanken

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

Page 3: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

223Datenbanken

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

Page 4: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

224Datenbanken

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

Page 5: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

225Datenbanken

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

Page 6: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

226Datenbanken

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

Page 7: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

227Datenbanken

Einschub: SEQUENCE und DUAL• In Oracle gibt es keine Auto-Inkrement-Funktion, man kann aber

spezielle Zähler (Sequenzen) definierenCREATE 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 DUALnutzen

• Beispiel: Anfrage einer Sequenz mit Erhöhung (ohne mi.currval )SELECT mi.NEXTVAL FROM DUAL;

Page 8: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

228Datenbanken

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;

Page 9: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

229Datenbanken

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.hs-osnabrueck.de/skleuker/querschnittlich/Datenbankwerkzeuge.pdf

Page 10: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

230Datenbanken

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

Page 11: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

231Datenbanken

Beispiel-Datenbank-- einfache VerwaltungstabelleCREATE TABLE ANGESTELLTE(

Minr NUMBER(5),Name VARCHAR(10),Gehalt NUMBER(4),PRIMARY KEY (MiNr)

);-- einfache KontrolltabelleCREATE 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;

Page 12: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

232Datenbanken

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 VARCHARund NUMBER-Parametern darf keine

Formatangabe (Länge) übergeben werden

• In SQL-Statements können (einfache, d.h. keine Tabellen) Variablen referenziert werden

Page 13: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

233Datenbanken

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;

BEGINSELECT mi.NEXTVAL

INTO mi_NummerFROM DUAL;

INSERT INTO ANGESTELLTE VALUES(mi_Nummer,N,G);SELECT USER_USERS.USERNAME

INTO NUTZERFROM USER_USERS;

INSERT INTO PROTOKOLL VALUES(NUTZER,'EINFUEGEN', 'ANGESTELLTE',mi_Nummer,SYSDATE);

END;

Page 14: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

234

Ausführung

SELECT * FROM Angestellte;

SELECT * FROM Protokoll;

EXECUTE EIN1('ich',42);

SELECT * FROM Angestellte;

SELECT * FROM Protokoll;

Datenbanken

Keine Zeilen gewähltKeine Zeilen gewähltKeine Zeilen gewähltKeine Zeilen gewähltKeine Zeilen gewähltKeine Zeilen gewähltKeine Zeilen gewähltKeine Zeilen gewähltanonymer Block abgeschlossenanonymer Block abgeschlossenanonymer Block abgeschlossenanonymer Block abgeschlossenMINR NAME GEHALTMINR NAME GEHALTMINR NAME GEHALTMINR NAME GEHALT

-------------------- ---------------------------------------- ------------------------1 ich 42 1 ich 42 1 ich 42 1 ich 42

WER WAS WO WEN WANN WER WAS WO WEN WANN WER WAS WO WEN WANN WER WAS WO WEN WANN ---------------------------------------- ---------------------------------------- ------------------------------------------------ ---------------- --------------------------------SKLEUKER EINFUEGEN ANGESTELLTE 1 19.11.12 SKLEUKER EINFUEGEN ANGESTELLTE 1 19.11.12 SKLEUKER EINFUEGEN ANGESTELLTE 1 19.11.12 SKLEUKER EINFUEGEN ANGESTELLTE 1 19.11.12

Page 15: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

235Datenbanken

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%TYPEkann direkt auf den Typen

einer Tabellenspalte zugegriffen werden

• Abfragen von Funktionswerten oder Sequenz-Werten werden immer auf die Dummy-Tabelle DUALbezogen

• USER_USERSist eine der Systemtabellen

Page 16: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

236Datenbanken

AlternativenCREATE TABLE Gehaltsklassen(CREATE TABLE Gehaltsklassen(CREATE TABLE Gehaltsklassen(CREATE TABLE Gehaltsklassen(klasse VARCHAR(1),klasse VARCHAR(1),klasse VARCHAR(1),klasse VARCHAR(1),anzahlanzahlanzahlanzahl NUMBER(3)NUMBER(3)NUMBER(3)NUMBER(3)

););););CREATE OR REPLACE PROCEDURE EIN (N IN VARCHAR, G IN NUMBER) IS CREATE OR REPLACE PROCEDURE EIN (N IN VARCHAR, G IN NUMBER) IS CREATE OR REPLACE PROCEDURE EIN (N IN VARCHAR, G IN NUMBER) IS CREATE OR REPLACE PROCEDURE EIN (N IN VARCHAR, G IN NUMBER) IS BEGINBEGINBEGINBEGININSERT INTO ANGESTELLTE VALUES(INSERT INTO ANGESTELLTE VALUES(INSERT INTO ANGESTELLTE VALUES(INSERT INTO ANGESTELLTE VALUES(mi.NEXTVALmi.NEXTVALmi.NEXTVALmi.NEXTVAL,N,G);,N,G);,N,G);,N,G);IF G>90IF G>90IF G>90IF G>90THEN UPDATE Gehaltsklassen SET THEN UPDATE Gehaltsklassen SET THEN UPDATE Gehaltsklassen SET THEN UPDATE Gehaltsklassen SET anzahlanzahlanzahlanzahl=anzahl+1 WHERE klasse='A';=anzahl+1 WHERE klasse='A';=anzahl+1 WHERE klasse='A';=anzahl+1 WHERE klasse='A';

DBMS_OUTPUT.PUT_LINE('In Klasse A DBMS_OUTPUT.PUT_LINE('In Klasse A DBMS_OUTPUT.PUT_LINE('In Klasse A DBMS_OUTPUT.PUT_LINE('In Klasse A eingefuegteingefuegteingefuegteingefuegt');');');');ELSIF G>60ELSIF G>60ELSIF G>60ELSIF G>60THEN THEN THEN THEN BEGINBEGINBEGINBEGINUPDATE Gehaltsklassen SET UPDATE Gehaltsklassen SET UPDATE Gehaltsklassen SET UPDATE Gehaltsklassen SET anzahlanzahlanzahlanzahl=anzahl+1 WHERE klasse='B';=anzahl+1 WHERE klasse='B';=anzahl+1 WHERE klasse='B';=anzahl+1 WHERE klasse='B';DBMS_OUTPUT.PUT_LINE('In Klasse B DBMS_OUTPUT.PUT_LINE('In Klasse B DBMS_OUTPUT.PUT_LINE('In Klasse B DBMS_OUTPUT.PUT_LINE('In Klasse B eingefuegteingefuegteingefuegteingefuegt');');');');

END; END; END; END; ELSEELSEELSEELSEUPDATE Gehaltsklassen SET UPDATE Gehaltsklassen SET UPDATE Gehaltsklassen SET UPDATE Gehaltsklassen SET anzahlanzahlanzahlanzahl=anzahl+1 WHERE klasse='C';=anzahl+1 WHERE klasse='C';=anzahl+1 WHERE klasse='C';=anzahl+1 WHERE klasse='C';

END IF; END IF; END IF; END IF; END;END;END;END;

INSERT INTO Gehaltsklassen VALUES('A',0);INSERT INTO Gehaltsklassen VALUES('A',0);INSERT INTO Gehaltsklassen VALUES('A',0);INSERT INTO Gehaltsklassen VALUES('A',0);INSERT INTO Gehaltsklassen VALUES('B',0);INSERT INTO Gehaltsklassen VALUES('B',0);INSERT INTO Gehaltsklassen VALUES('B',0);INSERT INTO Gehaltsklassen VALUES('B',0);INSERT INTO Gehaltsklassen VALUES('C',0);INSERT INTO Gehaltsklassen VALUES('C',0);INSERT INTO Gehaltsklassen VALUES('C',0);INSERT INTO Gehaltsklassen VALUES('C',0);

Page 17: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

237Datenbanken

Strukturierung des Ablaufs

• Alternativen werden durch die IF-THEN-ELSIF-ELSIF-...-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)

Page 18: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

238Datenbanken

Schleifen (Beispiele)CREATE OR REPLACE PROCEDURE FREMD0

(Anzahl NUMBER, Firma VARCHAR, Gehalt NUMBER) ISBEGIN

FOR i IN 1 .. AnzahlLOOP

EIN(Firma || i, Gehalt); END LOOP;

END;

CREATE OR REPLACE PROCEDURE FREMD(Anzahl NUMBER, Firma VARCHAR, Gehalt NUMBER) ISi INTEGER DEFAULT 1;

BEGINWHILE i<=Anzahl LOOP

EIN(Firma || i, Gehalt);i:=i+1;

END LOOP;END;

Page 19: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

239Datenbanken

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

Page 20: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

240Datenbanken

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

Page 21: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

241Datenbanken

Records

• Ein RECORDenthält mehrere Felder, entspricht einem Tupelin 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)

Page 22: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

242Datenbanken

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;

BEGINA.x:=1;A.y:=2;-- DBMS_OUTPUT.PUT_LINE(A); geht nichtDBMS_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!

Ausgabe:A.x= 1A.y= 2B.x=B.y=

Page 23: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

243Datenbanken

Etwas Semantikanalyse (2/2)

B.x:=1;B.y:=2;-- IF A=B ist verbotenIF 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;

Ausgabe:A gleich BA ungleich B

Page 24: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

244Datenbanken

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

Page 25: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

245Datenbanken

Ausnahmen (2/5)

•auslösen einer Exception•entsprechende Aktion der WHEN-Klausel ausführen•innersten Block verlassen (oft Anwendung von anonymenBlöcken sinnvoll)

PROCEDURE

BEGIN

EXCEPTION

END

ohneException

mitgefangenerException

ohnegefangeneException

PROCEDURE

BEGIN

EXCEPTION

END

PROCEDURE

BEGIN

EXCEPTION

END

Page 26: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

246Datenbanken

Ausnahmen (3/5)CREATE OR REPLACE FUNCTION noHeinz(name VARCHAR)

RETURN VARCHAR ISheinz EXCEPTION;BEGIN

IF name='Heinz'THEN

RAISE heinz;ELSE

RETURN name;END IF;

END;/

CREATE OR REPLACE PROCEDURE heinzTest ISheinz EXCEPTION;BEGIN

DBMS_OUTPUT.PUT_LINE(noHeinz('Egon'));DBMS_OUTPUT.PUT_LINE(noHeinz('Heinz'));DBMS_OUTPUT.PUT_LINE(noHeinz('Udo'));

EXCEPTION WHEN heinz THEN

DBMS_OUTPUT.PUT_LINE('Ein Heinz'); WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Wat nu?'); END;/ EXECUTE heinzTest;SELECT noHeinz('Heinz') FROM DUAL;

EgonWat nu?

SELECT SELECT SELECT SELECT noHeinznoHeinznoHeinznoHeinz('Heinz') FROM DUAL('Heinz') FROM DUAL('Heinz') FROM DUAL('Heinz') FROM DUALFEHLER in Zeile 1:FEHLER in Zeile 1:FEHLER in Zeile 1:FEHLER in Zeile 1:ORAORAORAORA----06510: PL/SQL: Unbehandelte benutzerdefinierte Ausnahmebedingung 06510: PL/SQL: Unbehandelte benutzerdefinierte Ausnahmebedingung 06510: PL/SQL: Unbehandelte benutzerdefinierte Ausnahmebedingung 06510: PL/SQL: Unbehandelte benutzerdefinierte Ausnahmebedingung ((((exceptionexceptionexceptionexception) ORA) ORA) ORA) ORA----06512065120651206512: in „SKLEUKER.NOHEINZ", Zeile 7: in „SKLEUKER.NOHEINZ", Zeile 7: in „SKLEUKER.NOHEINZ", Zeile 7: in „SKLEUKER.NOHEINZ", Zeile 7

1111

2222

33334!4!4!4!

Page 27: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

247Datenbanken

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

Page 28: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

248Datenbanken

Ausnahmen (5/5)CREATE OR REPLACE PROCEDURE exTest IS

I INTEGER DEFAULT 0;BEGIN

BEGINI:=I/I;DBMS_OUTPUT.PUT_LINE('Nicht Erreicht');

EXCEPTIONWHEN ZERO_DIVIDE THEN

DBMS_OUTPUT.PUT_LINE(''||SQLCODE||'::'||SQLERRM);END;

DBMS_OUTPUT.PUT_LINE(''||SQLCODE||'::'||SQLERRM);RAISE_APPLICATION_ERROR(-20101,'keine Lust mehr');

EXCEPTIONWHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(''||SQLCODE||'::'||SQLERRM);IF SQLCODE=-20101

THENDBMS_OUTPUT.PUT_LINE('stimmt nicht');

END IF;END;/EXECUTE exTest;

----1476::ORA1476::ORA1476::ORA1476::ORA----01476: Divisor ist Null01476: Divisor ist Null01476: Divisor ist Null01476: Divisor ist Null0::ORA0::ORA0::ORA0::ORA----0000: normal, successful completion0000: normal, successful completion0000: normal, successful completion0000: normal, successful completion----20101::ORA20101::ORA20101::ORA20101::ORA----20101: keine Lust mehr20101: keine Lust mehr20101: keine Lust mehr20101: keine Lust mehrstimmt nichtstimmt nichtstimmt nichtstimmt nicht

Page 29: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

249Datenbanken

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)

Page 30: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

250Datenbanken

CREATE OR REPLACE PROCEDURE gehaltAnpassen (mi Angestellte.MiNr%TYPE) IS

person Angestellte%ROWTYPE;BEGIN

SELECT *INTO personFROM AngestellteWHERE Angestellte.MiNr=mi;

UPDATE AngestellteSET Gehalt=person.GehaltWHERE Name=person.Name;

END;

RECORD und %ROWTYPE

CREATE TABLE ANGESTELLTE(Minr NUMBER(5),Name VARCHAR(10),Gehalt NUMBER(4),PRIMARY KEY (MiNr)

);

Mit %ROWTYPE erhält man einen RECORD-Typen, der sich genauaus den Attributen der genannten Tabelle zusammensetzt. Variablendieses Typs können dann eine Zeile der Tabelle aufnehmen

Page 31: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

251Datenbanken

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 SELECTund FROM auch PL/SQL-Variablen und

PL/SQL-Funktionen. PL/SQL-Variablen können ebenfalls in den WHERE-, GROUP-und HAVING-Klauseln verwendet

werden

Page 32: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

252Datenbanken

Beispiel für Cursor-Definition

Country Name

D Cologne

D Hamburg

Erinnerung:Tabelle City (vereinfacht):

Alle Städte in dem in der Variablen theCountry angegebenen Land:

CURSOR cities_in(theCountry Country.Code%TYPE)IS SELECT City.NameFROM CityWHERE City.Country=theCountry;

Page 33: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

253Datenbanken

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

Hamburg

Munich= virtuell im Speicher

= zugreifbar

FETCH

Page 34: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

254Datenbanken

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

Page 35: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

255Datenbanken

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)

Page 36: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

256Datenbanken

Cursor – Beispiel (1/2)

-- Nutzung von WHILECREATE OR REPLACE PROCEDURE teureLeute (betr Number)

ISCURSOR viel (betrag Number) IS

SELECT * FROM AngestellteWHERE Angestellte.Gehalt>=betrag;

zeile viel%ROWTYPE;BEGIN

OPEN viel(betr);FETCH viel INTO zeile;WHILE viel%FOUND

LOOPDBMS_OUTPUT.PUT_LINE(zeile.Name||' bekommt zu

viel! (mindestens '||betr||')');FETCH viel INTO zeile;

END LOOP;CLOSE viel;

END;

Page 37: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

257Datenbanken

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 OPENausgeführt

• bei jeder Ausführung des Schleifenkörpers wird automatisch ein FETCHausgeführt,

• Schleifenkörper enthält i.a. keinen FETCH-Befehl

• am Ende wird automatisch ein CLOSEausgeführt

• Spalten müssen explizit adressiert werden

Page 38: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

258Datenbanken

Cursor – Beispiel (2/2)-- Nutzung der CURSOR-FOR-Schleife

CREATE OR REPLACE PROCEDURE teureLeute0(betrNumber)

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;

Page 39: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

259Datenbanken

Aufdatieren an CURSOR-Position

• Um Veränderungen an der aktuellen CURSOR-Position vornehmen zu können, kann die Bedingung WHERE CURRENT OF <CURSOR-Name> genutzt werden, CURSOR muss zum ändern markiert sein

• Beispiel: Gehaltserhöhung um 10% bei allen Leuten deren MiNr kleiner als 100 ist

CREATE OR REPLACE Procedure mehrFuerAlteLeute ISCURSOR alt IS

SELECT *FROM AngestellteWHERE MiNr<100FOR UPDATE;

BEGINFOR person IN alt

LOOPUPDATE Angestellte

SET Gehalt=Gehalt*1.1WHERE CURRENT OF alt;

END LOOP;END;

Page 40: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

260Datenbanken

Trigger (1/2)

• spezielle Form von PL/SQL-Prozeduren

• werden beim Eintreten eines bestimmten Ereignisses

ausgeführt

• Spezialfall aktiver Regeln nach dem Event-Condition-Action-

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)

Page 41: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

261Datenbanken

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

Page 42: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

262Datenbanken

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

Page 43: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

263Datenbanken

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.

Page 44: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

264Datenbanken

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;

Page 45: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

265Datenbanken

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

DECLAREdefiniert

Page 46: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

266Datenbanken

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 zweiteist der Fehlertext

Page 47: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

267Datenbanken

Beispiel für Trigger (4/5)Niemand darf anfänglich mehr als die „Meiers“ in der Firma

verdienenCREATE OR REPLACE TRIGGER nichtMehrAlsMeiersBEFORE INSERT ON AngestellteFOR EACH ROWDECLARE

maxMeier NUMBER;BEGIN

SELECT MAX(Gehalt)INTO maxMeierFROM AngestellteWHERE Name='Meier';

IF :NEW.Gehalt>maxMeierTHEN

RAISE_APPLICATION_ERROR(-20111,'Nicht mehr als die Meiers');

END IF;END;

Page 48: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

268Datenbanken

Beispiel für Trigger (5/5)Niemand darf anfänglich mehr als die „Meiers“ in der Firma

verdienenCREATE OR REPLACE TRIGGER nichtMehrAlsMeiersAFTER INSERT ON AngestellteDECLARE

verboten INTEGER;BEGIN

SELECT COUNT(*)INTO verbotenFROM AngestellteWHERE Angestellte.Gehalt > (SELECT MAX(Gehalt)

FROM AngestellteWHERE Name='Meier');

IF verboten > 0THEN

RAISE_APPLICATION_ERROR(-20111,'Nicht mehr als die Meiers');

END IF;END;

nicht äquivalent zu vorher:- was wenn neuer

Mitarbeiter Meier heißt- was bei update dann insert

Page 49: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

269Datenbanken

FOR EACH ROWoder nichtCREATE 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 TrBEGIN

DBMS_OUTPUT.PUT_LINE('TrOhneEach');END;

/CREATE TRIGGER TrMitEach

BEFORE UPDATE ON TrFOR EACH ROWBEGIN

DBMS_OUTPUT.PUT_LINE('TrMitEach');END;

/UPDATE TR SET Y=Y+1 WHERE X=1;SELECT * FROM Tr;

X Y---------- ----------

1 31 41 5

3 Zeilen ausgewählt.Trigger wurde erstellt.Trigger wurde erstellt.

TrOhneEachTrMitEachTrMitEachTrMitEach

3 Zeilen wurden aktualisiert.

X Y---------- ----------

1 41 51 6

3 Zeilen ausgewählt.

Page 50: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

270Datenbanken

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

Page 51: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

271Datenbanken

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

Page 52: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

272

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 werdenCREATE TABLE Gebot(

mnr INTEGER,

ware INTEGER,

gebot NUMBER(8,2),

PRIMARY KEY(mnr,ware,gebot)

);

Datenbanken

Page 53: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

273

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 maxiFROM GebotWHERE Gebot.mnr = :NEW.mnr

AND Gebot.ware = :NEW.ware;IF maxi IS NOT NULL AND maxi >= :NEW.Gebot

THENRAISE_APPLICATION_ERROR(-20900

,'Gebot muss erhoeht werden');END IF;

END;

Datenbanken

Page 54: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

274

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 itORA-06512: at "ICH.GEBOTERHOEHEN", line 4ORA-04088: error during execution of trigger 'ICH.GEBOTERHOEHEN'04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"

Datenbanken

Page 55: Aufbau von PL/SQL-Programmen • Steuerung des ...home.edvsz.hs-osnabrueck.de/skleuker/WS14_DB/DB_WS14_Teil6.pdfProf. Dr. Stephan Kleuker Datenbanken 221 9. Einführung in PL/SQL •

Prof. Dr. Stephan Kleuker

275

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

Beispiel (4/4): Korrektur

Datenbanken