PL/SQL Procedures Overview with Examples

---

Sub programs :
==========
Sub programs are those blocks which we can store permanently as an object. So that at any particular point of time the end user can access these blocks.

These blocks saved with a proper name with in the database that’s why these blocks can be called as named PL/SQL blocks.

Procedures / Stored Procedures:
=====================
A stored procedure or in simple a proc is a named PL/SQL block which performs one or more specific task. This is similar to a procedure in other programming languages.

A procedure has a header and a body. The header consists of the name of the procedure and the parameters or variables passed to the procedure. The body consists or declaration section, execution section and exception section similar to a general PL/SQL Block.

A procedure is similar to an anonymous PL/SQL Block but it is named for repeated usage.

We can pass parameters to procedures in three ways.

• IN-parameters
• OUT-parameters
• IN OUT-parameters

NOTE :A procedure may or may not return any value.

we consider the following two tables i.e emp, dept to write SQL queries in the coming PL/SQL programs.

Double click on the image to view the full image with clarity

Example-1 :
=======

SQL>create procedure p1 -- Procedure declaration and definition begins
(a in number,
b out varchar2(20),
c out number,
d out number)
is
-- Declarative part of procedure (optional) goes here
-- Executable part of procedure begins
begin
select ename,sal,deptno into b,c,d from emp where empno=a;
end;
/
procedure created

Execution process:
============
SQL> variable x varchar2(20);
SQL> var y number;
SQL> var z number;
SQL> exec p1(7788,x,y,z);

PL/SQL procedure successfully completed

SQL> print x

X
---------------------------------------------
SCOTT

SQL> print y

Y
---------------------------------------------
3000

SQL> print z

Z
---------------------------------------------
20

Example-2 : write a procedure to display the employee details and employee number passed as in parameter.

SQL>create or replace procedure p1
(a in number)
is
b varchar2(20);
c number;
d number;
begin
select ename,sal,deptno into b,c,d from emp where empno=a;
dbms_output.put_line(‘ename is’||b);
dbms_output.put_line(‘salary is’||c);
dbms_output.put_line(‘deptno is’||d);
end;

SQL> exec p1(7789);
PL/SQL procedure successfully completed

SQL> set serveroutput on
procedure created

SQL> exec p1(7788);

SCOTT 3000 20

PL/SQL procedure successfully completed

Example-3 : write a procedure to display the employee details and department number passed as in parameter.

SQL>create procedure p2(p_deptno in emp.deptno%type)
is
cursor c is select * from emp where deptno=p_deptno;
b emp%rowtype;
begin
open c;
loop
fetch c into b;
if c%found then
dbms_output.put_line(b.empno);
dbms_output.put_line(b.ename);
dbms_output.put_line(b.sal);
dbms_output.put_line(b.hiredate);
else
exit;
end if;
end loop;
close c;
end;

Example-4 : write a procedure to display the employee details who are working in sales department.

SQL>create procedure p3
is
cursor c is select * from emp
where deptno=(select deptno from dept where dname=’SALES’);
b emp%rowtype;
begin
open c;
loop
fetch c into b;
if c%found then
dbms_output.put_line(b.empno);
dbms_output.put_line(b.ename);
dbms_output.put_line(b.sal);
dbms_output.put_line(b.hiredate);
else
exit;
end if;
end loop;
close c;
end;

Example-5 : write a procedure to display the employee details who are working in dallas and chicago.

SQL>create procedure p3
is
cursor c is select * from emp
where deptno in(select deptno from dept where loc=’SALES’ or loc=’CHICAGO’);
b emp%rowtype;
begin
open c;
loop
fetch c into b;
if c%found then
dbms_output.put_line(b.empno);
dbms_output.put_line(b.ename);
dbms_output.put_line(b.sal);
dbms_output.put_line(b.hiredate);
else
exit;
end if;
end loop;
close c;
end;

Example-6 : write a procedure to display the maximum and minimum salary employee details from employee table.

SQL>create procedure p3
is
a emp%rowtype;
b emp%rowtype;
begin
select * into a from emp where sal=(select max(sal) from emp);
select * into a from emp where sal=(select min(sal) from emp);
dbms_output.put_line(a.empno);
dbms_output.put_line(a.ename);
dbms_output.put_line(a.sal);
dbms_output.put_line(a.deptno);
dbms_output.put_line(b.empno);
dbms_output.put_line(b.ename);
dbms_output.put_line(b.sal);
dbms_output.put_line(b.deptno);
end;

Example-7 : write a procedure to display the maximum commission employee details with total salary.

SQL>create procedure p3
is
a emp%rowtype;
b number;
begin
select * into a from emp where comm=(select max(comm) from emp);
select sal+comm into b from emp where sal=(select max(comm) from emp);
dbms_output.put_line(a.empno);
dbms_output.put_line(a.ename);
dbms_output.put_line(a.sal);
dbms_output.put_line(a.deptno);
dbms_output.put_line(b);
end;

--> Syntax to see the list of all sub programs

SQL> select * from user_source;

--> Syntax to see the source code of a particular program

SQL> select text from user_source where name=’<sub program name>’;

--> Syntax to drop the package

SQL> drop procedure <procedure_name>;

---