Procedures

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 SELECT statement 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, or IN OUT mode.

• This implies that the only possibility for a procedure to return a value is through OUT parameters, but not through the RETURN [value] statement. The RETURN statement 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:

IN	OUT	IN OUT
Default parameter mode	Has to be explicitly defined	Has to be explicitly defined
Parameter's value is passed into the program from the calling environment	Parameter returns a value back to the calling environment	Parameter may pass a value from the calling environment to the program or return a value to the calling environment
Parameters are passed by reference	Parameters are passed by value	Parameters are passed by value
May be constant, literal, or initialized variable	Uninitialized variable	Initialized variable
Can hold the default value	Default value cannot be assigned	Default value cannot be assigned

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.

Executing a procedure

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.