Creating a Subprocedure v12
The PROCEDURE
clause specified in the declaration section defines and names a subprocedure local to that block.
The term block refers to the SPL block structure consisting of an optional declaration section, a mandatory executable section, and an optional exception section. Blocks are the structures for standalone procedures and functions, anonymous blocks, subprograms, triggers, packages, and object type methods.
The phrase the identifier is local to the block means that the identifier (that is, a variable, cursor, type, or subprogram) is declared within the declaration section of that block and is therefore accessible by the SPL code within the executable section and optional exception section of that block.
Subprocedures can only be declared after all other variable, cursor, and type declarations included in the declaration section. (That is, subprograms must be the last set of declarations.)
PROCEDURE <name> [ (<parameters>) ] { IS | AS } [ PRAGMA AUTONOMOUS_TRANSACTION; ] [ <declarations> ] BEGIN <statements> END [ <name> ];
Where:
name
name
is the identifier of the subprocedure.
parameters
parameters
is a list of formal parameters.
PRAGMA AUTONOMOUS_TRANSACTION
PRAGMA AUTONOMOUS_TRANSACTION
is the directive that sets the subprocedure as an autonomous transaction.
declarations
declarations
are variable, cursor, type, or subprogram declarations. If subprogram declarations are included, they must be declared after all other variable, cursor, and type declarations.
statements
statements
are SPL program statements (the BEGIN - END
block may contain an EXCEPTION
section).
Examples
The following example is a subprocedure within an anonymous block.
DECLARE PROCEDURE list_emp IS v_empno NUMBER(4); v_ename VARCHAR2(10); CURSOR emp_cur IS SELECT empno, ename FROM emp ORDER BY empno; BEGIN OPEN emp_cur; DBMS_OUTPUT.PUT_LINE('Subprocedure list_emp:'); DBMS_OUTPUT.PUT_LINE('EMPNO ENAME'); DBMS_OUTPUT.PUT_LINE('----- -------'); LOOP FETCH emp_cur INTO v_empno, v_ename; EXIT WHEN emp_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename); END LOOP; CLOSE emp_cur; END; BEGIN list_emp; END;
Invoking this anonymous block produces the following output:
Subprocedure list_emp: EMPNO ENAME ----- ------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER
The following example is a subprocedure within a trigger.
CREATE OR REPLACE TRIGGER dept_audit_trig AFTER INSERT OR UPDATE OR DELETE ON dept DECLARE v_action VARCHAR2(24); PROCEDURE display_action ( p_action IN VARCHAR2 ) IS BEGIN DBMS_OUTPUT.PUT_LINE('User ' || USER || ' ' || p_action || ' dept on ' || TO_CHAR(SYSDATE,'YYYY-MM-DD')); END display_action; BEGIN IF INSERTING THEN v_action := 'added'; ELSIF UPDATING THEN v_action := 'updated'; ELSIF DELETING THEN v_action := 'deleted'; END IF; display_action(v_action); END;
Invoking this trigger produces the following output:
INSERT INTO dept VALUES (50,'HR','DENVER'); User enterprisedb added dept on 2016-07-26