• Contact
  • About Us

  • Home
  • ORACLE
  • UNIX
  • JAVA
  • IBM BPM
  • Interview Questions
You are here: Home / Packages

Packages

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.

Share it!
Share
on Facebook
Tweet
Share on Twitter
Share
on Google+
  • Facebook
  • Twitter

Recent Posts

  • LISTAGG In oracle With Example
  • regexpr_instr in oracle
  • Regexp_replace in oracle
  • Regexp_like in oracle
  • How to remove numeric values from a string in oracle

© Copyright 2014 123Techguru · Sitemap · Privacy Policy ·Contactus ·