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