Stored Procedure

It is a named Pl/Sql block which is getting stored on database side and supposes to perform one or more specific task and that too repetitively. Procedure may or may not return any value. There are two major sections in a procedure generally known as header and body. The header is all about procedure-Name and parameters list which is IN/OUT parameter to procedure.

The body is having declaration part, execution part and exception part (optional) as same as in any Pl/Sql block.

Three possible ways to pass the parameter to procedure:-

i)                    IN type parameter: to send values to stored procedures. We cannot change its value inside the procedure.

ii)                  OUT type parameter: to get values from stored procedures. This is similar to a return type in functions.

iii)                IN OUT parameter: to send values and get values from stored procedures. This parameter is used if the value of the IN parameter can be changed in the calling program and after change to return the same.

Note: - PL/Sql is not case sensitive.

Syntax:-

                Create [or Replace] Procedure proc_name [Parameter’s list]

            IS

                        Declaration part

            Begin

                        Execution part

            Exception

                        Exception part

            End;

Let us understand the above procedure definition as below:

IS:-

                It is pretty much similar to Declare in PL/SQL block. Anything between IS and Begin is Declaration part. It is an optional part.

Create [or Replace]:-

                If we are writing as above it means that procedure will be created if no other procedure with the same name exists otherwise the existing procedure will be replaced by the current one. If you want to create the procedure only and in future even you think no need of procedure change may require then no need to write [or Replace] part.

Note: It is always a good practice to write both the things together.

[Parameter’s list]:-

                This simply shows the number of parameters which procedure is looking for as mentioned above of three categories (IN, OUT, IN OUT). It is an optional part.

Let us see an example of procedure as below:-

create or replace PROCEDURE SP_Test_proc (emp_id  IN VARCHAR2,

        address     OUT VARCHAR2,

        status      OUT INTEGER)

BEGIN

DBMS_OUTPUT.put_line ('inside');  

SELECT address ad, status s FROM emp

              WHERE EMP_ID = emp_id;

address := ad;

status := s;

DBMS_OUTPUT.put_line ('outside');  

END SP_Test_proc;

After creation of a procedure we can use it at different places and the syntax is as below:

On SQL prompt: - Execute proc_name

Within different procedure: - proc_name

The above created procedure can be called from SQL prompt as a Pl/Sql block as below:-

--Activate client processing of dbms_output buffer

SET SERVEROUTPUT ON

--Anonymous block, declare variable, call procedure, print resulting value

Declare

                ad VARCHAR2;

        st INTEGER;

Begin

        SP_Test_proc(101, ad, st);

        Dbms_output.put_line(ad);

        Dbms_output.put_line(st);

End; /

Note: - Here slash / indicates that Pl/Sql block is done and ready to execute.