Vorlesung Datenbanksysteme vom 10.10.2007

PL/SQL (von Oracle)

EinführungDeklarationenCursor AblaufsteuerungFehlerbehandlungstored program units

2

PL/SQL-Programmteile in der Vorlesung

Folien: Enthalten viele ProgrammausschnitteProgramme manchmal nur auszugsweise auf den Folien wiedergegeben (immer nur die "wesentlichen" Teile).Durch das Auslassen von "unwesentlichen" Details sind die Programme auf den Folien eventuell nicht ablauffähig.

PL/SQL-Sourcen:Auf der DBS-Homepage werden auch die vollständigen PL/SQL-Sourcen bereitgestellt.Diese wurden unter Oracle 10g getestet.Empfehlenswert: die PL/SQL Sourcen ablaufen lassen und mit Modifikationen experimentieren.

Einführung

• Prozedurale DB-Programmiersprachen• Beispiel• Struktur eines PL/SQL-Blocks• SQL innerhalb von PL/SQL• Ausführung von PL/SQL in SQL*PLUS• PL/SQL-Quellen

4

SQL vs. prozedurale SprachenSQL:

"Deklarative" Programmiersprache: Man sagt dem DB-System, was man will und nicht wie man es will.Vorteil: Möglichkeiten der Anfrageoptimierung durch das DB-SystemNachteil: manchmal wären typische Sprachkonstrukte zur Ablaufsteuerung wünschenswert (weil ausdrucksstärker).

Prozedurale DB-Programmiersprachen:Fast alle DB-Systeme bieten eine (proprietäre) prozeduraleProgrammiersprache an.Proprietäre Sprache von Oracle: PL/SQL(Procedural Language/Structured Query Language)

5

Vorteile einer prozeduralenDB-Programmiersprache (1)

Enge Anbindung an SQL: In SQL: Verwendung von stored functionsIn PL/SQL: DB-Zugriff mittels SQL

Performance: durch Ausführung des Programms in der DB.

6

Vorteile einer prozeduralenDB-Programmiersprache (2)

Portabilität: PL/SQL ist plattform-unabhängig.

Sicherheitsaspekte:Stored program units werden in der DB selbst gespeichert.Stored program units bieten zusätzliche Möglichkeiten bezüglich Zugriffskontrolle.Mit Hilfe von Triggern lassen sich wesentlich komplexere Bedingungen definieren als mittels Constraints.

Aufbau von Programm-Bibliothekeninsbesondere: zahlreiche nützliche Packages von Oracle.

Objekt-Relationale Erweiterungen:Methoden mittels PL/SQL programmieren

7

BeispielSET SERVEROUTPUT ON-- suche StudentIn mit vorgegebener MatrikelnummerDECLAREl_matrnr NUMBER(10) := 26120;l_name VARCHAR2(30);l_semester NUMBER(2);

BEGINSELECT s.Name, s.Semester INTO l_name, l_semesterFROM Studenten s WHERE s.MatrNr = l_matrnr;dbms_output.put_line('Name: ' || l_name ||

', Semester: ' || l_semester);EXCEPTIONWHEN NO_DATA_FOUND THEN

dbms_output.put_line('Leider nichts gefunden!');END;/

8

Output

Bemerkung zu den verwendeten Dateien:PLSQL-Intro1.sql: enthält das PL/SQL-Programm der vorigen Seite.PLSQL-Intro2.sql: gleiches Programm, aber mit l_matrnr := 26121

SQL*PLUS-Session:

SQL> start c:\PLSQL\Einfuehrung\PLSQL-Intro1.sql

Name: Fichte, Semester: 10

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> start c:\PLSQL\Einfuehrung\PLSQL-Intro2.sql

Leider nichts gefunden!

PL/SQL-Prozedur erfolgreich abgeschlossen.

9

Aufbau eines PL/SQL-Blocks (1)3 Teile ("Sections"):

DECLARE Section (optional):Variablen-, Typen- und Cursor-Deklarationen

BEGIN Section:Enthält die eigentliche Programmlogik, z.B.: Zugriff auf die DB, Wertzuweisungen, Schleifen, Verzweigungen, etc.

EXCEPTION Section (optional):Fehlerbehandlung (die Ausführung eines PL/SQL-Blocks gilt auch dann als erfolgreich, wenn ein Fehlerzweig durchlaufen wurde).

10

Aufbau eines PL/SQL-Blocks (2)Lebensdauer der deklarierten Objekte:

Nur während der Ausführung des PL/SQL-Blocks

