PL/SQL Packages

PL/SQL Packages is schema object and collection of related data type (variables, constants), cursors, procedures, functions are defining within a single context. Package are device into two part,

    1.   Package Specification

    2.   Package Body

Package specification block you can define variables, constants, exceptions and package body you can create procedure, function, subprogram.

PL/SQL Package Advantages

 1.   You can create package to store all related functions and procedures are     grouped together into single unit called packages.

 2.   Package are reliable to granting a privileges.

 3.   All function and procedure within a package can share variable among them.

 4.   Package are support overloading to overload functions and procedures.

 5.   Package are improving the performance to loading the multiple object into memory at once, therefore, subsequent calls to related program doesn't require to calling physically I/O.

    6.   Package are reducing the traffic because all block executes all at once.

PL/SQL Package Syntax

PL/SQL Specification : This contain the list of variables, constants, functions, procedure names which are the part of the package. PL/SQL specification are public declaration and visible to a program.

Package Specification Code

Create Package specification code for defining procedure, function IN or OUT parameter and execute package specification program.

CREATE OR REPLACE PACKAGE personnel AS

  -- get employee's fullname

  FUNCTION get_fullname(n_emp_id NUMBER)

    RETURN VARCHAR2;

  -- get employee's salary

  FUNCTION get_salary(n_emp_id NUMBER)

    RETURN NUMBER;

END personnel;

Package Body Code

Create Package body code for implementing procedure or function that are defined package specification. Once you implement execute this program.

CREATE OR REPLACE PACKAGE BODY personnel AS

  -- get employee's fullname

  FUNCTION get_fullname(n_emp_id NUMBER) RETURN VARCHAR2 IS

      v_fullname VARCHAR2(46);

  BEGIN

    SELECT first_name || ',' ||  last_name

    INTO v_fullname

    FROM employees

    WHERE employee_id = n_emp_id;

    RETURN v_fullname;

  EXCEPTION

  WHEN NO_DATA_FOUND THEN

    RETURN NULL;

  WHEN TOO_MANY_ROWS THEN

    RETURN NULL;

  END; -- end get_fullname

  -- get salary

  FUNCTION get_salary(n_emp_id NUMBER) RETURN NUMBER IS

    n_salary NUMBER(8,2);

  BEGIN

    SELECT salary

    INTO n_salary

    FROM employees

    WHERE employee_id = n_emp_id;

    RETURN n_salary;

    EXCEPTION

      WHEN NO_DATA_FOUND THEN

        RETURN NULL;

      WHEN TOO_MANY_ROWS THEN

        RETURN NULL;

  END;

END personnel;

Pl/SQL Program calling Package

SET SERVEROUTPUT ON SIZE 1000000;

DECLARE

  n_salary NUMBER(8,2);

  v_name   VARCHAR2(46);

  n_emp_id NUMBER := &emp_id;

BEGIN

  v_name   := personnel.get_fullname(n_emp_id);

  n_salary := personnel.get_salary(n_emp_id);

  IF v_name  IS NOT NULL AND

    n_salary IS NOT NULL

  THEN

    dbms_output.put_line('Employee: ' || v_name);

    dbms_output.put_line('Salary:' || n_salary);

  END IF;

END;

PL/SQL Package Alter

ALTER PACKAGE package_name COMPILE BODY; 

Recompile the already created/executed package code,

 SQL>ALTER PACKAGE pkg1 COMPILE BODY;

 Package body Altered.

PL/SQL Package Drop

You can drop package using package DROP statement,

Package Drop Syntax :

DROP PACKAGE package_name;