Datenbanksysteme 198Prof. Dr. Stephan Kleuker
9. Einführung in PL/SQL
• Motivation für PL/SQL• Aufbau von PL/SQL-Programmen• Alternativen• Steuerung des Programmablaufs
• Records• Ausnahmebehandlung
Datenbanksysteme 199Prof. Dr. Stephan Kleuker
Einwurf
• Oracle nutzt Transaktionssteuerung (wie alle echten Datenbanken)
• Nutzer haben Gefühl, dass sie individuell arbeiten• Vorstellung: Nutzer arbeiten auf lokalen Kopien• erst mit Befehl COMMIT;werden Änderungen
endgültig übernommen• kommen mehrere Nutzer in Konflikt (gleiches Datum
bearbeiten), findet DB Lösung (z. B. ein Nutzer muss warten)
• Fazit: Nach INSERT-Befehlen soll COMMIT;stehen
Datenbanksysteme 200Prof. Dr. Stephan Kleuker
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 (Procedurallanguage extensions to SQL)
• Vorteile von PL/SQL: Bessere Integration der prozeduralen Elemente in die Datenbank; Nutzung in Prozeduren, Funktionen und Triggern
Datenbanksysteme 201Prof. Dr. Stephan Kleuker
Warum Erweiterung von SQL sinnvoll ist
• keine prozeduralen Konzepte in SQL (Schleifen, Verzweigungen, Variablendeklarationen)
• viele Aufgaben nur umständlich über Zwischentabellen oder überhaupt nicht in SQL zu realisieren.– Transitive Hülle
• Programme repräsentieren anwendungsspezifisches Wissen, das nicht in der Datenbank enthalten ist
Datenbanksysteme 202Prof. Dr. Stephan Kleuker
Warum PL/SQL generell interessant ist
• PL/SQL erhöht die Funktionalität und Mächtigkeit von SQL-Anfragen
• Obwohl PL/SQL Oracle-spezifisch ist, können viele zentralen Ideen (z.B. Cursor) auch genutzt werden, wenn sie SQL in andere Programmiersprachen einbetten (z. B. Transact SQL für MS Server)
• Für Informatiker ist es uninteressant, welche Programmiersprache sie können, sie müssen aber die Fähigkeit haben, sich schnell in eine beliebige Sprache einzuarbeiten; PL/SQL ist ein gutes Beispiel, diese Fähigkeit zu prüfen
Datenbanksysteme 203Prof. Dr. Stephan Kleuker
Anmerkungen zu PL/SQL in dieser Veranstaltung
• PL/SQL wird schrittweise erweitert, wir betrachten nur Basiskonzepte (z.B. zum Erstellen von Triggern)
• PL/SQL ist eine prozedurale Sprache, die sich an Ada anlehnt (z.B. strenge Typisierung), C -Kenntnisse helfen aber auch
• PL/SQL hat einen „objekt-basierten“-Ansatz, erlaubt die Aufteilung der Software in Packages
• In Oracle existieren einige Built-In-Packages, die hier nur am Rand betrachtet werden
• Achtung: PL/SQL wird kontinuierlich erweitert, ab und zu klappt nicht das, was einem logisch erscheint
Datenbanksysteme 204Prof. Dr. Stephan Kleuker
Einschub: SEQUENCE und DUAL
• In Oracle gibt es keine Auto-Inkrement-Funktion, ma n kann aber spezielle Zähler (Sequenzen) definieren
• CREATE SEQUENCE mi INCREMENT BY 1;
definiert eine Sequenz mi , die den Startwert 1 hat, • durch den Aufruf mi.NEXTVAL , wird der Wert von mi zurück
gegeben und um eins erhöht• Nutzungsbeispiel:
INSERT INTO Angestellte VALUES(mi.NEXTVAL,‘Meier‘,30);
INSERT INTO Angestellte VALUES(mi.NEXTVAL,‘Mücke‘,25);
• Will man Informationen über verschiedene Werte von Oracle erfahren, kann man dazu die Dummy-Tabelle DUALnutzen
• Beispiel: Anfrage einer Sequenz mit ErhöhungSELECT mi.NEXTVAL FROM DUAL;
Datenbanksysteme 205Prof. Dr. Stephan Kleuker
Einstieg ohne DB (Hello World)
CREATE OR REPLACE PROCEDURE Hello IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END Hello;
1. Zeile: Definition einer Prozedur (Parameter im nächsten Schritt)
2.-4. Zeile: Rumpf der Prozedur mit Ausgabebefehl
Ausführung: EXECUTE Hello;
Datenbanksysteme 206Prof. Dr. Stephan Kleuker
Anmerkung zur SW -Entwicklung
DBMS_OUTPUT.PUT_LINE('...') ist Prozedur eines Zusatzpakets DBMS_OUTPUT und kann bei inhaltlicher Fehlersuche hilfreich sein
Datenbanksysteme 207Prof. Dr. Stephan Kleuker
Aufbau einer PL/SQL-Prozedur oder Funktion
CREATE OR REPLACE PROCEDURE<PName> (<Parameter>) IS
<Variablenname> <VariablenTyp>BEGIN
<PL/SQL-Programmteile und SQL-Anfragen>EXCEPTION
<Behandlung von Ausnahmen (optionaler Anteil)>END;
CREATE OR REPLACE FUNCTION<FName> (<Parameter>) RETURN<Ergebnistyp> IS
... (wie oben, aber mindestens ein Befehl RETURN<Ergebnis>)
Datenbanksysteme 208Prof. Dr. Stephan Kleuker
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;
Datenbanksysteme 209Prof. Dr. Stephan Kleuker
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 Tabell en)
Variablen referenziert werden
Datenbanksysteme 210Prof. Dr. Stephan Kleuker
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;
Datenbanksysteme 211
Ausführung
SELECT * FROM Angestellte;
SELECT * FROM Protokoll;
EXECUTE EIN1('ich',42);
SELECT * FROM Angestellte;
SELECT * FROM Protokoll;
Prof. Dr. Stephan Kleuker
Keine Zeilen gewähltKeine Zeilen gewähltanonymer Block abgeschlossen
MINR NAME GEHALT----- ---------- ------
1 ich 42
WER WAS WO WEN WANN ---------- ---------- ------------ ---- --------SKLEUKER EINFUEGEN ANGESTELLTE 1 19.11.12
Datenbanksysteme 212Prof. Dr. Stephan Kleuker
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
Datenbanksysteme 213Prof. Dr. Stephan Kleuker
Alternativen
CREATE TABLE Gehaltsklassen(CREATE TABLE Gehaltsklassen(CREATE TABLE Gehaltsklassen(CREATE TABLE Gehaltsklassen(klasse VARCHAR(1),klasse VARCHAR(1),klasse VARCHAR(1),klasse VARCHAR(1),anzahl NUMBER(3)anzahl NUMBER(3)anzahl NUMBER(3)anzahl 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 anzahl=anzahl+1 WHERE klasse='A';THEN UPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='A';THEN UPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='A';THEN UPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='A';
DBMS_OUTPUT.PUT_LINE('In Klasse A eingefuegt');DBMS_OUTPUT.PUT_LINE('In Klasse A eingefuegt');DBMS_OUTPUT.PUT_LINE('In Klasse A eingefuegt');DBMS_OUTPUT.PUT_LINE('In Klasse A eingefuegt');ELSIF G>60ELSIF G>60ELSIF G>60ELSIF G>60THEN THEN THEN THEN BEGINBEGINBEGINBEGINUPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='B';UPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='B';UPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='B';UPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='B';DBMS_OUTPUT.PUT_LINE('In Klasse B eingefuegt');DBMS_OUTPUT.PUT_LINE('In Klasse B eingefuegt');DBMS_OUTPUT.PUT_LINE('In Klasse B eingefuegt');DBMS_OUTPUT.PUT_LINE('In Klasse B eingefuegt');
END; END; END; END; ELSEELSEELSEELSEUPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='C';UPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='C';UPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='C';UPDATE Gehaltsklassen SET anzahl=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);
Datenbanksysteme 214Prof. Dr. Stephan Kleuker
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)
Datenbanksysteme 215Prof. Dr. Stephan Kleuker
Schleifen (Beispiele)
CREATE OR REPLACE PROCEDURE FREMD0(Anzahl NUMBER, Firma VARCHAR, Gehalt NUMBER) IS
BEGINFOR 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;
Datenbanksysteme 216Prof. Dr. Stephan Kleuker
Übersicht - Schleifen
• Simple LOOP: LOOP ... END LOOP;
• WHILE LOOP:WHILE <bedingung> LOOP ... END LOOP;
• Numeric FOR LOOP:FOR <loop_index>
IN [REVERSE] <Anfang> .. <Ende>
LOOP … END LOOP;
• Die Variable <loop_index> wird dabei automatisch als INTEGER deklariert.
• EXIT [WHEN <bedingung>] : LOOP Verlassen (ist aber schlechter Programmierstil)
Datenbanksysteme 217Prof. Dr. Stephan Kleuker
Zugriffsrechte bei Prozeduren / Funktionen
• Benutzungsrechte vergeben:GRANT EXECUTE ON <procedure/function>
TO <user>;
• Prozeduren und Funktionen werden jeweils mit den Zugriffsrechten des Besitzers ausgeführt
• d.h. der Nutzer kann die Prozedur/Funktion auch dann aufrufen, wenn er kein Zugriffsrecht auf die dabei benutzten Tabellen hat
• Rechtesteuerung in späteren VL
• Anmerkung: Sieht man PL/SQL als serverseitige Programmierung, ist dies ein zentrales Argument für PL/SQL
Datenbanksysteme 218Prof. Dr. Stephan Kleuker
Records
• Ein RECORDenthä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 (k ein direkter Vergleich, direkte Zuweisung möglich, bei Zuweisung werden Werte kopiert)
Datenbanksysteme 219Prof. Dr. Stephan Kleuker
Etwas Semantikanalyse (1/2)
CREATE OR REPLACE PROCEDURE RecordTest ISTYPE 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=
Datenbanksysteme 220Prof. Dr. Stephan Kleuker
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
Datenbanksysteme 221Prof. Dr. Stephan Kleuker
Ausnahmen (1/5)
• Declaration Section: Deklaration (der Namen) benutzerdefinierter Exceptions.[DECLARE] <exception_name> EXCEPTION;
• Exceptions können dann an beliebigen Stellen des PL/SQL-Blocks durch RAISE ausgelöst werden. IF <condition>
THEN RAISE <exception_name>;
• Exception Section: Definition der beim Auftreten einer Exception auszuführenden Aktionen.WHEN <exception_name>
THEN <PL/SQL-Statement>;
WHEN OTHERS THEN <PL/SQL-Statement>;
Datenbanksysteme 222Prof. Dr. Stephan Kleuker
Ausnahmen (2/5)
• auslösen einer Exception• entsprechende Aktion der WHEN-Klausel ausführen• innersten Block verlassen (oft Anwendung von anon ymenBlöcken sinnvoll)
PROCEDURE
BEGIN
EXCEPTION
END
ohneException
mitgefangenerException
ohnegefangeneException
PROCEDURE
BEGIN
EXCEPTION
END
PROCEDURE
BEGIN
EXCEPTION
END
Datenbanksysteme 223Prof. Dr. Stephan Kleuker
Ausnahmen (3/5)
CREATE OR REPLACE FUNCTION noHeinz(name VARCHAR) RETURN VARCHAR IS
heinz 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 noHeinz('Heinz') FROM DUALFEHLER in Zeile 1:ORA-06510: PL/SQL: Unbehandelte benutzerdefinierte Ausnahmebedingung (exception)ORA-06512: in „SKLEUKER.NOHEINZ", Zeile 7
1
2
34!
Datenbanksysteme 224Prof. Dr. Stephan Kleuker
Ausnahmen (4/5)
• Es gibt viele vordefinierte Ausnahmen, die alle abgeprüft und bearbeitet werden können, Beispiele:– NO_DATA_FOUND– TOO_MANY_ROWS– INVALID_CURSOR– ZERO_DIVIDE– DUP_VAL_ON_INDEX
• Ausnahmen können auch direkt ausgelöst werden:RAISE_APPLICATION_ERROR(-20101,
'Kunde nicht kreditwürdig');
• Die Werte dieser Ausnahmen müssen zwischen -21000 und -20000 liegen, Rest ist für Oracle
Datenbanksysteme 225Prof. Dr. Stephan Kleuker
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::ORA-01476: Divisor ist Null0::ORA-0000: normal, successful completion-20101::ORA-20101: keine Lust mehrstimmt nicht
Datenbanksysteme 226Prof. Dr. Stephan Kleuker
10. Cursor und Trigger in PL/SQL
• Definition eines Cursor
• Einsatzmöglichkeiten von Cursorn
• Verschiedene Arten von Triggern
Hinweis: Oracle-Bibliothek (als eine Sammlung herunterladbar)
Datenbanksysteme 227Prof. Dr. Stephan Kleuker
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 zusammense tzt. Variablendieses Typs können dann eine Zeile der Tabelle aufn ehmen
Datenbanksysteme 228Prof. Dr. Stephan Kleuker
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
Datenbanksysteme 229Prof. Dr. Stephan Kleuker
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;
Datenbanksysteme 230Prof. Dr. Stephan Kleuker
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 ei ngelesen,
wobei der CURSOR automatisch ein Feld weiter gesetz t wird
• OPEN cities_in('D');Name
Cologne
Hamburg
Munich= virtuell im Speicher
= zugreifbar
FETCH
Datenbanksysteme 231Prof. Dr. Stephan Kleuker
FETCH• FETCH <cursor-name>
INTO <record-variable>; oder
FETCH <cursor-name> INTO <variable-list>;
• bewegt den Cursor auf die nächste Zeile des Ergebni sses der Anfrage und kopiert diese in die angegebene Record- Variable oder Variablenliste.
• Diese wird kann z.B. mit <cursor-name>%ROWTYPE mit dem Record-Typ des Cursors definiert werden:
<variable> <cursor-name>%ROWTYPE;
• CLOSE <cursor-name>; schließt einen Cursor.• nicht möglich:
OPEN cities_in ('D');
OPEN cities_in ('CH');
FETCH cities_in INTO <variable>;
ein parametrisierter Cursor, nicht eine Familie von Cursoren
Datenbanksysteme 232Prof. Dr. Stephan Kleuker
Attribute von Cursorn
• <cursor-name>%ISOPEN : Cursor offen?
• <cursor-name>%FOUND : Solange ein Cursor bei der letzten FETCH-Operation ein neues Tupel gefunden hat, ist <cursor-name>%FOUND = TRUE .
• <cursor-name>%NOTFOUND: TRUE wenn man alle Zeilen eines Cursors geFETCHt hat.
• <cursor-name>%ROWCOUNT: Anzahl der von einem Cursor bereits gelesenen Tupel.
• Attribute nicht innerhalb eines SQL-Ausdrucks verwendbar (gehören zu Ablaufsteuerungsmöglichkeiten in PL/SQL)
Datenbanksysteme 233Prof. Dr. Stephan Kleuker
Cursor – Beispiel (1/2)
-- Nutzung von WHILECREATE OR REPLACE PROCEDURE teureLeute (betr Number ) IS
CURSOR viel (betrag Number) ISSELECT * 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;
Datenbanksysteme 234Prof. Dr. Stephan Kleuker
Cursor FOR LOOP
• FOR <record_index> IN <cursor-name>
LOOP ... END LOOP;
• <record_index> wird dabei automatisch als Variable vom Typ <cursor-name>%ROWTYPEdeklariert,
• <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
Datenbanksysteme 235Prof. Dr. Stephan Kleuker
Cursor – Beispiel (2/2)
-- Nutzung der CURSOR-FOR-Schleife
CREATE OR REPLACE PROCEDURE teureLeute0(betr Number )
IS
CURSOR viel (betrag Number) IS
SELECT *
FROM Angestellte
WHERE Angestellte.Gehalt>=betrag;
BEGIN
FOR person IN viel(betr)
LOOP
DBMS_OUTPUT.PUT_LINE(person.Name||' verdient
zu viel! (mindestens '||betr||')');
END LOOP;
END;
Datenbanksysteme 236Prof. Dr. Stephan Kleuker
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;
Datenbanksysteme 237Prof. Dr. Stephan Kleuker
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)
Datenbanksysteme 238Prof. Dr. Stephan Kleuker
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.
Datenbanksysteme 239Prof. Dr. Stephan Kleuker
Syntax von Triggern (1/2)
CREATE [OR REPLACE] TRIGGER <trigger-name>BEFORE | AFTER{INSERT | DELETE | UPDATE} [OF <column-list>][ OR {INSERT | DELETE | UPDATE}
[OF <column-list>]]...[ OR {INSERT | DELETE | UPDATE}
[OF <column- list>]] ON <table>[FOR EACH ROW][WHEN (<condition>)]<pl/sql-block>;
Datenbanksysteme 240Prof. Dr. Stephan Kleuker
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 :OLDbzw. :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 :NEWsind in <condition> erlaubt.
Datenbanksysteme 241Prof. Dr. Stephan Kleuker
Beispiel für Trigger (1/5)
• Wenn ein Landes-Code geändert wird, pflanzt sich diese Änderung auf die Relation Province fort:
CREATE OR REPLACE TRIGGER change_Code
BEFORE UPDATE OF Code
ON Country
FOR EACH ROW
BEGIN
UPDATE Province
SET Country = :NEW.Code
WHERE Country = :OLD.Code;
END;
Datenbanksysteme 242Prof. Dr. Stephan Kleuker
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 zwisc hen „ FOR
EACH ROW“ und BEGIN nach dem Schlüsselwort DECLARE
definiert
Datenbanksysteme 243Prof. Dr. Stephan Kleuker
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_ERRORwird ein Fehler erzeugt, der zum Abbruch führt. Der erste Parameter muss zwischen -21000 und -20000 liegen, der zweiteist der Fehlertext
Datenbanksysteme 244Prof. Dr. Stephan Kleuker
Beispiel für Trigger (4/5)
Niemand darf anfänglich mehr als die „Meiers“ in de r Firma verdienen
CREATE 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;
Datenbanksysteme 245Prof. Dr. Stephan Kleuker
Beispiel für Trigger (5/5)
Niemand darf anfänglich mehr als die „Meiers“ in de r 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;
Datenbanksysteme 246Prof. Dr. Stephan Kleuker
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 TRSET Y=Y+1WHERE 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.
Datenbanksysteme 247Prof. Dr. Stephan Kleuker
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. änd ert
• d.h. Ergebnis abhängig von der Reihenfolge der veränderten Tupel
• ORACLE: Betroffene Tabellen werden während der gesamten Aktion als „mutating“ gekennzeichnet, können nicht erneut von Triggern gelesen oder geschrieben werden
• Nachteil: Oft ein zu strenges Kriterium
Datenbanksysteme 248Prof. Dr. Stephan Kleuker
Problem der Mutating Tables (2/2)
• Trigger soll auf Tabelle zugreifen, auf der er selbe r definiert ist– nur das auslösende Tupel soll von dem Trigger
gelesen/geschrieben werden: Verwendung eines BEFORE-Triggers und der :NEW- und :OLD -Variablen
– es sollen neben dem auslösenden Tupel auch weitere Tupel verändert werden: Verwendung eines Statement-orientierten Triggers
• Trigger soll auf andere Tabellen zugreifen: Verwendung von Statement-Triggern und ggf. Hilfstabellen
Datenbanksysteme 249
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)
);
Prof. Dr. Stephan Kleuker
Datenbanksysteme 250
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;
Prof. Dr. Stephan Kleuker
Datenbanksysteme 251
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"
Prof. Dr. Stephan Kleuker
Datenbanksysteme 252
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
Prof. Dr. Stephan Kleuker
Top Related