Kleinstmöglicher PL/SQL-Block:BEGIN

-- mindestens 1 Statement, z.B.:NULL;

END;

Kommentare:restliche Zeile auskommentieren: -- kommentarOder /* kommentar */

11

Aufbau eines PL/SQL-Blocks (3)Schachtelung von PL/SQL-Blöcken:

PL/SQL-Blöcke können "beliebig" geschachtelt werden.Der innere Block ist einfach ein Teil der BEGIN-Section.Interessante Anwendung: innerer Block inkl. Exception-Section kann analog zu einem "try … catch" Block in Java verwendet werden.

12

SQL innerhalb von PL/SQLErlaubt:

SELECT-INTO (darf nur 1 Zeile liefern)SELECT: mittels CursorDML-Kommandos ("data manipulation language"), d.h.:INSERT, UPDATE, DELETETransaction Control: COMMIT, SAVEPOINT, ROLLBACK

Nicht erlaubt:DDL-Kommandos ("data definition language"), z.B.:CREATE TABLE, CREATE VIEW, DROP TABLE, etc.

13

Ausführung von PL/SQL in SQL*PLUS

Voraussetzung für Bildschirmausgabe: SET SERVEROUTPUT ON

PL/SQL-Block mit / abschließenerst dann wird der PL/SQL-Block ausgeführt ansonsten wartet SQL*PLUS einfach auf weitere Eingaben

Datei einlesen: start <dateiname>

oder: @<dateiname>

14

PL/SQL-QuellenOracle: User's Guide and Referencehttp://download-uk.oracle.com/docs/cd/B19306_01

/appdev.102/b14261/toc.htm

PL/SQL-Tutorial (englisch)http://www-db.stanford.edu/~ullman/fcdb/oracle/or-plsql.html

PL/SQL-Tutorial (deutsch)http://www.datenbank-plsql.de/index.htm

Deklarationen

• Variablen-Deklaration• Einfache Datentypen• Zusammengesetzte Datentypen• Wertzuweisung / Ausdrücke

16

Variablen-DeklarationVerpflichtend: Variablen-Name, Variablen-TypOptional: Initial-Wert, CONSTANT, NOT NULLVariablen ohne Inital-Wert: sind bei Programmbeginn NULL

DECLARE

l_matrnr NUMBER(10) := 26120;

l_name VARCHAR2(30);

l_gdat DATE NOT NULL := SYSDATE;

l_plz CONSTANT VARCHAR2(4):= '1040';

BEGIN

dbms_output.put_line(l_matrnr || ' '|| l_name);

dbms_output.put_line(l_gdat || ' ' || l_plz);

END;

/

17

Einfache Typen in PL/SQLIm wesentlichen wie in SQL, z.B.:

CHAR, CHAR(n)VARCHAR2 (n)NUMBER, NUMBER (s), NUMBER (s,p)DATE

Zustätzliche Datentypen: BOOLEANBINARY_INTEGER, PLS_INTEGERBINARY_FLOAT, BINARY_DOUBLE

18

Zusammengesetzte Typen in PL/SQL (1)

RECORD Typ:Mehrere Felder (wie Spalten einer Zeile)Zugriff: mittels Punkt-Notation

DECLARE

TYPE StudentenTyp IS RECORD (

MatrNr NUMBER,

Name VARCHAR(30) );

l_student StudentenTyp;

BEGIN

SELECT s.MatrNr, s.Name INTO l_studentFROM Studenten s WHERE s.MatrNr = 24002;

dbms_output.put_line(l_student.Name … );

END;

19

Zusammengesetzte Typen in PL/SQL (2)

TABLE Typ:Elemente desselben Typs; wie Arrays variabler LängeOptional: IndexZugriff: mittels Position (startet bei 1)

DECLARE

TYPE StudTabTyp IS TABLE OF StudentenTypINDEX BY PLS_INTEGER;

l_stud_tab StudTabTyp;

BEGIN

SELECT s.MatrNr, s.Name INTO l_stud_tab(5)

FROM Studenten s WHERE s.MatrNr = 24002;

END;

20

Import von Typen einer TabellePL/SQL-Attribute:

%TYPE: importiert den Typ einer Spalte%ROWTYPE: importiert den Typ der ganzen Zeile(Zugriff auf die Komponenten mittels Punkt-Notation)

DECLARE

l_matrnr Studenten.MatrNr%TYPE;

l_student Studenten%ROWTYPE;

BEGINSELECT s.MatrNr INTO l_matrnr FROM … WHERE … ;SELECT * INTO l_student FROM … WHERE … ; dbms_output.put_line(l_matrnr); dbms_output.put_line(l_student.Name);

