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
EXECUTEstatement. Note that a procedure can not be called from aSELECTstatement.A procedure can optionally accept parameters in
IN,OUT, orINOUTmode.A procedure cannot return a value. The only way for a procedure to return a value is through
OUTparameters, but not through theRETURN[value]statement. TheRETURNstatement 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:
|
|
|
|
|---|---|---|
|
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.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.