PROCEDURES

A procedure is a one kind of subprogram, which is designed and created to perform a specific operation on data in your database. A procedure takes zero or more input parameters and returns 125 zero or more output parameters.
The syntax of a creation of procedure is as follows:
Syntax:
CREATE OR REPLACE PROCEDURE procedure_name
[(argument1 [IN/OUT/IN OUT] datatype, 
argument2 [IN/OUT/IN OUT] datatype,_)] IS 
[<local variable declarations>]

BEGIN
Executable Statements 
[EXCEPTION 
     Optional Exception Handler(s) 
] 
END;

The procedure is made up of two parts: the declaration and the body of the procedure. The declara- tion begins with the keyword PROCEDURE and ends with the last parameter declaration. The body begins with the keyword IS and ends with the keyword END. The procedure body is further divided into three parts : declarative, executable and exception part same as PL/SQL blcok. The declaration section is used to assign name and define parameter list, which variables are passed to the procedure and which values are returned from the procedure back to the calling program.
Parameters can be define in following format
Argument [parameter mode] datatype
There are three types of parameters mode: IN, OUT and IN OUT 

 

IN Mode:

• Default parameter mode
• Used to pass values to the procedure. 
• Formal parameter can be a constant, literal, initialized variable or expression. 
• Used for reading purpose

OUT Mode:

• Used to return values to the caller. 
• Formal parameter cannot be used in an expression, but should be assigned a value. 
• Used for writing purpose

IN OUT Mode:

• Used to pass values to the procedure as well as return values to the caller 
• Formal parameter acts like an initialized variable and should be assigned a value. 
• Used for both reading and writing purpose

Execution of procedure:

Procedure is executed from a SQL prompt as per follows and One can execute procedure from caller program also.
 
SQL > execute/exec procedure_name(parameter list) 
For example above created procedure is executed as follows 
SQL> Exec example1(7) 
7 is odd number


Example

Create a procedure, which receives department number and get total Salary of that Department. 
Create or replace procedure dept_total(dno in numbe, total out number) 
Is 
Begin 
Select sum(salary) into total from emp where deptno= dno; 
dbms_output.put_line(‘Total salary of Department ‘|| dno || 
‘ is ‘ || total); 
End;
/ 
Procedure created. 
Block To execute procedure 
Declare dn number(5) := &no; 
tot number; 
Begin dept_total(dn,tot); 
End;
/

Output 

Enter value for dn: 10 
old 2: dn number(5) := &dn; 
new 2: dn number(5) := 10; 
Total salary of Department 10 is 235300

Example

Write procedure to accept Department number and display Name, Designation and Age of each employee belonging to such Department. 
Create or replace procedure dept_list(dno number) 
Is 
cursor c1 is select * from emp where deptno = dno; 
erec emp%rowtype; 
Begin 
For erec in c1
loop dbms_output.put_line(‘Emp. Name : ‘ || erec.ename); 
dbms_output.put_line(‘Designation : ‘|| erec.desg); 
dbms_output.put_line(‘Age : ‘|| round((sysdate-erec.bdate)/ 365,0);

dbms_output.put_line(‘=============================’); 
End loop; 
End; / 
Procedure created. 

Output 

SQL>exec dept_list(20); 
Emp. Name : AANSHI 
Designation : ANALYST 
Age : 21 
========================= 
Emp. Name : TEJAS 
Designation : MANAGER 
Age : 27 
========================= 
Emp. Name : DAXESH 
Designation : MANAGER 
Age : 24 
=========================