Package in PL/SQL

Package in PL/SQL 

Introduction:

·     ·       PL/SQL package is a logical grouping of a related subprogram Such as procedure, function into a single or package or element.

·    Components of Package:

     PL/SQL package has two components.

1.    Package Specification

2.    Package Body

Package Specification:

·       Package specification consists of a declaration of all the public variables, cursors, objects, procedures, functions, and exception.

Objects:

·       Private Object

·       Public object

Private Object:

Any subprogram not in the package specification but codes in the Body of the Package is known as Private Object.

Public object:

All objects are placed in the Package specification is known as Public object.

 Syntax:

Creation of Package specification:

Create [OR Replace] Package Package_name < Public element declaration> ……… End Package_name;

Package Body:

·       ·       It consists of the definition of all the elements that are present in the         package specification.

·        ·        It can also have a definition of elements that are not declared in the     specification.

·        ·       These elements are called private elements and can be called only from     inside the package.

Syntax:

Create [OR Replace] package body package_name Is <sub program and public element definition> <private element declaration> …….. End package_name;

Example:

Package Specification:

CREATE OR REPLACE PACKAGE BODY as emp_deptno       PROCEDURE find_dept(p_ename emp.ename%TYPE) ; End  emp_deptno;

Package Body:

CREATE OR REPLACE PACKAGE BODY emp_deptno AS        PROCEDURE find_dept(p_ename emp.ename%TYPE)       IS          V_deptno emp.deptno%TYPE;      BEGIN         SELECT deptno INTO V_deptno         FROM emp         WHERE ename = p_ename;            dbms_output.put_line(‘Department '|| v_deptno);      END find_dept; END emp_deptno;

 After executing on the SQL prompt the result of the package is,

Package is created

Execution of the Package:

Syntax for using elements

Package_name.Procedure_name; Or Package_name.Element_name;

Method:

Declare       Result emp.ename%type :=  &P_ename ; Begin      emp_deptno. find_dept(Result); End;

Output:

P_ename : ‘Tani’ Deptno : 10

 

 

 

 

 

Share: