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