Define a Sub Procedure in Oracle

Q

How To Define a Sub Procedure in Oracle?

✍: FYIcenter.com

A

A sub procedure is a named procedure defined and used inside another procedure or function. You need to define a sub procedure in the declaration part of the enclosing procedure or function. Sub procedure definition starts with the PROCEDURE key word. Here is a sample script showing you how to define and use a sub procedure:

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.

 

Creating Oracle PL/SQL Procedures and Functions

⇒⇒Oracle Database Tutorials

2016-10-17, 143👍, 0💬