PL/SQL Procedure Example

PL/SQL Procedure Example

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • PLSQL
  • 3 mins read

A procedure is similar to a PL/SQL block, but it has a name and is stored in the database permanently. The schema object is the main way that business logic is implemented on the server side. By breaking up complex logic into simple routines, a procedure helps a modular programming method.

The most important things about stored procedures are:

  • A procedural statement must be used in the executable part of a PL/SQL block to call a procedure. You can also use the EXECUTE statement in SQLPLUS to run it right away. Remember that you can't call a procedure from a SELECT statement.
  • A procedure can accept parameters in IN, OUT, or IN OUT mode, but it doesn't have to.
  • A procedure cannot return a value. The OUT parameters are the only way for a procedure to return a value. The RETURN [value] statement is not an option. With the RETURN statement in a procedure, you can stop the program from running and leave control.

The difference between the IN, OUT, and IN OUT parameters is shown in the table below:

IN OUT IN OUT 
Default parameter mode.Has to be explicitly defined. Has to be explicitly defined. 
The value of a parameter is given to a program by the environment that calls it.Parameter sends a value back to the environment that called it.The calling environment can send a value to the program using a parameter, or the program can send a value back 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]; 

PL/SQL Procedure Example

The following procedure changes the case of the string you give it 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 PL/SQL Procedure 

Either SQL*Plus or a PL/SQL block can be used to run a procedure. SQL*Plus can be used to run the P_TO_UPPER procedure.

The following code shows how the procedure is run from SQL*Plus (note that the parameter is passed using a bind variable):

SET SERVEROUTPUT ON;

/*Declare a session variable for the input*/
VARIABLE M_STR VARCHAR2(50);
/*Assign a test value to the session variable*/
EXECUTE :M_STR := 'My first PLSQL procedure';

PL/SQL PROCEDURE successfully completed.
/*Call the procedure P_TO_UPPER*/
EXECUTE P_TO_UPPER(:M_STR);
Input STRING IN Upper CASE: MY FIRST PLSQL PROCEDURE 
PL/SQL PROCEDURE successfully completed.

Executing Procedure in PL/SQL Block:

BEGIN
/*Call the P_TO_UPPER procedure*/
P_TO_UPPER ('My first PLSQL procedure');
END;
/

Output:

Input string in Upper case : MY FIRST PLSQL PROCEDURE