PL/SQL Packages Overview with Examples

---

Packages:
======
A package is a combination of procedure and function.A package is a schema object that groups logically related PL/SQL types, items, and subprograms.Package also contains two parts.

• Package specification
• Package body

NOTE :1) Package specification and package body both start with a create statement and ends with end statement.

2) Package specification name and package body name should be unique.

3) Package body does not existed without package specification

Syntax :
=======
CREATE [OR REPLACE] PACKAGE package_name [ AUTHID { CURRENT_USER | DEFINER } ]
{ IS | AS }
[definitions of public TYPES,
declarations of public variables, types, and objects,
declarations of exceptions,
programs,
declarations of cursors, procedures, and functions,
headers of procedures and functions]
END [package_name];

NOTE : IN the above syntax [] represents optional
{} represents compulsory

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 :write a package to display the employee details on employee number passed as in parameter and find out his annual salary.

SQL>Create or replace package techsnibpack1
Is
Procedure p1
(P_empno in emp.empno%type,
P_ename out emp.ename%type,
P_sal out emp.sal%type,
P_deptno out emp.deptno%type);
Function myfun
(f_empno in emp.empno%type)
Return number;
End;
/
Package Created

SQL>Create or replace package body techsnibpack1
Is
Procedure p1
(p_empno in emp.empno%type,
P_ename out emp.ename%type,
P_sal out emp.sal%type,
P_deptno out emp.deptno%type)
Is
Begin
Select ename,sal,deptno into p_ename,p_sal,p_deptno from emp where where empno=p_empno;
End p1;
Function myfun
(f_empno in emp.empno%type)
Return number
Is
c number;
Begin
Select sal*12 into c from emp where empno=p_empno;
Return c;
End myfun;
End;
/
Package body created

Execution Process :
============
SQL> var a varchar2(15);
SQL> var b number
SQL> var c number
SQL> var d number

SQL> exec techsnibpack1.p1(7939,a,b,c);
PL/SQL Successfully completed

SQL> print a

A
---------------------------------------------
SMITH

SQL> print b

B
---------------------------------------------
800

SQL> print c

C
---------------------------------------------
20

SQL> exec :d:=techsnibpack1.myfun(7369);
PL/SQL procedure successfully completed

SQL> print d

D
---------------------------------------------
9600

Example-2 :write a package to display the employee details who are working in sales department and count the number of employees working in that.

SQL>Create or replace package techsnibpack2
Is
Procedure p1;
Function myfun
return number;
End;
/
Package Created

SQL>Create or replace package body techsnibpack2
Is
Procedure p1
Is
a emp%rowtype;
cursor c is select * from emp
where deptno=(select deptno from dept where dname=’SALES’);
Begin
open c;
Loop
Fetch c into a;
if c%found then
dbms_output.put_line(a.empno);
dbms_output.put_line(a.ename);
dbms_output.put_line(a.sal);
dbms_output.put_line(a.hiredate);
else
exit;
end if;
End loop;
close c;
End p1;
Function myfun
return number
Is
c number;
Begin
select count(*) into c from emp where deptno=(select deptno from dept where dname=’SALES’);
Return c;
End myfun;
End;
/
Package body created

Example-3 :write a package to display the employee details whose salary is greater than any employee salary working under 20th department and find out their sum of the salaries.

SQL>Create or replace package techsnibpack3
Is
Procedure p1;
Function myfun
Return number;
End;
/
Package Created

SQL>Create or replace package body techsnibpack3
Is
Procedure p1
Is
a emp%rowtype;
Cursor c is select * from emp where sal>any(select sal from emp where deptno=20);
Begin
Open c;
Loop
Fetch c into a;
if c%found then
dbms_output.put_line(a.empno);
dbms_output.put_line(a.ename);
dbms_output.put_line(a.sal);
dbms_output.put_line(a.hiredate);
else
Exit;
End if;
End loop;
Close c;
End p1;
Function myfun
Return number
Is
c number;
Begin
Select sum(sal) into c from emp where sal>any(select sal from emp where deptno=20);
Return c;
End myfun;
End;
/
Package body created

Example-4 :write a package to find out the maximum salary employee details working in DALLAS and CHICAGO and find out his total salary.

SQL>Create or replace package techsnibpack4
Is
Procedure p1;
Function myfun
Return number;
End;
/
Package Created

SQL>Create or replace package body techsnibpack4
Is
Procedure p1
Is
a emp%rowtype;
Begin
Select emp.* into a from emp
where sal in(select max(sal) from emp
where deptno in(select deptno from dept where loc in(‘DALLAS’,’CHICAGO’);
dbms_output.put_line(a.ename);
dbms_output.put_line(a.sal);
dbms_output.put_line(a.hiredate);
End p1;
Function myfun Return number
Is
c number;
Begin
Select sal*12 into c from emp
where sal in(select max(sal) from emp
where deptno in(select deptno from dept where loc in(‘DALLAS’,’CHICAGO’);
Return c;
End myfun;
End;
/
Package body created

--> 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 package <package_name>;

---