END;/

21

Host VariablenIdee: Manchmal möchte man Variablen-Werte aus der "Umgebung" innerhalb eines PL/SQL-Programms verwenden, z.B.:

Variablen der SQL*PLUS-SessionEmbedded SQL (z.B. SQLJ): Variablen der Programmier-sprache, in die SQL eingebettet ist (z.B. Java).

Lösung in PL/SQL: Host Variablen (= "bind variables")

Zugriff: Mittels ":"-Notation

22

Host Variablen: Beispielvariable matrikelNr NUMBER -- SQL*PLUS: ";" nicht nötig

BEGIN

:matrikelNr := 24002;

END;

/

print matrikelNr

DECLARE

l_name Studenten.Name%TYPE;

BEGIN

SELECT s.Name INTO l_name FROM Studenten s WHERE s.MatrNr = :matrikelNr;

END;/

23

Wertzuweisung / AusdrückeZuweisungsoperator: ":="

Ausdrücke verwenden folgende OperatorenArithmetische Operatoren: + , -, *, /Vergleichsoperatoren: =, >, <, >=, <=, ungleich: <>, ^=, !=Logische Operatoren: AND, OR, NOTString-Vergleiche: LIKE, NOT LIKE,wildcards: %, _String-Operationen: || -- string concatenationWeitere SQL-Operationen:IS NULL, IS NOT NULL,x BETWEEN a AND b, x IN (1,2,3)

Cursor

• Idee• Verwendung eines Cursors• CURSOR FOR loop• Schreibzugriff mittels Cursor• Cursor mit Parametern

25

IdeeDB-Anfragen mittels SQL sind in PL/SQL-Block erlaubt, aber:

Anfrage-Ergebnis muss mittels INTO einer Variable zugewiesen werden.Diese Variable kann nur 1 Wert (bei einfachem Typ) bzw. 1 Zeile (bei zusammengesetztem Typ) aufnehmen.

Lösung, um Ergebnisse mit mehreren Zeilen zu bearbeiten:Cursor: erlaubt das Durchlaufen des SELECT-Ergebnisses(so dass jeweils auf die aktuelle Zeile zugegriffen wird).Man kann mittels Cursor auf den aktuellen Datensatz auch schreibend zugreifen (was bei der Deklaration des Cursors angegeben werden muss).

26

BeispielSET SERVEROUTPUT ON-- Ausgabe aller StudentInnenen am BildschirmDECLARECURSOR c_student IS SELECT * FROM Studenten s;rec_student Studenten%ROWTYPE;

BEGINOPEN c_student;LOOPFETCH c_student INTO rec_student;EXIT WHEN c_student%NOTFOUND;DBMS_OUTPUT.PUT('Name: ' || rec_student.Name || ', ');DBMS_OUTPUT.PUT_LINE('Semester: ' ||

rec_student.Semester);END LOOP;CLOSE c_student;

END;/

27

Verwendung eines CursorsDeklaration:

CURSOR cursor_name IS SELECT FROM … ;

Record-Variable, in die jeweils eine Zeile des SELECT-Ergebnisses geschrieben werden kann.

Ausführungsteil:OPEN: Beim Abarbeiten des OPEN-Befehls wird das SELECT-Statement ausgewertet.FETCH: Einlesen des ersten bzw. des nächsten DatensatzesCLOSE: Schließen des Cursors (damit werden die Ressourcen dieses Cursors wieder frei gegeben).

Typisches Schleifen-Konstrukt:"Basic loop": LOOP … END LOOP;

Abbruchbedingung: EXIT WHEN cursor_name%NOTFOUND;

28

BeispielDECLARECURSOR c_student IS

SELECT s.Name, v.Titel, p.NoteFROM Studenten s, Vorlesungen v, Pruefen pWHERE s.MatrNr = p.MatrNr AND v.VorlNr = p.VorlNr;

TYPE type_student IS RECORD (sName Studenten.Name%TYPE,vTitel Vorlesungen.Titel%TYPE,pNote Pruefen.Note%TYPE );

rec_student type_student;BEGINOPEN c_student;LOOP

FETCH c_student INTO rec_student;…

END LOOP;CLOSE c_student;

END;/

29

Vordefinierte Cursor-Attribute%FOUND:

TRUE, falls der letzte FETCH einen Satz gefunden hat.

%NOTFOUND:FALSE, falls der letzte FETCH einen Satz gefunden hat.

