Post
Ask Question

Packages In Oracle

A package is a group of procedures, functions, variables and SQL statements, created as a single unit. It is used to store together the 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 the variables and constants, accessible to all the procedures and functions in the package.

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

Example 1

  1.  package specification  
  2. SQL> create or replace package circle_area_peri is  
  3.  function area(r number) return number; ---> function area is declared with datatype.  
  4.  function perimeter(r number) return number; ---> function perimeter is declared with datatype,  
  5.  end;  
  6.  /  
  7. Package created.  
  8. SQL>  
Package body
  1. 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:
  1. ed packagedemo  
  2. declare  
  3. r number(5,2);  
  4. area number(7,2);  
  5. perimeter number(7,2);  
  6. ar number(7);  
  7. pr number(7);  
  8. begin  
  9. dbms_output.put_line('CIRCLE');  
  10. dbms_output.put_line('Enter the radius:');  
  11. r := &r;  
  12. area := circle_area_peri.area(r);  
  13. perimeter := circle_area_peri.perimeter(r);  
  14. dbms_output.put_line('Area of the circle is :'||area);  
  15. dbms_output.put_line('Perimeter of the circle is :'||perimeter);  
  16. end;  
Execute SQL, mentioned above, to see how the package works.
  1. SQL> @packagedemo  
  2. Enter value for r: 10  
  3. old 12: r := &r;  
  4. 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.