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.
PROCEDURE name [ (parameters) ]{ IS | AS }
[ PRAGMA AUTONOMOUS_TRANSACTION; ]
[ declarations ]
BEGIN
statements
END [ name ];
Arguments
Argument | Description |
---|---|
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;
The following output is generated by invoking this anonymous block:
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;
The following output is generated by invoking this trigger:
INSERT INTO dept VALUES (50,'HR','DENVER');
User polardb added dept on 2016-07-26