%ROWCOUNT:Liefert die Anzahl der bisher mit FETCH gelesenen Zeilen

%ISOPEN:Liefert TRUE, falls der Cursor geöffnet ist.

Bemerkung: Falls der Cursor nicht geöffnet ist (vor dem OPEN, nach dem CLOSE) führt der Zugriff auf die Attribute %FOUND, %NOTFOUND und %ROWCOUNT zu einer Exception.

30

CURSOR FOR loop"übliche" Cursor-Verwendung:

FOR-loop, die bei %NOTFOUND abbricht

Vereinfachte Schreibweise mittels CURSOR FOR loop: FOR rec_name IN cursor_name LOOP

…

END LOOP;

"Ersparnis": RECORD-Variable muss nicht deklariert werden (die Deklaration steckt implizit in der CURSOR FOR loop).OPEN, FETCH, CLOSE, EXIT fallen ebenfalls weg.

31

BeispielSET SERVEROUTPUT ON-- Ausgabe aller StudentInnenen am BildschirmDECLARECURSOR c_student IS SELECT * FROM Studenten s;-- rec_student Studenten%ROWTYPE;

BEGIN-- OPEN c_student;FOR rec_student IN c_student LOOP-- FETCH c_student INTO rec_student;-- EXIT WHEN c_student%NOTFOUND;

DBMS_OUTPUT.PUT('Name: ' || rec_student.Name);DBMS_OUTPUT.PUT_LINE(', Semester: ' ||rec_student.Semester);

END LOOP;-- CLOSE c_student;

END; /

32

Schreibzugriff mittels CURSORIdee: Beim Verarbeiten des aktuellen Datensatzes eines Cursors erfordert die Programm-Logik eventuell, dass dieser Datensatz modifiziert oder gelöscht werden soll.

Lösung: Der Cursor muss als "FOR UPDATE" deklariert werdenIm UPDATE- oder DELETE-Kommando kann man auf den aktuellen Datensatz zugreifen mittels

WHERE CURRENT OF cursor_name

zusätzliche (optionale) Angaben bei der Deklaration:Die Spalte, auf die geschrieben werden soll=> kleinere Sperre bei Mehrbenutzer-BetriebVerhalten des PL/SQL-Blocks, falls schreibender Zugriff nicht möglich ist (d.h.: Warten oder Exception).

33

Beispiel-- Verbesserung der PruefungsnotenDECLARECURSOR c_pruefen IS SELECT * FROM Pruefen pFOR UPDATE /* optional: OF p.Note NOWAIT */;

BEGINFOR rec_pruefen IN c_pruefen LOOPIF rec_pruefen.Note = 5 THENDELETE FROM PruefenWHERE CURRENT OF c_pruefen;

ELSIF rec_pruefen.Note > 1 THEN UPDATE Pruefen SET Note = Note - 1WHERE CURRENT OF c_pruefen;

END IF; END LOOP;

END;

34

Cursor mit Parametern: BeispielSET SERVEROUTPUT ON

DECLARE

CURSOR c_student (l_sem NUMBER) IS

SELECT * FROM Studenten s WHERE s.semester = l_sem;

rec_student Studenten%ROWTYPE;

BEGIN

OPEN c_student (2);

-- verarbeite alle Studenten im 2. Semester

CLOSE c_student;

OPEN c_student (6);

-- verarbeite alle Studenten im 6. Semester

CLOSE c_student;

END;

/

Ablaufsteuerung

• IF-Statement• CASE-Statement• basic LOOP• while LOOP• for LOOP

36

IF-Statement (1)Syntax:

IF <bedingung> THEN

…

ELSIF <bedingung> THEN

…

ELSE

…

END IF;

Bemerkung:0 oder beliebig viele ELSIF-TeileELSE-Teil ist optional.

37

IF-Statement (2)Beispiel: (Umrechnung nach Euro)

IF waehrung = 'ATS' THEN euro := x / 13.7603;

ELSIF waehrung = 'BEF' THEN euro := x / 40.3399; ELSIF waehrung = 'DEM' THEN euro := x / 1.95583; ELSIF waehrung = 'ESP' THEN euro_:= x / 166.386;

ELSIF waehrung = 'FIN' THEN euro := x / 5.94573;

ELSIF waehrung = 'FRF' THEN euro := x / 6.55957;

ELSIF waehrung = 'GRD' THEN euro := x / 340.750;

…

ELSIF waehrung = 'PTE' THEN euro := x / 200.482;

ELSE

DBMS_OUTPUT.PUT_LINE('unbekannte Waehrung: '

|| waehrung);

