A Procedure is a named PL/SQL block which is compiled and stored in the database for repeated execution.
Basic Syntax :
------------
Create or replace procedure <procedure_name>
is
begin
..............
..............
.............
end;
/
Ex 1:
-----------
Create or replace procedure p1
is
begin
dbms_output.put_line('Hello World');
end;
/
Procedure created.
To execute the procedure:
----------------------------
Exec command is used to execute the procedure.
SQL> Exec p1
Hello World
A procedure can have three types of parameters.
1) IN Parameter
2) OUT Parameter
3) IN OUT Parameter
In Parameters are used to accept values from the user.
Ex 2:
---------
Create a procedure which accepts two numbers and display its sum.
create or replace procedure add_num ( a IN number,
b IN number)
is
c number(3);
begin
c := a+b;
dbms_output.put_line(' The sum is '||c);
end;
/
Procedure created.
To execute the procedure:
--------------------------
SQL> exec add_num (10,20)
Ex 3:
--------
Create a Procedure which accepts an empno and increments his salary by 1000.
create or replace procedure inc_sal ( a in number)
is
begin
update emp set sal = sal+1000
where empno = a;
end;
/
Procedure created.
TO execute the procedure:
---------------------------
SQL> exec inc_sal(7900)
We can improve the above procedure code by using %type attribute in procedure parameters.
The above procedure can be re-written as below :
create or replace procedure inc_sal ( a in emp.empno%type)
is
begin
update emp set sal = sal+1000
where empno = a;
end;
/
Ex 4:
-------
Create a procedure which accepts empno and display ename and salary.
create or replace procedure display_emp ( l_empno emp.empno%type)
is
l_ename emp.ename%type;
l_sal emp.sal%type;
begin
select ename, sal into l_ename,l_sal from emp
where empno = l_empno;
dbms_output.put_line(l_ename||'....'||l_sal);
exception
when no_data_found then
dbms_output.put_line('Invalid empno');
end;
/
Ex 5:
--------
Create a procedure which accepts deptno and display ename and salary of employees working in that department.
create or replace procedure display_emp1 (l_deptno emp.deptno%type)
is
cursor c1
is select ename,sal from emp
where deptno = l_deptno;
begin
for emp_rec in c1 loop
dbms_output.put_line(emp_rec.ename||'...'||emp_rec.sal);
end loop;
end;
Ex 6:
--------
We can call a procedure from another procedure.
create or replace procedure demo1
is
begin
dbms_output.put_line('This is from demo1');
end;
/
create or replace procedure demo2
is
begin
dbms_output.put_line ('Welcome');
demo1;
dbms_output.put_line ('Thank you');
end;
/
SQL> Exec demo2
Ex 7:
---------
We can call multiple procedures at a time using PL/SQL block.
begin
p1;
add_num(10,20);
inc_sal(7900);
end;
/
Ex 8:
--------
If there are any syntax errors in the procedure code, then the
procedcure is created with compilation errors.
create or replace procedure add_num ( a IN number,
b IN number)
is
c number(3);
begin
c := a+b;
dbms_outut.put_line(' The sum is '||c);
end;
/
Procedure is created with compilation errrors.
To see the errors, use the following command.
SQL> sho err
We get error information.
Rectify the error and re compile the code to create procedure successfully.
Ex 9:
---------
Sub procedure: A procedure inside another procedure is called as
Sub procedure.
create or replace procedure test
is
procedure sample
is
begin
dbms_output.put_line('This is from sample');
end;
begin
dbms_output.put_line('This is from test');
sample;
end;
In the above example procedure sample is called as Sub procedure.
A Sub procedure can be invoked from the main procedure only.
SQL> EXEC test
This is from test
This is from sample
We cannot invoke the Sub procedure independently.
The following command will give error.
SQL>EXEC sample
Ex 10:
----------
OUT parameters are used to return the values to the calling environment.
create a procedure which accepts empno and return salary.
create or replace procedure ret_sal( l_empno in emp.empno%type,
l_sal out emp.sal%type)
is
begin
select sal into l_sal from emp
where empno = l_empno;
end;
As the procedure is returning a value using OUT parameter,
we need to have a bind variable to catch the value. We need to follow a 3 step process to execute the above procedure.
Step 1: Create bind variable
Step 2: Execute the procedure using bind variable
Step 3: Print the value in the bind variable.
Step 1: creating Bind variable
SQL> variable g_sal number
Step 2: Invoking the procedure using bind variable
SQL> Exec ret_sal( 7900, :g_sal)
Step 3: Print the value in the bind variable
SQL> Print g_sal
Ex 11:
-----------------
IN OUT parameters are used to accept the value as well as return the values to the calling environment.
Create a procedure which accepts a number and return its square.
create or replace procedure cal_square( a In OUT number)
is
begin
a := a*a;
end;
/
To run the above proceure we need to follow a four step process.
Step 1: Create Bind variable
Step 2: Initiate the Bind variable
Step 3: Invoke the procedure using bind varaible
Step 4: Print the value in the bind variable
Step 1:
SQL> Variable n number
Step 2:
begin
:n :=5;
end;
/
Step 3:
SQL> Exec cal_square (:n)
Step 4:
SQL> Print n
Ex 12:
------------
To see the list of procedures, use the following queries
SQL> select object_name from user_objects where
object_type='PROCEDURE';
or
SQL> select procedure_name from user_procedures.
Ex 13:
-------
Using Default keyword:
-----------------------
create or replace procedure add_num3( a number,
b number default 100,
c number default 200)
is
d number(5);
begin
d := a+b+c;
dbms_output.put_line('The sum is ...'||d);
end;
/
Procedure created.
To execute the procedure
SQL> EXEC add_num3(10,20,30)
Output: The sum is 60
SQL> Exec add_num3(10,20)
Output : The sum is 230
Note: Default value is considered if we do not pass any value.
SQL> You need to use arrow operator if you pass values to specific parameters
Ex:
SQL> Exec add_num3(a=>10, c =>20)
Output: The sum is 130
Default value 100 is considered for parameter b.
ex 14:
----------
If there are any errors in the procedure code, then procedure is created with compilation errors.
To see the compilation errors SHO ERR command is used.
We need to rectify the errors and recreate the procedure sucessfully.
Ex 15:
-----------
To see the code of the existing procedure
select text from user_source
where name =ADD_NUM3;
TO drop a procedure:
-----------------------
SQL> Drop Procedure <procedure_name>;