Packages are logically related sub programs.
Package creating involves two steps.
Step 1: Creating Package specification (PKS)
Step 2: Creating Package Body (PKB)
Package Specification:
It contains declaration of sub programs
Syntax:
Create or replace package <package_name>
Is
Declaration of procedures;
Declaration of functions;
End;
/
Package Body:
It contains definition of sub programs
Syntax:
Create or replace package body <package_name>
Is
Definition of procedures;
Definition of functions;
End;
/
Example:
Let’s create a package with two procedures and function.
Procedure add_num — which takes two parameters and display its sum.
Procedure display_emp — which accepts empno and display ename and Sal.
Function cal_tax — which accepts Sal and returns tax value (10% of Sal is tax value).
Package Specification:
Create or replace package test_pack
Is
Procedure add_num (a number,
b number);
Procedure display_emp (l_empno emp.empno%type);
Function cal_tax (l_sal emp.sal%type)
return number;
End test_pack;
/
Package body:
Create or replace package body test_pack
Is
Procedure add_num (a number,
b number)
Is
c number;
Begin
C: = a+b;
dbms_output.put_line (‘the sum is ‘||c);
End;
Procedure display_emp (l_empno emp.empno%type)
Is
l_ename emp.ename%type;
l_sal emp.sal%type;
Begin
Select sal into l_sal from EMP
Where empno = l_empno;
dbms_output.put_line (l_ename||’…….’||l_sal);
End;
Function cal_tax (l_sal emp.sal%type)
Is
l_tax number;
Begin
l_tax:= l_sal *10/100;
Return l_tax;
End;
End test_pack;
/
To invoke sub programs inside the package:
SQL> EXEC test_pack.display_emp (7900)
SQL> select empno, ename, Sal, test_pack.cal_tax (Sal) from EMP;
Procedure overloading using packages:
We can achieve procedure overloading using Packages.
Basing on the no of parameters and datatype of the parameters,
The appropriate procedure is invoked.
Example:
Create or replace package test_pack2
Is
Procedure p1 (a number,
b number);
Procedure p1 (a number);
End test_pack2;
/
Create or replace package body test_pack2
Is
Procedure p1 (a number,
b number)
Is
c number;
Begin
c: = a+b;
dbms_output.put_line (‘the sum is’||c);
End;
Procedure p1 (a number)
Is
Begin
dbms_output.put_line (‘The square of the number is ‘||a*a);
End;
End test_pack2;
/
In the above package there are two procedures with the same name.
Appropriate procedure is invoked basing on the no of parameters which are passed
At the time of calling the procedure.
Example:
SQL> exec test_pack2 (10, 20);
The sum is 30
SQL> exec test_pack2 (10);
The square of the number is 100
To drop the package:
We need to drop package body first and then the package specification.
Drop package body <package_name>;
Drop package <package_name>;
Example:
Drop package body test_pack2;
Drop package test_pack2;
Guidelines of the packages:
1) Helps in modularity of the code.
2) Packages cannot be nested.
3) Packages cannot be parameterized.