END IF;

38

CASE-Statement (mit Selektor) (1)Syntax:

CASE <selector>

WHEN <wert1> … ;

WHEN <wert2> … ;

…

ELSE

END CASE;

Bemerkung:1 oder beliebig viele WHEN-TeileELSE-Teil ist optional.Fehler, wenn kein WHEN-Zweig ausgeführt wird und es auch keinen ELSE-Zweig gibt.

39

CASE-Statement (mit Selektor) (2)Beispiel: (Umrechnung von Euro)CASE waehrungWHEN 'ATS' THEN betrag := euro * 13.7603;WHEN 'BEF' THEN betrag := euro * 40.3399; WHEN 'DEM' THEN betrag := euro * 1.95583; WHEN 'ESP' THEN betrag := euro * 166.386;WHEN 'FIN' THEN betrag := euro * 5.94573;WHEN 'FRF' THEN betrag := euro * 6.55957;WHEN 'GRD' THEN betrag := euro * 340.750;

…WHEN 'PTE' THEN betrag := euro * 200.482;ELSE

DBMS_OUTPUT.PUT_LINE('unbekannteWaehrung: ' || waehrung);

END CASE;

40

Basic LoopSyntax:

LOOP

…

EXIT WHEN <bedingung> … ;

…

END LOOP;

Typische Anwendung: Cursor-BearbeitungOPEN c_student;

LOOP

FETCH c_student INTO rec_student;

EXIT WHEN c_student%NOTFOUND;

…

END LOOP;

CLOSE c_student;

41

WHILE LoopSyntax:

WHILE <bedingung> LOOP

…

END LOOP;

Bemerkung:Prinzipiell kann auch in der WHILE-Schleife eine EXIT-Anweisung verwendet werden (schlechter Stil?)Es gibt in PL/SQL keine REPEAT … UNTIL Schleife.

42

FOR LoopSyntax:

FOR <var> IN a..b LOOP

…

END LOOP;

Bemerkung:Die Lauf-Variable wird automatisch deklariert (und muss daher nicht im Deklarationsteil definiert werden)Durchlaufen des Zahlenbereichs in umgekehrter Richtung ist möglich: FOR <var> IN REVERSE a..b LOOP

Die Schrittlänge kann nicht beeinflusst werden. Sie ist immer 1 (bzw. -1).

Fehlerbehandlung

• Exception Section• Exception Handling• Oracle Exceptions• user-defined Exceptions

44

Exception SectionSyntax: Beispiel

BEGIN

…

EXCEPTION

WHEN NO_DATA_FOUND THEN … ;

WHEN TOO_MANY_ROWS THEN …;

WHEN OTHERS THEN …;

END;

Bemerkung:Es können Exception Handler für beliebig viele Fehlersituationen definiert werden.WHEN OTHERS: ist sozusagen der "ELSE"-Zweig.

45

Exception HandlingEinige Prinzipien:1. Wenn ein PL/SQL-Programm auf einen Fehler stößt,

springt die Programm-Ausführung zur Exception Section.2. Falls kein passender Fehlerzweig gefunden wird, wird der

WHEN OTHERS Teil ausgeführt (falls vorhanden). 3. Wenn die Fehlersituation von einem Fehlerzweig (oder

von WHEN OTHERS) abgedeckt wird, gilt die Ausführung des PL/SQL-Blocks als "erfolgreich".

4. Falls die Fehlersituation von keinem Fehlerzweig abgedeckt wird (und es kein WHEN OTHERS gibt), wird die Exception zum umschließenden Block propagiert.

5. Diese Exception Propagation (lt. Punkt 4) gilt auch bei Prozedur- oder Funktionsaufrufen.

46

Oracle ExceptionsEinige Oracle Exceptions haben vordefinierte Namen, z.B.:

NO_DATA_FOUND: Select - Into Statement innerhalb eines PL/SQL Blocks liefert 0 Zeilen.TOO_MANY_ROWS: Select - Into Statement innerhalb eines PL/SQL Blocks liefert mehr als 1 Zeile.CASE NOT FOUND: Keine WHEN-Klausel innerhalb eines CASE-Statements wird ausgeführt (und es gibt auch keinen ELSE-Zweig).DUP_VAL_ON_INDEX: Versuchte Verletzung eines Primary Key oder Unique Constraint.etc.

Bemerkung: Nicht alle Oracle Exceptions haben so einen vordefinierten Namen.

47

Nützliche FunktionenFunktionen:

