Stored Procedure

A procedure is a set of instructions(usually combining sql and plsql commands) saved for calling and repeated execution

A procedure is a sub program that performs a specific action. A procedure can be called from any pl/sql block. A procedure has two parts one is Specification and Other is Body

The procedure specification begins with the keyword procedure followed by procedure name and an option list of arguments enclosed with in the parenthesis.

The procedure body begins with is or as and ends with an end statement.

It consists of three parts

1. Declarative Part

2. Execution Part

3. Exception Handling Part

Syntax of Procedure

Create [ Or Replace ] Procedure < Procedure Name > ( Argument1 [Mode] <Data type>, Argument2 [Mode] <data type>,---)

Is / As

Local Variables Declarations;

------------

Begin

Executable Statements;

---------------

[Exception

Exception Handling; ]

End;

/

where Mode refers to the type of arguments such as In , Out or InOut

Modes In Procedures And Functions

1. IN : the In parameter lets the user to pass values to the called sub programs with in the sub program, the IN parameter acts like a constant. There fore it cannot be modified.

1. OUT : The Out parameter lets the user returned values to the calling block. Inside the sub program the out parameter acts like a un initialized variable. Its value can not be assigned to another variable or itself

1. INOUT : the INOUT parameter lets the user pass initial values to the called sub program and return updated values to the calling block

Note : the default mode of an argument is "IN"

Show Errors :

This Command is used to show the errors that are occurred during the procedure creation

Ex : when ever we create a procedure and executed, if it show procedure created with compilation errors, the we can see those errors using the following statement

show Errors

Example :1

Write a procedure to show a simple message

Sol. Steps

1. Write the sub Program

Ed subprocedure1

Create or replace procedure disp is

Begin

Dbms_output.put_line('This is a Sub Program');

End;

/

1. Next, Compile the above procedure to find errors in the procedure, with the following statement

@ subprocedure1

1. Next, Write the main program

Begin

Dbms_output.put_line('This is Main Program');

Disp;

Dbms_output.put_line('Again Continuing the Main Program');

End;

/

1. Next, Run the above main procedure with the following statement

@mainprogram1

output :

SQL> @ mainprogram1

This is Main Program

This is a Sub Program

Again Continuing the Main Program

PL/SQL procedure successfully completed.

Example :2

Write a procedure to show a message when updated any row in a particular table

Sol. Steps

1. Write the sub Program

Ed subprocedure2

Create or replace procedure upd(d number)

is

Begin

update employee set sal=sal+(sal*0.2) where empno=d;

dbms_output.put_line(sql%rowcount||' Record Updated...');

End;

/

1. Next, Compile the above procedure to find errors in the procedure, with the following statement

@ subprocedure2

1. Next, Write the main program

Begin

upd(&empno);

End;

/

1. Next, Run the above main procedure with the following statement

@mainprogram2

output :

SQL> @ mainprogram2

Enter value for empno: 7369

old 2: upd(&empno);

new 2: upd(7369);

1 Record Updated...

PL/SQL procedure successfully completed.

Example 3:

Create a procedure which adds the given three numbers using “in” and “out” parameters

Sol. Steps

1. Write the sub Program

Ed subprocedure3

Create or replace procedure sum_numbers(n1 in number, n2 in

number, res out number)

is

Begin

res:=n1+n2;

End;

/

1. Next, Compile the above procedure to find errors in the procedure, with the following statement

@ subprocedure3

1. Next, Write the main program

Declare

a number;

b number;

c number;

Begin

a:=&a;

b:=&b;

sum_numbers(a,b,c);

dbms_output.put_line('sum of three numbers is '||c);

End;

/

1. Next, Run the above main procedure with the following statement

@mainprogram3

output :

SQL> @ mainprogram3

Enter value for a: 10

old 6: a:=&a;

new 6: a:=10;

Enter value for b: 20

old 7: b:=&b;

new 7: b:=20;

sum of three numbers is30

PL/SQL procedure successfully completed.

Example 4:

Create a procedure which Updates the sal with the increment value that you give according to the given emp number

Sol. Steps

1. Write the sub Program

Ed subprocedure4

Create or replace procedure incr(eno employee.empno%type,s

out number, i number)

Is

Begin

Update employee set sal=sal+i where empno=eno;

Dbms_output.put_line('Record Updated----');

End;

/

1. Next, Compile the above procedure to find errors in the procedure, with the following statement

@ subprocedure4

1. Next, Write the main program

Declare

e number:=&empno;

Incr_value number:=&increment;

s employee.sal%type;

Begin

Select sal into s from employee where empno=e;

Incr(e,s,incr_value);

End;

/

1. Next, Run the above main procedure with the following statement

@mainprogram4

output :

SQL> @ mainprogram4

Enter value for empno: 7369

old 2: e number:=&empno;

new 2: e number:=7369;

Enter value for increment: 48

old 3: incr_value number:=&increment;

new 3: incr_value number:=48;

Record Updated----

PL/SQL procedure successfully completed.