A procedure is a group of PL/SQL statements that you can call by name which performs one or more specific task.

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.

The syntax for creating a procedure:

CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
BEGIN 
 < procedure_body > 
END procedure_name;

How to pass parameters in procedure:

We can pass parameters to procedures in three ways.

1) IN-parameters: The IN parameter can be referenced by the procedure or function. The value of the parameter cannot be overwritten by the procedure or the function. 
2) OUT-parameters: The OUT parameter cannot be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function. 
3) IN OUT-parameters: The INOUT parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

• A procedure may or may not return any value.
• IS – marks the beginning of the body of the procedure and is similar to DECLARE in anonymous PL/SQL Blocks. The code between IS and BEGIN forms the Declaration section.
• The AS keyword is used instead of the IS keyword for creating a standalone procedure.

How to execute a Stored Procedure

There are two ways to execute a procedure.

1) From the SQL prompt.

EXECUTE [or EXEC] procedure_name;

2) Within another procedure – simply use the procedure name.

procedure_name;

PL/SQL Create Procedure

CREATE OR REPLACE PROCEDURE 

remove_cust (cust_id NUMBER) IS

 BEGIN
 DELETE FROM CUSTOMERS
 WHERE CUSTOMERS.id = remove_cust.cust_id;

 DBMS_OUTPUT.PUT_LINE('Customer deleted');
 END;
/

Procedure REMOVE_CUST compiled

SET SERVEROUTPUT ON
BEGIN
remove_cust(2);

END;

Customer deleted

PL/SQL procedure successfully completed.

To view the Procedure availability

select object_name,object_type,status 
from USER_OBJECTS WHERE
object_name='REMOVE_CUST';

Recompile an Existing Procedure.

ALTER PROCEDURE remove_cust
 COMPILE;

Dropping Procedure.

Similar to dropping a table, the procedure can also be dropped.

DROP PROCEDURE remove_cust;

IN & OUT Mode Example 1

This program finds the minimum of two values. Here, the procedure takes two numbers using the IN mode and returns their minimum using the 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:= 25; 
 b:= 40; 
 findmin(a, b, c); 
 dbms_output.put_line(' Minimum of (25, 40) : ' || c); 
END; 
/


Minimum of (25, 40) : 25

PL/SQL procedure successfully completed.

IN & OUT Mode Example 2

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

DECLARE 
 a number; 
PROCEDURE square(x IN OUT number) IS 
BEGIN 
 x := x * x; 
END; 
BEGIN 
 a:= 25; 
 square(a); 
 dbms_output.put_line(' Square of (25): ' || a); 
END; 
/


Square of (25): 625

PL/SQL procedure successfully completed.

Methods for Passing Parameters

Actual parameters can be passed in three ways

Positional notation

It is simply an association of the values by POSITION of the arguments at call time with that of declaration in the header of the procedure creation. The order of the parameters used when executing the procedure should match the order in the procedure header exactly.

Ex: findmin(a, b, c);

Named notation

It is an explicit association using the symbol =>

In Named notation, the order of the parameters are doesn’t matter.

Ex: findmin(x => a, y => b, z => c);

Mixed notation

In mixed notation, you can mix both notations in procedure call; however, the positional notation should precede the named notation.

Ex: findmin(a, b, z => c); — is valid

Ex: findmin(X => a, b, c); — is not valid