Prozeduren - Datenbanken
Werbung
Überblick
• Eine Prozedur ist ein PL/SQL Block mit
Namen, der Aktionen durchführt
Prozeduren
Copyright © Oracle Corporation, 1998. All rights reserved.
Syntax zur Prozedurerzeugung
CREATE [OR REPLACE] PROCEDURE procedure_name
(argument1 [mode1] datatype1,
argument2 [mode2] datatype2,
. . .
IS [AS]
-- kein DECLARE
PL/SQL Block;
• Eine Prozedur kann als Objekt in der
Datenbank im Data Dictionary
gespeichert und wiederholt ausgeführt
werden.
SQL2 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Entwicklung von Stored
Procedures
Edit
System
editor
1
Text
file
Storage in
database
Source
code
Compile
p-code
Execute
SQL3 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
SQL4 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
2
Erzeugung Storage Procedures
mit SQL*Plus
Prozedur-Parameter Modi
Procedure
1. Text für das CREATE PROCEDURE
Statement in einen einfachen Editor
eingeben und als Skriptfile speichern.
(.sql Extension).
2. Als Skriptfile testen
Calling
environment
IN parameter
OUT parameter
IN OUT parameter
(DECLARE)
BEGIN
3. Umschreiben zur Prozedur
EXCEPTION
END;
SQL5 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Parameter Modi für formale Parameter
IN
OUT
IN OUT
Default
spezifizieren
spezifizieren
Wert geht ins
Unterprogramm ein
Rückgabe zum
rufenden
Programm
Wert geht ein ins
Unterprogramm,
Rückgabe zum rufenden Programm
Formaler Parameter
kann Konstante
sein
Nicht initialisierte
Variable
Initialisierte
Variable
Aktueller Parameter
kann sein : Literal,
Ausdruck, Konstante oder initialisierte Variable
Muss eine Variable Muss eine Variable
sein
sein
SQL7 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
SQL6 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
IN Parameter: Beispiel
7369
v_id
SQL> CREATE OR REPLACE PROCEDURE raise_salary
2 (v_id in emp.empno%TYPE)
3 IS
-- beachte kein DECLARE
4 BEGIN
5
UPDATE emp
6
SET
sal = sal * 1.10
7
WHERE empno = v_id;
8 END raise_salary;
9 /
Procedure created.
SQL> EXECUTE raise_salary (7369)
PL/SQL procedure successfully completed.
SQL8 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
OUT Parameter: Beispiel
Rufende Ebene
QUERY_EMP Prozedur
7654
v_id
MARTIN v_name
1250
v_salary
1400
v_ comm
SQL9 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
OUT Parameter und SQL*Plus
OUT Parameter: Beispiel
SQL>
1
2
3
4
5
6
7
8
9
10
11
12
CREATE OR REPLACE PROCEDURE query_emp
(v_id
IN
emp.empno%TYPE,
v_name
OUT emp.ename%TYPE,
v_salary OUT emp.sal%TYPE,
v_comm
OUT emp.comm%TYPE)
IS
BEGIN
SELECT
ename, sal, comm
INTO
v_name, v_salary, v_comm
FROM
emp
WHERE
empno = v_id;
END query_emp;
/
SQL10 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
IN OUT Parameter
Rufende Ebene
SQL> START emp_query.sql
Procedure created.
'8006330575'
SQL> VARIABLE g_name
SQL> VARIABLE g_salary
SQL> VARIABLE g_comm
varchar2(15)
number
number
SQL> EXECUTE query_emp (7654, :g_name, :g_salary,
2 :g_comm)
PL/SQL procedure successfully completed.
SQL> PRINT g_name
G_NAME
--------------MARTIN
SQL11 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
FORMAT_PHONE Prozedur
'(800)633-0575'
v_phone_no
SQL> CREATE OR REPLACE PROCEDURE format_phone
2 (v_phone_no IN OUT VARCHAR2)
3 IS
4 BEGIN
5
v_phone_no := '(' || SUBSTR(v_phone_no,1,3) ||
6
')' || SUBSTR(v_phone_no,4,3) ||
7
'-' || SUBSTR(v_phone_no,7);
8 END format_phone;
9 /
SQL12 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
FORMAT_PHONE von SQL*Plus
aufrufen
SQL>VARIABLE g_phone_no varchar2(15)
SQL> BEGIN :g_phone_no := '8006330575'; END;
2 /
PL/SQL procedure successfully completed.
Arten der Parameterübergabe
• Position
• Mit Namen
• Kombination
SQL> EXECUTE format_phone (:g_phone_no)
PL/SQL procedure successfully completed.
SQL> PRINT g_phone_no
G_PHONE_NO
--------------(800)633-0575
SQL13 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Parameterübergabe: Beispiel
Prozedur
SQL>
1
2
3
4
5
6
7
8
CREATE OR REPLACE PROCEDURE add_dept
(v_name IN dept.dname%TYPE DEFAULT 'unknown',
v_loc
IN dept.loc%TYPE
DEFAULT 'unknown')
IS
BEGIN
INSERT INTO dept
VALUES (dept_deptno.NEXTVAL, v_name, v_loc);
END add_dept;
/
SQL15 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
SQL14 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Beispiel:Parameterübergabe
SQL>
2
3
4
begin
add_dept;
add_dept ( 'TRAINING', 'NEW YORK');
add_dept ( v_loc => 'DALLAS', v_name =>
'EDUCATION') ;
5 add_dept ( v_loc => 'BOSTON') ;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SELECT * FROM dept;
DEPTNO
-----...
41
42
43
44
DNAME
-------------...
unknown
TRAINING
EDUCATION
unknown
LOC
------------...
unknown
NEW YORK
DALLAS
BOSTON
SQL16 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Aufrufen einer Prozedur aus
einem anonymen PL/SQL Block
DECLARE
v_id NUMBER := 7900;
BEGIN
raise_salary(v_id);
COMMIT;
...
--Prozeduraufruf
END;
SQL17 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Prozeduren löschen
• In SQL*Plus:
server-side procedures mit DROP
Aufruf einer Prozedur aus einer
Stored Procedure
SQL> CREATE OR REPLACE PROCEDURE process_emps
2 IS
3
CURSOR emp_cursor IS
4
SELECT empno
5
FROM
emp;
6 BEGIN
7
FOR emp_rec IN emp_cursor LOOP
8
raise_salary(emp_rec.empno); --Prozeduraufruf
9
END LOOP;
10 COMMIT;
11 END process_emps;
12 /
SQL18 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Löschen von Server-Side
Procedures
SQL*Plus verwenden:
• Syntax
DROP PROCEDURE procedure_name
• Beispiel
SQL> DROP PROCEDURE raise_salary;
Procedure dropped.
SQL19 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
SQL20 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
