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:

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.