aufbau von pl/sql-programmen • steuerung des...
Post on 16-Apr-2019
217 Views
Preview:
TRANSCRIPT
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
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
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
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
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
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
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;
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;
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
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>)
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;
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
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;
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
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
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);
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)
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;
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)
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
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)
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=
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
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>;
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
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!
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
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
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)
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
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
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;
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
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
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)
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;
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
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;
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;
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)
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
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>;
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.
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;
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
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
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;
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
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.
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
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
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
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
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
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
top related