Procedure in PL/SQL

What is a Procedure in PL/SQL? A procedure or in simple a proc is a named PL/SQL block which performs one or more specific task. This is similar to a procedure in other programming languages. A procedure has a header and a body. The header consists of the name of the procedure and the parameters or variables passed to the procedure. The body consists or declaration section, execution section and exception section similar to a general PL/SQL Block. A procedure is similar to an anonymous PL/SQL Block but it is named for repeated usage. We can pass parameters to procedures in three ways. 1) IN-parameters 2) OUT-parameters 3) IN OUT-parameters A procedure may or may not return any value. 1 IN An IN parameter lets you pass a value to the subprogram. It is a read-only parameter. Inside the subprogram, an IN parameter acts like a constant. It cannot be assigned a value. You can pass a constant, literal, initialized variable, or expression as an IN parameter. You can also initialize it to a default value; however, in that case, it is omitted from the subprogram call. It is the default mode of parameter passing. Parameters are passed by reference. 2 OUT An OUT parameter returns a value to the calling program. Inside the subprogram, an OUT parameter acts like a variable. You can change its value and reference the value after assigning it. The actual parameter must be variable and it is passed by value. 2 IN OUT An IN OUT parameter passes an initial value to a subprogram and returns an updated value to the caller. It can be assigned a value and its value can be read. The actual parameter corresponding to an IN OUT formal parameter must be a variable, not a constant or an expression. Formal parameter must be assigned a value. Actual parameter is passed by value. Creating a Procedure A procedure is created with the CREATE OR REPLACE PROCEDURE statement. The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows: CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter_name [IN | OUT | IN OUT] type [, ...])] {IS | AS} BEGIN < procedure_body > END procedure_name; Where, 1)  procedure-name specifies the name of the procedure. 2) [OR REPLACE] option allows modifying an existing procedure. 3) The optional parameter list contains name, mode and types of the parameters. IN represents that value will be passed from outside and OUT represents that this parameter will be used to return a value outside of the procedure. 4) procedure-body contains the executable part. 5) The AS keyword is used instead of the IS keyword for creating a standalone procedure. Example: The following example creates a simple procedure that displays the string 'Hello World!' on the screen when executed. CREATE OR REPLACE PROCEDURE greetings AS BEGIN dbms_output.put_line('Hello World!'); END; / When above code is executed using SQL prompt, it will produce the following result: Procedure created. Executing a Standalone Procedure A standalone procedure can be called in two ways: --> Using the EXECUTE keyword --> Calling the name of the procedure from a PL/SQL block The above procedure named 'greetings' can be called with the EXECUTE keyword as: EXECUTE greetings; The above call would display: Hello World PL/SQL procedure successfully completed. The procedure can also be called from another PL/SQL block: BEGIN greetings; END; / The above call would display: Hello World PL/SQL procedure successfully completed.

DROP PROCEDURE

Once you have created your procedure in Oracle, you might find that you need to remove it from the database.

SYNTAX

The syntax to a drop a procedure in Oracle is:

DROP PROCEDURE procedure_name;

procedure_name is the name of the procedure that you wish to drop.

Example 1

This program finds the minimum of two values, here procedure takes two numbers using IN mode and returns their minimum using OUT parameters.

DECLARE
   a number;
   b number;
   c number;

PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
BEGIN
   IF x < y THEN
      z:= x;
   ELSE
      z:= y;
   END IF;
END; 

BEGIN
   a:= 23;
   b:= 45;
   findMin(a, b, c);
   dbms_output.put_line(' Minimum of (23, 45) : ' || c);
END;
/

When the above code is executed at SQL prompt, it produces the following result:

 Minimum of (23, 45) : 23

PL/SQL procedure successfully completed.

 Example 2

This procedure computes the square of value of a passed value. This example shows how we can use same parameter to accept a value and then return another result.

DECLARE
   a number;
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
  x := x * x;
END; 
BEGIN
   a:= 23;
   squareNum(a); // calls the procedure //
   dbms_output.put_line(' Square of (23): ' || a);
END;
/

When the above code is executed at SQL prompt, it produces the following result:

Square of (23): 529

PL/SQL procedure successfully completed