Creating stored procedures

A procedure is a derivative of a PL/SQL block that has a name and is stored persistently within the database. It is the schema object that is primarily used to implement business logic on the server side. A procedure promotes a modular programming technique by breaking down complex logic into simple routines.

The key features of stored procedures are:

• A procedure must be invoked from the executable section of a PL/SQL block as a procedural statement. You can also execute it directly from SQLPLUS using the EXECUTE statement. Note that a procedure can not be called from a SELECT statement.

• A procedure can optionally accept parameters in IN, OUT, or IN OUT mode.

• A procedure cannot return a value. The only way 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 skip the further execution of the program and exit control.

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 to 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 value to the calling environment
Parameters are passed by reference	Parameters are passed by value	Parameters are passed by value
May be a constant, literal, or initialized variable	Uninitialized variable	Initialized variable
Can hold 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 lower case to upper case:

/*Create a procedure to change case of a string */
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 either be executed from SQL*Plus or a PL/SQL block. The P_TO_UPPER procedure can be executed from SQL*Plus.

The following code shows the execution of the procedure from SQL*Plus (note that the parameter is passed using 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.