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