PL/SQL - Programmierung von Programmeinheiten
© Prof. T. Kudraß, HTWK Leipzig
Gespeicherte Prozeduren
– Eine Prozedur ist ein benannter PL/SQL Block, der eine Aktion ausführt.
– Eine Prozedur kann in der Datenbank gespeichert sein als Datenbankobjekt zur wiederholten Ausführung.
© Prof. T. Kudraß, HTWK Leipzig
Syntax zum Erzeugen von Prozeduren
CREATE [OR REPLACE] PROCEDURE procedure_name (argument1 [mode1] datatype1, argument2 [mode2] datatype2, . . .IS [AS]PL/SQL Block;
CREATE [OR REPLACE] PROCEDURE procedure_name (argument1 [mode1] datatype1, argument2 [mode2] datatype2, . . .IS [AS]PL/SQL Block;
© Prof. T. Kudraß, HTWK Leipzig
Entwicklung von Prozeduren/Funktionen
Textfile
SourceCode
P-Code
IDEIDE
Speichern in Datenbank
Compile
Execute
1
2
Systemeditor
JDeveloper JDeveloper SQLDeveloper SQLDeveloper
Application Express Application Express EditierenEditieren
© Prof. T. Kudraß, HTWK Leipzig
Parameter-Modi
IN OUT
Muß bekannt sein
Wert zum Subpro-gramm übermittelt; Rückgabe an Aufrufumgebung
Initialisierte Variable
Muß Variable sein
OUT
Muß bekannt sein
Rückgabe an Aufrufumgebung
Uninitialisierte Variable
Muß Variable sein
IN
Default
Wert wird zum Subprogram übermittelt
Formaler Parameter wie Konstante
Aktueller Parameter: Literal, Ausdruck, Kon-stante, init.Variable
© Prof. T. Kudraß, HTWK Leipzig
IN-Parameter: Beispiel
SQL> CREATE OR REPLACE PROCEDURE raise_salary 2 (v_id in emp.empno%TYPE) 3 IS 4 BEGIN 5 UPDATE emp 6 SET sal = sal * 1.10 7 WHERE empno = v_id; 8 END raise_salary; 9 /Procedure created.Procedure created.
SQL> EXECUTE raise_salary (7369)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> CREATE OR REPLACE PROCEDURE raise_salary 2 (v_id in emp.empno%TYPE) 3 IS 4 BEGIN 5 UPDATE emp 6 SET sal = sal * 1.10 7 WHERE empno = v_id; 8 END raise_salary; 9 /Procedure created.Procedure created.
SQL> EXECUTE raise_salary (7369)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
v_idv_id73697369
© Prof. T. Kudraß, HTWK Leipzig
OUT-Parameter: Beispiel
SQL> CREATE OR REPLACE PROCEDURE query_emp 1 (v_id IN emp.empno%TYPE, 2 v_name OUT emp.ename%TYPE, 3 v_salary OUT emp.sal%TYPE, 4 v_comm OUT emp.comm%TYPE) 5 IS 6 BEGIN 7 SELECT ename, sal, comm 8 INTO v_name, v_salary, v_comm 9 FROM emp 10 WHERE empno = v_id; 11 END query_emp; 12 /
SQL> CREATE OR REPLACE PROCEDURE query_emp 1 (v_id IN emp.empno%TYPE, 2 v_name OUT emp.ename%TYPE, 3 v_salary OUT emp.sal%TYPE, 4 v_comm OUT emp.comm%TYPE) 5 IS 6 BEGIN 7 SELECT ename, sal, comm 8 INTO v_name, v_salary, v_comm 9 FROM emp 10 WHERE empno = v_id; 11 END query_emp; 12 /
© Prof. T. Kudraß, HTWK Leipzig
OUT-Parameter und SQL*Plus
SQL> START emp_query.sqlProcedure created.Procedure created.
SQL> START emp_query.sqlProcedure created.Procedure created.
SQL> VARIABLE g_name varchar2(15)SQL> VARIABLE g_salary numberSQL> VARIABLE g_comm number
SQL> VARIABLE g_name varchar2(15)SQL> VARIABLE g_salary numberSQL> VARIABLE g_comm number
SQL> EXECUTE query_emp (7654, :g_name, :g_salary, 2 :g_comm)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> EXECUTE query_emp (7654, :g_name, :g_salary, 2 :g_comm)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> PRINT g_nameG_NAME---------------MARTIN
SQL> PRINT g_nameG_NAME---------------MARTIN
© Prof. T. Kudraß, HTWK Leipzig
Parameter Passing
Position Namen Kombination aus beiden
© Prof. T. Kudraß, HTWK Leipzig
Parameter Passing (Beispielprozedur)
SQL> CREATE OR REPLACE PROCEDURE add_dept 1 (v_name IN dept.dname%TYPE DEFAULT 'unknown', 2 v_loc IN dept.loc%TYPE DEFAULT 'unknown') 3 IS 4 BEGIN 5 INSERT INTO dept 6 VALUES (dept_deptno.NEXTVAL, v_name, v_loc); 7 END add_dept; 8 /
SQL> CREATE OR REPLACE PROCEDURE add_dept 1 (v_name IN dept.dname%TYPE DEFAULT 'unknown', 2 v_loc IN dept.loc%TYPE DEFAULT 'unknown') 3 IS 4 BEGIN 5 INSERT INTO dept 6 VALUES (dept_deptno.NEXTVAL, v_name, v_loc); 7 END add_dept; 8 /
© Prof. T. Kudraß, HTWK Leipzig
Parameter Passing (Beispiele)
SQL> begin 2 add_dept; 3 add_dept ( 'TRAINING', 'NEW YORK'); 4 add_dept ( v_loc => 'DALLAS', v_name =>
'EDUCATION') ; 5 add_dept ( v_loc => 'BOSTON') ; 6 end; 7 /PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> begin 2 add_dept; 3 add_dept ( 'TRAINING', 'NEW YORK'); 4 add_dept ( v_loc => 'DALLAS', v_name =>
'EDUCATION') ; 5 add_dept ( v_loc => 'BOSTON') ; 6 end; 7 /PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC------ -------------- ------------- ... ... ... 41 unknown unknown 42 TRAINING NEW YORK 43 EDUCATION DALLAS 44 unknown BOSTON
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC------ -------------- ------------- ... ... ... 41 unknown unknown 42 TRAINING NEW YORK 43 EDUCATION DALLAS 44 unknown BOSTON
© Prof. T. Kudraß, HTWK Leipzig
Löschen von Prozeduren
Mit SQL*Plus:Drop der serverseitigen Prozedur
Beispiel
SyntaxDROP PROCEDURE procedure_nameDROP PROCEDURE procedure_name
SQL> DROP PROCEDURE raise_salary;Procedure dropped.
SQL> DROP PROCEDURE raise_salary;Procedure dropped.
© Prof. T. Kudraß, HTWK Leipzig
Zusammenfassung Prozeduren
Eine Prozedur ist ein benannter PL/SQL Block, der eine Aktion ausführt.
Parameter zum Übermitteln der Daten von der Aufrufumgebung zur Prozedur.
Prozeduren können von jedem Tool oder Sprache aufgerufen werden, die PL/SQL unterstützen.
Prozeduren können als Bausteine für eine Applikation dienen.
© Prof. T. Kudraß, HTWK Leipzig
Gespeicherte Funktionen
Eine Funktion ist ein benannter PL/SQL Block, der einen Wert zurückliefert.
Eine Funktion kann in der Datenbank als Datenbankobjekt für wiederholte Ausführung gespeichert werden.
Eine Funktion kann als Teil eines Ausdrucks aufgerufen werden.
© Prof. T. Kudraß, HTWK Leipzig
Erzeugen neuer Funktionen
CREATE [OR REPLACE] FUNCTION function_name (argument1 [mode1] datatype1, argument2 [mode2] datatype2, . . .RETURN datatypeIS|ASPL/SQL Block;
CREATE [OR REPLACE] FUNCTION function_name (argument1 [mode1] datatype1, argument2 [mode2] datatype2, . . .RETURN datatypeIS|ASPL/SQL Block;
Syntax
© Prof. T. Kudraß, HTWK Leipzig
Erzeugen neuer Funktionen (Beispiel)
SQL> CREATE OR REPLACE FUNCTION get_sal 2 (v_id IN emp.empno%TYPE) 3 RETURN NUMBER 4 IS 5 v_salary emp.sal%TYPE :=0; 6 BEGIN 7 SELECT sal 8 INTO v_salary 9 FROM emp 10 WHERE empno = v_id; 11 RETURN (v_salary); 12 END get_sal; 13 /
SQL> CREATE OR REPLACE FUNCTION get_sal 2 (v_id IN emp.empno%TYPE) 3 RETURN NUMBER 4 IS 5 v_salary emp.sal%TYPE :=0; 6 BEGIN 7 SELECT sal 8 INTO v_salary 9 FROM emp 10 WHERE empno = v_id; 11 RETURN (v_salary); 12 END get_sal; 13 /
SQL*Plus
© Prof. T. Kudraß, HTWK Leipzig
Ausführen von Funktionen
–Aufrufen einer Funktion als Teil eines PL/SQL Ausdrucks.
–Erzeugen einer Host-Variable, um den Rückgabewert aufzunehmen.
–Ausführen der Funktion. Host-Variable wird belegt mit dem RETURN Wert.
SQL> VARIABLE g_salary numberSQL> VARIABLE g_salary number
SQL> EXECUTE :g_salary := get_sal(7934)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> EXECUTE :g_salary := get_sal(7934)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
© Prof. T. Kudraß, HTWK Leipzig
Wo können benutzerdefinierter Funktionen aufgerufen werden?
Select-Liste in einem SELECT Befehl Bedingung in einer WHERE oder HAVING Klausel CONNECT BY, START WITH, ORDER BY, und
GROUP BY Klausel VALUES Klausel eines INSERT Befehls SET Klausel eines UPDATE Befehls
© Prof. T. Kudraß, HTWK Leipzig
Restriktionen für benutzerdefinierte Funktionen
Muß als gespeicherte Funktion vorliegen. Nur IN Parameter erlaubt. Erlaubte Datentypen: CHAR, DATE, oder NUMBER -
keine PL/SQL Typen wie BOOLEAN, RECORD, oder TABLE.
Rückgabe-Typ muß ein interner Typ des Oracle Server sein.
INSERT, UPDATE, oder DELETE verboten. Keine Aufrufe von Subprogrammen, die diese
Restriktion verletzen.
© Prof. T. Kudraß, HTWK Leipzig
Löschen von Funktionen
•Mit SQL*PlusSyntax
Beispiel
SQL> DROP FUNCTION get_salary;Function dropped.
SQL> DROP FUNCTION get_salary;Function dropped.
DROP FUNCTION function_nameDROP FUNCTION function_name
•Procedure Builder (Löschen clientseitiger und serverseitiger Funktionen möglich)
© Prof. T. Kudraß, HTWK Leipzig
Vergleich von Prozeduren und Funktionen
Prozedur
Ausführen als PL/SQL Befehl
Kein RETURN Datentyp
Kann einen oder meh-rere Werte zurückgeben
Funktion
Aufruf als Teil eines Ausdrucks
RETURN Datentyp
Muß einen Wert zurückliefern
© Prof. T. Kudraß, HTWK Leipzig
Packages: Überblick
Gruppierung logisch zusammenhängender PL/SQL-Typen, Konstrukte (Cursor, Exeptions, Variable) und Subprogramme
Besteht aus zwei Teilen:– Spezifikation– Rumpf
Kann nicht aufgerufen, parametrisiert oder geschachtelt werden
Erlaubt Oracle, gleichzeitig mehrere Objekte in den Speicher einzulesen
© Prof. T. Kudraß, HTWK Leipzig
Vorteile von Packages
Information Hiding und Kapselung– Objekte eines Packages benutzbar, ohne interne
Implementierung zu kennen
Top-Down Design– Kopf kann vor dem Rumpf implementiert werden, d.h. Nutzung
der deklarierten Objekte schon möglich
Objektpersistenz– Objekte, die im Kopf deklariert werden, bleiben für die Dauer
der Sitzung erhalten
© Prof. T. Kudraß, HTWK Leipzig
Vorteile von Packages (Forts.)
Bessere Performance– Package wird als ganzes in den Hauptspeicher
geladen, spart weitere I/Os
Overloading– Mehrere Prozeduren und Funktionen mit dem
gleichen Namen im Package, jeweils mit unterschiedlichen Parametern (Anzahl, Typ)
© Prof. T. Kudraß, HTWK Leipzig
Beispiel-Package: COMM_PACKAGE
G_COMMG_COMM
RESET_COMMRESET_COMMprocedure declarationprocedure declaration
VALIDATE_COMMVALIDATE_COMMfunction definitionfunction definition
Package-Package-SpezifikationSpezifikation
Package-Package-RumpfRumpf
1
2
3
2RESET_COMMRESET_COMMprocedure definitionprocedure definition
© Prof. T. Kudraß, HTWK Leipzig
Beispiel Package-Spezifikation
SQL>CREATE OR REPLACE PACKAGE comm_package IS
2 g_comm NUMBER := 10; --initialized to 10
3 PROCEDURE reset_comm
4 (v_comm IN NUMBER);
5 END comm_package;
6 /
© Prof. T. Kudraß, HTWK Leipzig
Beispiel Package-RumpfSQL>CREATE OR REPLACE PACKAGE BODY comm_package IS 2 FUNCTION validate_comm 3 (v_comm IN NUMBER) RETURN BOOLEAN 4 IS 5 v_max_comm NUMBER; 6 BEGIN 7 SELECT MAX(comm) 8 INTO v_max_comm 9 FROM emp; 10 IF v_comm > v_max_comm THEN RETURN(FALSE); 11 ELSE RETURN(TRUE); 12 END IF; 13 END validate_comm; . . . -- nächste Folie
© Prof. T. Kudraß, HTWK Leipzig
Beispiel Package-Rumpf (Forts.)
14 PROCEDURE reset_comm 15 (v_comm IN NUMBER) 16 IS 17 v_valid BOOLEAN; 18 BEGIN 19 v_valid := validate_comm(v_comm); 20 IF v_valid = TRUE THEN 21 g_comm := v_comm; 22 ELSE 23 RAISE_APPLICATION_ERROR 24 (-20210,'Invalid commission'); 25 END IF; 26 END reset_comm; 27 END comm_package; 28 /
© Prof. T. Kudraß, HTWK Leipzig
Aufruf von Package-Konstrukten
Beispiel1: Aufruf einer Package-Prozedur aus SQL*Plus.
Beispiel2: Aufruf einer Package-Prozedur in einem anderen Schema.
SQL> EXECUTE comm_package.reset_comm(1500);
SQL> EXECUTE scott.comm_package.reset_comm(1500);
© Prof. T. Kudraß, HTWK Leipzig
Löschen von Packages
DROP PACKAGE package_nameDROP PACKAGE package_name
Löschen der Package-Spezifikation und Löschen der Package-Spezifikation und des Rumpfes: des Rumpfes:
Löschen nur des Package-Rumpfes: Löschen nur des Package-Rumpfes:
DROP PACKAGE BODY package_name DROP PACKAGE BODY package_name
© Prof. T. Kudraß, HTWK Leipzig
Erzeugen von Triggern
Trigger-Zeitpunkt: BEFORE oder AFTER Auslösendes Event: INSERT oder UPDATE oder
DELETE Tabellen-Name: ON Tabelle Trigger-Type: Zeilen- oder befehlsorientiert WHEN-Klausel: Einschränkende Bedingung Trigger-Rumpf: DECLARE
BEGINEND;
© Prof. T. Kudraß, HTWK Leipzig
Auslösen von Triggern
EMPNOEMPNO
78397839
76987698
77887788
ENAMEENAME
KINGKING
BLAKEBLAKE
SMITHSMITH
DEPTNODEPTNO
3030
3030
3030
BEFORE statement triggerBEFORE statement trigger
BEFORE row triggerBEFORE row triggerAFTER row triggerAFTER row triggerBEFORE row triggerBEFORE row triggerAFTER row triggerAFTER row triggerBEFORE row triggerBEFORE row triggerAFTER row triggerAFTER row trigger
AFTER statement triggerAFTER statement trigger
© Prof. T. Kudraß, HTWK Leipzig
Befehlsorientierter Trigger (Syntax)
CREATE [OR REPLACE] TRIGGER trigger_nametiming event1 [OR event2 OR event3]ON table_name PL/SQL block;
CREATE [OR REPLACE] TRIGGER trigger_nametiming event1 [OR event2 OR event3]ON table_name PL/SQL block;
© Prof. T. Kudraß, HTWK Leipzig
Befehlsorientierter BEFORE-Trigger Beispiel
SQL> CREATE OR REPLACE TRIGGER secure_emp
2 BEFORE INSERT ON emp
3 BEGIN
4 IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN'))
5 OR (TO_CHAR(sysdate,'HH24')NOT BETWEEN
6 '08' AND '18'
7 THEN RAISE_APPLICATION_ERROR (-20500,
8 'You may only insert into EMP during normal
9 hours.');
10 END IF;
11 END;
12 /
SQL> CREATE OR REPLACE TRIGGER secure_emp
2 BEFORE INSERT ON emp
3 BEGIN
4 IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN'))
5 OR (TO_CHAR(sysdate,'HH24')NOT BETWEEN
6 '08' AND '18'
7 THEN RAISE_APPLICATION_ERROR (-20500,
8 'You may only insert into EMP during normal
9 hours.');
10 END IF;
11 END;
12 /
© Prof. T. Kudraß, HTWK Leipzig
Zeilenorientierter Trigger (Syntax)
CREATE [OR REPLACE] TRIGGER trigger_nametiming event1 [OR event2 OR event3]ON table_name [REFERENCING OLD AS old | NEW AS new]FOR EACH ROW[WHEN condition]PL/SQL block;
CREATE [OR REPLACE] TRIGGER trigger_nametiming event1 [OR event2 OR event3]ON table_name [REFERENCING OLD AS old | NEW AS new]FOR EACH ROW[WHEN condition]PL/SQL block;
© Prof. T. Kudraß, HTWK Leipzig
Zeilenorientierter Trigger (Beispiel)
SQL>CREATE OR REPLACE TRIGGER audit_emp_values
2 AFTER DELETE OR INSERT OR UPDATE ON emp
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO audit_emp_values (user_name,
6 timestamp, id, old_last_name, new_last_name,
7 old_title, new_title, old_salary, new_salary)
8 VALUES (USER, SYSDATE, :old.empno, :old.ename,
9 :new.ename, :old.job, :new.job,
10 :old.sal, :new.sal);
11 END;
12 /
SQL>CREATE OR REPLACE TRIGGER audit_emp_values
2 AFTER DELETE OR INSERT OR UPDATE ON emp
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO audit_emp_values (user_name,
6 timestamp, id, old_last_name, new_last_name,
7 old_title, new_title, old_salary, new_salary)
8 VALUES (USER, SYSDATE, :old.empno, :old.ename,
9 :new.ename, :old.job, :new.job,
10 :old.sal, :new.sal);
11 END;
12 /
Verwendung von old und new-Präfixen beachten!
© Prof. T. Kudraß, HTWK Leipzig
Ausführung von Triggern
1. Ausführung aller befehlsorientierten BEFORE Trigger
2. Für jede betroffene Zeile
a. Ausführung aller zeilenorientierten BEFORE Trigger
b. Ausführung des DML-Befehls und der Integritätsprüfungen
c. Ausführung aller zeilenorientierten AFTER Trigger
3. Ausführung der verzögerten Integritätsprüfungen
4. Ausführung aller befehlsorientierten AFTER Trigger
© Prof. T. Kudraß, HTWK Leipzig
Anwendungen für Trigger
SecuritySecurity Auditing Datenintegrität Referentielle Integrität Replikation von Tabellen Abgeleitete Daten Aufzeichnen von Ereignissen