SQLCODE: liefert die Oracle Fehlernummer (ORA-…)SQLERRM: liefert die Oracle Fehlermeldung (Text).

Beispiel:

EXCEPTION

WHEN … ;

WHEN OTHERS THEN

l_err_num := SQLCODE;

l_err_msg := substr(SQLERRM,1,100);

dbms_output.put_line ('FehlerNr: ' ||

l_err_num || ' FehlerMsg: ' || l_err_msg);

END;

48

User-defined ExceptionsIdee:

Fehler aus Applikationssicht muss nicht unbedingt ein Fehler aus Oracle Sicht sein. Daher gibt es in PL/SQL die Möglichkeit der benutzerdefinierten Exceptions.3 Teile: Deklaration, "RAISE", Exception-Handler.

Syntax:

DECLARE

my_exception EXCEPTION;

BEGIN

IF <bedingung> THEN RAISE my_exception;

EXCEPTION

WHEN my_exception THEN … ;

END;

Stored Program Units

• Prozeduren• Funktionen• Packages• Trigger

50

ProzedurenDefinition:CREATE [OR REPLACE] PROCEDURE my_procedure

AS -- oder: IS

… -- Deklarationsteil

BEGIN

…

EXCEPTION

…

END;

Bemerkung: Schlüsselwort "DECLARE" entfällt, da der Deklara-tionsteil durch Prozedur-Kopf und BEGIN gekennzeichnet ist.

Löschen einer stored procedure:DROP PROCEDURE my_procedure;

51

Beispiel-- StudentInnenen in eine andere Tabelle kopieren

CREATE OR REPLACE PROCEDURE p_copy_student AS

CURSOR c_student IS SELECT * FROM Studenten s;

BEGIN

FOR rec_student IN c_student LOOP

INSERT INTO Studenten_Kopie

VALUES (rec_student.MatrNr,

rec_student.Name, rec_student.Semester);

END LOOP;

END;

/

52

ParameterParameter:

Arten: IN, OUT oder IN OUTDatentyp: nur die Typfamilie (ohne Size-Information)

Beispiel:CREATE OR REPLACE PROCEDURE p_copy_student(l_sem IN NUMBER, l_anzahl OUT NUMBER) AS

CURSOR c_student IS SELECT * FROM Studenten s WHERE s.semester = l_sem;

BEGIN

…

l_anzahl := c_student%ROWCOUNT;

END;

/

53

ProzeduraufrufeVon einem PL/SQL-Block aus (d.h. anonymer PL/SQL-Block oder stored program unit): DECLARE

anzahl NUMBER(2);

BEGIN

p_copy_student(4,anzahl);

dbms_output.put_line(to_char(anzahl));

END;

/

Von SQL*PLUS aus:variable anzahl NUMBER;

execute p_copy_student(4,:anzahl);

print anzahl;

54

Transaction ControlEin Prozeduraufruf (ebenso ein Funktionsaufruf) bedeutet eine Fortsetzung der laufenden Transaktion.

Innerhalb einer Prozedur (oder einer Funktion) können auch Transaction Control Kommandos stehen, d.h.: COMMIT, ROLLBACK, SAVEPOINT.

Transaction Control Kommandos innerhalb einer Prozedur (oder Funktion) haben denselben Effekt wie innerhalb des aufrufenden Blocks.

In Triggern sind keine Transaction Control Kommandos erlaubt.

55

FunktionenDefinition:CREATE [OR REPLACE] FUNCTION my_function

( <Parameterliste> ) RETURN <Datentyp>

AS -- oder: IS

… -- Deklarationsteil

BEGIN

…

RETURN <ausdruck>;

…

EXCEPTION

…

END;

Löschen: DROP FUNCTION my_function;

56

BeispielCREATE OR REPLACE FUNCTION f_copy_student

(l_sem IN NUMBER) RETURN NUMBER AS

l_anzahl NUMBER;

CURSOR c_student IS … ;

BEGIN

…

l_anzahl := c_student%ROWCOUNT;

RETURN l_anzahl;

END;

/

57

Funktionsaufrufe (1)Von einem PL/SQL-Block aus (d.h. anonymer PL/SQL-Block oder stored program unit): DECLARE

anzahl NUMBER(2);

BEGIN

anzahl := f_copy_student(4);

dbms_output.put_line(to_char(anzahl));

END;

/

Von SQL*PLUS aus:variable anzahl NUMBER;

execute :anzahl := f_copy_student(4);

print anzahl;

58

Funktionsaufrufe (2)Im Gegensatz zu Prozeduren können Funktionen auch innerhalb eines SQL-Statements aufgerufen werden, z.B.:

