Packages in Plsql with Examples

Package is a schema object for storing logically related items.Package has mainly two sections:

• Package Specification(HEADER): Used to declare types, variables, constants, exceptions, cursors, and subprograms
• Package BODY: Defines the cursors and subprograms declared in the Specification.

Packages can have two or more subprograms with the same name but different set of parameters or different datatypes. This is known as overloading of subprograms

Package Dependencies:

The package header doesn’t depend on the package body and we can change the package body without changing the header.

Referencing Package Contents:

Package_name.item_name
Package_name.subprogram_name

Package Syntax:

CREATE OR REPLACE PACKAGE package_name

      {IS | AS}

   <collection_type_definition ...>

   <record_type_definition ...>

   <subtype_definition ...>

   <collection_declaration ...>

   <constant_declaration ...>

   <exception_declaration ...>

   <object_declaration ...>

   <record_declaration ...>

   <variable_declaration ...>

   <cursor_spec ...>

   <function_spec ...>

   <procedure_spec ...>

END package_name;

CREATE OR REPLACE PACKAGE BODY package_name

{IS | AS}

   <collection_type_definition ...>

   <record_type_definition ...>

   <subtype_definition ...>

   <collection_declaration ...>

   <constant_declaration ...>

   <exception_declaration ...>

   <object_declaration ...>

   <record_declaration ...>

   <variable_declaration ...>

   <cursor_body ...>

   <function_spec ...>

   <procedure_spec ...>

   <call_spec ...>

BEGIN

   initialization section

    Exception

Exception handling seciton

END package_name;

Example 1:

create or replace package Test_package as

procedure proc_test;

function func_test return varchar2;

end pack1;

/

create or replace package body Test_package as

procedure proc_test is

         begin

         dbms_out.put_line('This is a test Procedure');

         end proc1;

function func_test return varchar2 is

begin

         return('This is a test fucntion);

end func_test;

end proc_test;

Example 2:

create or replace package test_pack as

procedure amount(n number);

procedure proc_emp;

end test_pack;

/

create or replace package body mypack2 as

procedure amount (n number) is

         begin

         dbms_output.put_line('Rs.'||n||'only');

end amount;

procedure proc_emp as

for emprec in c2

loop

exit when c2%rowcount:=5;

dbms_output.put_line(emprec.ename);

dbms_output.put_line(' proc_emp executed');

end loop;

end proc_emp;

end test_pack;

/