Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Pass Parameters to Procedures

By: FYIcenter.com

(Continued from previous topic...)

How To Pass Parameters to Procedures?

Store procedures or functions can take parameters. You need to define parameters while defining the procedure, and providing values to parameters while calling the procedure. The script below shows you how to do this:

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

SQL> EXECUTE DBA_TASK('MONDAY');
Checking log files.

SQL> EXECUTE DBA_TASK('SUNDAY');
Reading some papers.

As you can see, procedures with parameters can make procedures more flexible.

(Continued on next topic...)

1. What Is PL/SQL?
2. What Are the Types PL/SQL Code Blocks?
3. How To Define an Anonymous Block?
4. How Many Anonymous Blocks Can Be Defined?
5. How To Run the Anonymous Block Again?
6. What Is a Stored Program Unit?
7. How To Create a Stored Program Unit?
8. How To Execute a Stored Program Unit?
9. How Many Data Types Are Supported?
10. What Are the Execution Flow Control Statements?
11. How To Use SQL Statements in PL/SQL?
12. How To Process Query Result in PL/SQL?
13. How To Create an Array in PL/SQL?
14. How To Manage Transaction Isolation Level?
15. How To Pass Parameters to Procedures?
16. How To Define a Procedure inside Another Procedure?
17. What Do You Think about PL/SQL?