SELECT …, f_anzahl (s.semester) as anzahlFROM …WHERE … ;

SELECT …FROM …WHERE f_anzahl (s.semester)> 17;

Voraussetzung für Funktionsaufrufe von SQL aus:Ausschließlich IN-ParameterKeine Transaction Control Kommandos Ausschließlich SQL-Datentypen (und nicht z.B. BOOLEAN) bei Parametern und RETURN-WertKeine Seiteneffekte auf die Datenbank

59

PackagesIdee: Mehrere Definitionen (Prozeduren, Funktionen, Variablen, Typen, Cursor, Exceptions) werden zu einer Einheit (dem "Package") zusammengefasst.

Vorteile:StrukturierungKapselung: public / private DefinitionTrennung von Schnittstellen und ImplementierungDefinition von (Session-)globalen VariablenOverloading von Prozeduren und Funktionenzusätzliche Caching-Möglichkeiten, z.B. bei Cursor

Package Definition:Package Specification (Schnittstelle + public Definitionen) Package Body (Implementierung + private Definitionen)

60

Package SpecificationDefinition:CREATE [OR REPLACE] PACKAGE my_package AS

/* in beliebiger Reihenfolge:

Variablen-, Cursor-Deklationen

Typ-, Exception-Deklarationen

Prozedurköpfe

Funktionsköpfe

*/

END;

/

Löschen: DROP PACKAGE my_package;

61

BeispielCREATE OR REPLACE PACKAGE copy_package AS

PROCEDURE p_copy_student (l_sem IN NUMBER,l_anzahl OUT NUMBER);

FUNCTION f_copy_student (l_sem IN NUMBER)RETURN NUMBER;

FUNCTION f_anzahl (l_sem IN NUMBER) RETURNNUMBER;

END;

/

62

Package BodyDefinition:CREATE [OR REPLACE] PACKAGE BODY my_package AS

/* in beliebiger Reihenfolge:

Private Deklarationen (sind nur innerhalb des Package Body sichtbar)

Prozedur-Implementierungen

Funktions-Implementierungen

*/

END;

/

Löschen: DROP PACKAGE BODY my_package;

63

BeispielCREATE OR REPLACE PACKAGE BODY copy_package AS

PROCEDURE p_copy_student (l_sem …) AS

CURSOR c_student IS SELECT * FROM …;

BEGIN…

END p_copy_student;

FUNCTION f_copy_student (l_sem IN NUMBER)

RETURN NUMBER AS

CURSOR c_student IS SELECT * FROM … ;

BEGIN…

END f_copy_student; /* f_… beim END optional */

END copy_package; /* copy_… beim END optional */

/

64

Verwendung der Definitionenaußerhalb des Package: inclusive Package-Name, mit Punktnotation, z.B.:

variable anzahl NUMBER;

execute copy_package.p_copy_student(2,:anzahl);

print anzahl;

innerhalb des Package (sowohl in der Specification als auch im Body): ohne Package-Name, z.B.

FUNCTION f_copy_student …

…

BEGIN

p_copy_student(l_semester, l_anzahl);

…

END;

65

TriggerTrigger-Definition für eine Tabelle:CREATE [OR REPLACE] TRIGGER my_trigger

[BEFORE, AFTER]

[INSERT, DELETE, UPDATE]

[OF <spalten-name>]

ON <tabellen-name>

[REFERENCING OLD as <name> | NEW as <name>]

[FOR EACH ROW]

[WHEN <bedingung>]

<Trigger-Body>

Löschen: DROP TRIGGER my_trigger; Deaktivieren: ALTER TRIGGER my_trigger DISABLE;

Aktivieren: ALTER TRIGGER my_trigger ENABLE;

66

Trigger BodyEntweder ein PL/SQL-Block:DECLARE…

BEGIN…

EXCEPTIION…

END;

Oder ein Prozedur-Aufruf:call my_procedure;

Bemerkung: Declare- und Exception-Section sind optional. Falls der PL/SQL-Block eine Declare Section enthält, muss das Schlüsselwort "DECLARE" explizit verwendet werden.

67

Trigger-AusführungDer Trigger wird im Zusammenhang mit einem DML-Eventausgeführt, d.h. INSERT und/oder UPDATE und/oder DELETE(Bemerkung: DML = Data manipulation language):

BEFORE / AFTER: Trigger wird vor oder nach dem Event ausgeführt.FOR EACH ROW: Trigger wird für jede Zeile, die vom aktuellen DML-Kommando betroffen ist, ausgeführt oder nur ein Mal für das DML-Kommando.

