Package in oracle with example

A package is a group of procedures, functions, variables and sql statements created as a single unit. It is used to store together related objects. A package has two parts, Package Specification or spec or package header and Package Body.

Package Specification acts as an interface to the package. Declaration of types, variables, constants, exceptions, cursors and subprograms is done in Package specifications. Package specification does not contain any code. Thus a package specification lists the functions and procedures in the package, with their call specifications: the arguments and their datatypes. It can also define variables and constants accessible to all the procedures and functions in the package.

Package body is used to provide implementation for the subprograms, queries for the cursors declared in the package specification or spec.

Example.1

1. package specification

SQL> create or replace package circle_area_peri is
  2  function area(r number) return number; ---> function area is declared with datatype.
  3  function perimeter(r number) return number; ---> function perimeter is declared  with datatype,
  4  end;
  5  /
Package created.

SQL>

2. package body

SQL> create or replace package body circle_area_peri is
  2  function area(r number) return number is --> function area is implemented here.
  3  ar number(7,2);
  4  begin
  5  ar := 3.14159*r*r;
  6  return ar;
  7  end;
  8  function perimeter(r number) return number is --> function perimeter is implemented here.
  9  pr number(7,2);
 10  begin
 11  pr := 2*3.14159*r;
 12  return pr;
 13  end;
 14  end;
 15  /

Package body created.

SQL>

For using the package , create sql file as follows ,
ed packagedemo
declare
r number(5,2);
area number(7,2);
perimeter number(7,2);
ar number(7);
pr number(7);
begin
dbms_output.put_line('CIRCLE');
dbms_output.put_line('Enter the radius:');
r := &r;
area := circle_area_peri.area(r);
perimeter := circle_area_peri.perimeter(r);
dbms_output.put_line('Area of the circle is :'||area);
dbms_output.put_line('Perimeter of the circle is :'||perimeter);
end;

Execute the above sql to see how package works,
SQL> @packagedemo
Enter value for r: 10
old  12: r := &r;
new  12: r := 10;
CIRCLE
Enter the radius:
Area of the circle is :314.16
Perimeter of the circle is :62.83

PL/SQL procedure successfully completed.

SQL>