A procedure is a derivative of PL/SQL block structure which is identified by its own specific name. It is stored as a schema object in the database and implements business logic in the applications. For this reason, procedures are often referred to as Business Managers of PL/SQL which not only maintain the business logic repository, but also demonstrate solution scalability and a modular way of programming.
The characteristics of procedures are as follows:
A procedure can neither be called from a
SELECTstatement nor can it appear as a right-hand operand in an assignment statement. It has to be invoked from the executable section of a PL/SQL block as a procedural statement.They can optionally accept parameters in
IN,OUT, orINOUTmode.This implies that the only possibility for a procedure to return a value is through
OUTparameters, but not through theRETURN[value]statement. TheRETURNstatement in a procedure is used to exit the procedure and skip the further execution.
For recapitulation, the following table differentiates between the IN, OUT, and IN OUT parameters:
The syntax for a procedure is as follows:
CREATE [OR REPLACE] PROCEDURE [Procedure Name] [Parameter List] [AUTHID DEFINER | CURRENT_USER] IS [Declaration Statements] BEGIN [Executable Statements] EXCEPTION [Exception handlers] END [Procedure Name];
The following standalone procedure converts the case of the input string from lowercase to uppercase:
/*Create a procedure to convert the string from lower case to upper case*/CREATE OR REPLACE PROCEDURE P_TO_UPPER (P_STR VARCHAR2) IS/*Declare the local variables*/L_STR VARCHAR2(50); BEGIN/*Convert the case using UPPER function*/L_STR := UPPER(P_STR);/*Display the output with appropriate message*/DBMS_OUTPUT.PUT_LINE('Input string in Upper case : '||L_STR); END; / Procedure created.
A procedure can be either executed from SQL*Plus or from a PL/SQL block. The P_TO_UPPER procedure can be executed from SQL*Plus.
The following illustration shows the execution of the procedure from SQL*Plus (note that the parameter is passed using the bind variable):
/*Enable the SERVEROUTPUT parameter to print the results in the environment*/SQL> SET SERVEROUTPUT ON /*Declare a session variable for the input*/ SQL> VARIABLE M_STR VARCHAR2(50);/*Assign a test value to the session variable*/SQL> EXECUTE :M_STR := 'My first PLSQL procedure'; PL/SQL procedure successfully completed./*Call the procedure P_TO_UPPER*/SQL> EXECUTE P_TO_UPPER(:M_STR); Input string in Upper case : MY FIRST PLSQL PROCEDURE PL/SQL procedure successfully completed.
The P_TO_UPPER procedure can be called as a procedural statement within an anonymous PL/SQL block:
/*Enable the SERVEROUTPUT parameter to print the results in the environment*/SQL> SET SERVEROUTPUT ON /*Start a PL/SQL block*/ SQL> BEGIN/*Call the P_TO_UPPER procedure*/P_TO_UPPER ('My first PLSQL procedure'); END; / Input string in Upper case : MY FIRST PLSQL PROCEDURE PL/SQL procedure successfully completed.