Vorsicht bei mehreren Triggern:Bei mehreren Triggern auf einer Tabelle ist die Reihenfolge der Ausführung nicht definiert.Eine Aktion innerhalb eines Triggers kann selbst wiederum einen Trigger auslösen.

68

Werte von NEW und OLDAllgemeine Regeln:

OLD und NEW werden innerhalb des PL/SQL-Codes im Trigger Body mittels :OLD bzw. :NEW verwendet.

OLD und NEW beziehen sich immer auf eine Zeile.=> "FOR EACH ROW" erforderlich (auch bei INSERT)

Zugriff auf die Spalten innerhalb der Zeile mittels Punkt-Notation, z.B. :OLD.spalte1 oder :NEW.spalte2

Wann NEW und OLD definiert sind:INSERT: OLD undefiniert, NEW enthält die insert-WerteUPDATE: OLD und NEW definiertDELETE: OLD enthält die "alten" Werte, NEW undefiniert.

69

Typische Trigger-AnwendungenKomplexe Integritätsbedingungen:

Mit Triggern lassen sich wesentlich komplexere Bedingungen formulieren als mit CHECK-Constraints (z.B.: Bedingungen, die von alten und neuen Werten abhängen)

Referentielle Integrität:Mit Triggern können Änderungen eines referenziertenSchlüssels "kaskadiert" werden (z.B.: Bei Änderung einer Abteilungsbezeichnung in einem Unternehmen müssen die Foreign Keys in der Mitarbeiter-Tabelle angepasst werden)

Abgeleitete Daten:Mittels Trigger werden zusammenhängende Daten konsistent gehalten (z.B.: Tabelle mit Einzelbestellungen und Spalte mit Gesamtpreis).

70

BeispielCREATE OR REPLACE TRIGGER t_trigger

AFTER UPDATE OF AbtNr ON Abteilungen

FOR EACH ROW

-- Tabelle Mitarbeiter hat einen FK auf Abteilungen

DECLARE

CURSOR c_mitarbeiter IS SELECT * FROM mitarbeiter m

WHERE m.AbtNr = :OLD.AbtNr

FOR UPDATE;

BEGIN

FOR rec_mitarbeiter IN c_mitarbeiter LOOP

UPDATE Mitarbeiter SET AbtNr = :NEW.AbtNr

WHERE CURRENT OF c_mitarbeiter;

END LOOP;

END;

/

71

INSTEAD OF Trigger (1)Views sind üblicherweise nicht durch DML-Kommandosänderbar (insbesondere Views, die mittels Joins, Aggregat-Funktionen, GROUP BY, etc. definiert wurden).

Mittels INSTEAD OF Trigger können DML-Aktionen"nachgebaut" werden, d.h.: Der Trigger führt die entsprechenden Aktionen auf den Basistabellen der Viewdurch, z.B.:

INSTEAD OF UPDATE: Trigger kann mit den NEW-Wertendie Updates auf den Basistabellen durchführen.INSTEAD OF INSERT: Trigger kann mit den NEW-Wertenentsprechende Zeilen in den Basistabellen einfügen.INSTEAD OF DELETE: Trigger kann mit Hilfe der OLD-Werte Zeilen in den Basistabellen finden und löschen.

72

INSTEAD OF Trigger (2)Definition:CREATE [OR REPLACE] TRIGGER my_trigger

INSTEAD OF

[INSERT, DELETE, UPDATE]

[OF <spalten-name>]

ON <view-name>

[REFERENCING OLD as <name> | NEW as <name>]

[FOR EACH ROW]

[WHEN <bedingung>]

<Trigger-Body>

Löschen: DROP TRIGGER my_trigger;Aktivieren/Deaktivieren: ALTER TRIGGER …

73

BeispielCREATE OR REPLACE TRIGGER t_instead_triggerINSTEAD OF UPDATE ON lesen_view-- View = Join der Tabellen Vorlesungen und ProfessorenFOR EACH ROWBEGINIF (:OLD.VorlNr != :NEW.VorlNr) OR

(:OLD.PersNr != :NEW.PersNr) THEN -- Fehler

ELSIF :NEW.Titel != :OLD.Titel THENUPDATE Vorlesungen SET Titel = :NEW.TitelWHERE VorlNr = :OLD.VorlNr;

ELSIF :NEW.Name != :OLD.Name THENUPDATE Professoren SET Name = :NEW.NameWHERE PersNr = :OLD.PersNr;

END IF; END;/