Define a Procedure inside another Procedure in Oracle

Q

How To Define a Procedure inside Another Procedure in Oracle?

✍: FYIcenter.com

A

Define a procedure inside another procedure is supported by PL/SQL. The following tutorial script shows you an example:

SQL> CREATE OR REPLACE PROCEDURE HR.DBA_WEEK AS
  2    PROCEDURE DBA_TASK (day VARCHAR2) AS
  3    BEGIN
  4      IF day = 'MONDAY' THEN
  5        DBMS_OUTPUT.PUT_LINE('Checking log files.');
  6      ELSIF day = 'FRIDAY' THEN
  7        DBMS_OUTPUT.PUT_LINE('Rebuild indexes.');
  8      ELSE
  9        DBMS_OUTPUT.PUT_LINE('Reading some papers.');
 10      END IF;
 11    END;
 12  BEGIN
 13    DBA_TASK('MONDAY');
 14    DBA_TASK('TUESDAY');
 15  END;
 16  /
 
SQL> EXECUTE DBA_WEEK;
Checking log files.
Reading some papers.

Remember that procedures used inside a procedure must be defined in the declaration block.

 

Introduction to Oracle PL/SQL

⇒⇒Oracle Database Tutorials

2016-10-16, 167👍, 0💬