PROCEDURES

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.