Store Procedure in Oracle

By: Pankaj Yadav

Store procedures are the sub programs used to perform some actions. Mainly a procedure has two parts Specification and body .The Procedure is created with Create and ends with end procedure. The body of the store procedure stats after the IS or AS.

The Uses of procedures

1. They help us to make program in small manageable parts.

2. They make the code reusable

3. They help us to meet our need

syntax:

Create or replace Procedure [procedure name](Parameter1 [mode] ,..)
As
{Local variable declaration}
Begin
{Procedure statements}
end {procedure name}

Note: Replace is used if the procedure exists previously, replace it with the new one

Modes of Parameters :

There are 3 modes of parameters in store procedure. They are as follows

IN mode parameter is used to pass the value to the called procedure and in program it acts like constant

OUT mode parameter allows us to return the values from store procedure. In a store procedure the OUT parameter s are act like uninitialized parameters. So we cannot assign value to these

INOUT mode parameters allow both to return the value from subprogram

We Can execute a store procedure with the keyword ‘exec’

syntax for executing store procedure :

exec  {Procedure name}(Parameters)

Example:

create procedure minno(firstno in Number, secondno in Number)
returning integer;
 if (firstno < secondno) then
  return firstno;
 else
  return secondno;
 end if;
end minno

if we execute this procedure

exec  minno(20,10)

Output will be

10