Tuesday, 14 January 2014

PL/SQL - Packages

#=======================================================================
# Writer          : Lokanadham Thandlam
#=======================================================================
--Example 10–1 Simple Package Specification

CREATE OR REPLACE PACKAGE trans_data
   AUTHID DEFINER
AS
   TYPE TimeRec IS RECORD
   (
      minutes   SMALLINT,
      hours     SMALLINT
   );

   TYPE TransRec IS RECORD
   (
      category   VARCHAR2 (10),
      account    INT,
      amount     REAL,
      time_of    TimeRec
   );

   minimum_balance   CONSTANT REAL := 10.00;
   number_processed           INT;
   insufficient_funds         EXCEPTION;
   PRAGMA EXCEPTION_INIT (insufficient_funds, -4097);
END trans_data;
/


--Example 10–2 Passing Associative Array to Standalone Subprogram

CREATE OR REPLACE PACKAGE aa_pkg
   AUTHID DEFINER
IS
   TYPE aa_type IS TABLE OF INTEGER
                      INDEX BY VARCHAR2 (15);
END;
/

CREATE OR REPLACE PROCEDURE print_aa (aa aa_pkg.aa_type)
   AUTHID DEFINER
IS
   i   VARCHAR2 (15);
BEGIN
   i := aa.FIRST;

   WHILE i IS NOT NULL
   LOOP
      DBMS_OUTPUT.PUT_LINE (aa (i) || ' ' || i);
      i := aa.NEXT (i);
   END LOOP;
END;
/

DECLARE
   aa_var   aa_pkg.aa_type;
BEGIN
   aa_var ('zero') := 0;
   aa_var ('one') := 1;
   aa_var ('two') := 2;
   print_aa (aa_var);
END;
/



--Example 10–3 Matching Package Specification and Body

CREATE PACKAGE emp_bonus
AS
   PROCEDURE calc_bonus (date_hired employees.hire_date%TYPE);
END emp_bonus;
/

CREATE PACKAGE BODY emp_bonus
AS
   -- DATE does not match employees.hire_date%TYPE
   PROCEDURE calc_bonus (date_hired DATE)
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE (
         'Employees hired on ' || date_hired || ' get bonus.');
   END;
END emp_bonus;
/

---Result:
--Warning: Package Body created with compilation errors.
--Show errors (in SQL*Plus):
--SHOW ERRORS
--Result:
--Errors for PACKAGE BODY EMP_BONUS:
--LINE/COL ERROR
-------- -----------------------------------------------------------------
--2/13 PLS-00323: subprogram or cursor 'CALC_BONUS' is declared in a
--package specification and must be defined in the package body
--Correct problem:

CREATE OR REPLACE PACKAGE BODY emp_bonus
AS
   PROCEDURE calc_bonus (date_hired employees.hire_date%TYPE)
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE (
         'Employees hired on ' || date_hired || ' get bonus.');
   END;
END emp_bonus;
/

--Result:
--Package body created.

--Example 10–4 Creating SERIALLY_REUSABLE Packages
-- Create bodiless SERIALLY_REUSABLE package:

CREATE OR REPLACE PACKAGE bodiless_pkg
   AUTHID DEFINER
IS
   PRAGMA SERIALLY_REUSABLE;
   n   NUMBER := 5;
END;
/

-- Create SERIALLY_REUSABLE package with specification and body:

CREATE OR REPLACE PACKAGE pkg
   AUTHID DEFINER
IS
   PRAGMA SERIALLY_REUSABLE;
   n   NUMBER := 5;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg
IS
   PRAGMA SERIALLY_REUSABLE;
BEGIN
   n := 5;
END;
/


--Example 10–5 Effect of SERIALLY_REUSABLE Pragma

CREATE OR REPLACE PACKAGE pkg
IS
   n   NUMBER := 5;
END pkg;
/

CREATE OR REPLACE PACKAGE sr_pkg
IS
   PRAGMA SERIALLY_REUSABLE;
   n   NUMBER := 5;
END sr_pkg;
/

BEGIN
   pkg.n := 10;
   sr_pkg.n := 10;
END;
/

BEGIN
   DBMS_OUTPUT.PUT_LINE ('pkg.n: ' || pkg.n);
   DBMS_OUTPUT.PUT_LINE ('sr_pkg.n: ' || sr_pkg.n);
END;
/

--Example 10–6 Cursor in SERIALLY_REUSABLE Package Open at Call Boundary
DROP TABLE people;

CREATE TABLE people (name VARCHAR2 (20));

INSERT INTO people (name)
     VALUES ('John Smith');

INSERT INTO people (name)
     VALUES ('Mary Jones');

INSERT INTO people (name)
     VALUES ('Joe Brown');

INSERT INTO people (name)
     VALUES ('Jane White');

CREATE OR REPLACE PACKAGE sr_pkg
IS
   PRAGMA SERIALLY_REUSABLE;

   CURSOR c
   IS
      SELECT name FROM people;
END sr_pkg;
/

CREATE OR REPLACE PROCEDURE fetch_from_cursor
IS
   name_   VARCHAR2 (200);
BEGIN
   IF sr_pkg.c%ISOPEN
   THEN
      DBMS_OUTPUT.PUT_LINE ('Cursor is open.');
   ELSE
      DBMS_OUTPUT.PUT_LINE ('Cursor is closed; opening now.');

      OPEN sr_pkg.c;
   END IF;

   FETCH sr_pkg.c INTO name_;

   DBMS_OUTPUT.PUT_LINE ('Fetched: ' || name_);

   FETCH sr_pkg.c INTO name;

   DBMS_OUTPUT.PUT_LINE ('Fetched: ' || name_);
END fetch_from_cursor;
/

--First call to server:

BEGIN
   fetch_from_cursor;
   fetch_from_cursor;
END;
/

--New call to server:

BEGIN
   fetch_from_cursor;
   fetch_from_cursor;
END;
/


--Example 10–7 Separating Cursor Declaration and Definition in Package

CREATE PACKAGE emp_stuff
AS
   CURSOR c1
      RETURN employees%ROWTYPE;                              -- Declare cursor
END emp_stuff;
/

CREATE PACKAGE BODY emp_stuff
AS
   CURSOR c1
      RETURN employees%ROWTYPE
   IS
      SELECT *
        FROM employees
       WHERE salary > 2500;                                   -- Define cursor
END emp_stuff;
/


--Example 10–8 ACCESSIBLE BY Clause

CREATE OR REPLACE PACKAGE helper
AUTHID DEFINER
ACCESSIBLE BY (api)
IS
PROCEDURE h1;
PROCEDURE h2;
END;
/

CREATE OR REPLACE PACKAGE BODY helper
IS
   PROCEDURE h1
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE ('Helper procedure h1');
   END;

   PROCEDURE h2
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE ('Helper procedure h2');
   END;
END;
/

CREATE OR REPLACE PACKAGE api
   AUTHID DEFINER
IS
   PROCEDURE p1;

   PROCEDURE p2;
END;
/

CREATE OR REPLACE PACKAGE BODY api
IS
   PROCEDURE p1
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE ('API procedure p1');
      helper.h1;
   END;

   PROCEDURE p2
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE ('API procedure p2');
      helper.h2;
   END;
END;
/

--Invoke procedures in API package:

BEGIN
   api.p1;
   api.p2;
END;
/

--Invoke a procedure in helper package:

BEGIN
   helper.h1;
END;
/



--Example 10–9 Creating emp_admin Package
-- Log to track changes (not part of package):
DROP TABLE LOG;

CREATE TABLE LOG
(
   date_of_action   DATE,
   user_id          VARCHAR2 (20),
   package_name     VARCHAR2 (30)
);

-- Package specification:

CREATE OR REPLACE PACKAGE emp_admin
   AUTHID DEFINER
AS
   -- Declare public type, cursor, and exception:
   TYPE EmpRecTyp IS RECORD
   (
      emp_id   NUMBER,
      sal      NUMBER
   );

   CURSOR desc_salary
      RETURN EmpRecTyp;

   invalid_salary   EXCEPTION;

   -- Declare public subprograms:
   FUNCTION hire_employee (last_name         VARCHAR2,
                           first_name        VARCHAR2,
                           email             VARCHAR2,
                           phone_number      VARCHAR2,
                           job_id            VARCHAR2,
                           salary            NUMBER,
                           commission_pct    NUMBER,
                           manager_id        NUMBER,
                           department_id     NUMBER)
      RETURN NUMBER;

   -- Overload preceding public subprogram:
   PROCEDURE fire_employee (emp_id NUMBER);

   PROCEDURE fire_employee (emp_email VARCHAR2);

   PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER);

   FUNCTION nth_highest_salary (n NUMBER)
      RETURN EmpRecTyp;
END emp_admin;
/

-- Package body:

CREATE OR REPLACE PACKAGE BODY emp_admin
AS
   number_hired   NUMBER;    -- private variable, visible only in this package

   -- Define cursor declared in package specification:
   CURSOR desc_salary
      RETURN EmpRecTyp
   IS
        SELECT employee_id, salary
          FROM employees
      ORDER BY salary DESC;

   -- Define subprograms declared in package specification:
   FUNCTION hire_employee (last_name         VARCHAR2,
                           first_name        VARCHAR2,
                           email             VARCHAR2,
                           phone_number      VARCHAR2,
                           job_id            VARCHAR2,
                           salary            NUMBER,
                           commission_pct    NUMBER,
                           manager_id        NUMBER,
                           department_id     NUMBER)
      RETURN NUMBER
   IS
      new_emp_id   NUMBER;
   BEGIN
      new_emp_id := employees_seq.NEXTVAL;

      INSERT INTO employees (employee_id,
                             last_name,
                             first_name,
                             email,
                             phone_number,
                             hire_date,
                             job_id,
                             salary,
                             commission_pct,
                             manager_id,
                             department_id)
           VALUES (new_emp_id,
                   hire_employee.last_name,
                   hire_employee.first_name,
                   hire_employee.email,
                   hire_employee.phone_number,
                   SYSDATE,
                   hire_employee.job_id,
                   hire_employee.salary,
                   hire_employee.commission_pct,
                   hire_employee.manager_id,
                   hire_employee.department_id);

      number_hired := number_hired + 1;
      DBMS_OUTPUT.PUT_LINE (
         'The number of employees hired is ' || TO_CHAR (number_hired));
      RETURN new_emp_id;
   END hire_employee;

   PROCEDURE fire_employee (emp_id NUMBER)
   IS
   BEGIN
      DELETE FROM employees
            WHERE employee_id = emp_id;
   END fire_employee;

   PROCEDURE fire_employee (emp_email VARCHAR2)
   IS
   BEGIN
      DELETE FROM employees
            WHERE email = emp_email;
   END fire_employee;

   -- Define private function, available only inside package:
   FUNCTION sal_ok (jobid VARCHAR2, sal NUMBER)
      RETURN BOOLEAN
   IS
      min_sal   NUMBER;
      max_sal   NUMBER;
   BEGIN
      SELECT MIN (salary), MAX (salary)
        INTO min_sal, max_sal
        FROM employees
       WHERE job_id = jobid;

      RETURN (sal >= min_sal) AND (sal <= max_sal);
   END sal_ok;

   PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER)
   IS
      sal     NUMBER (8, 2);
      jobid   VARCHAR2 (10);
   BEGIN
      SELECT job_id, salary
        INTO jobid, sal
        FROM employees
       WHERE employee_id = emp_id;

      IF sal_ok (jobid, sal + amount)
      THEN                                          -- Invoke private function
         UPDATE employees
            SET salary = salary + amount
          WHERE employee_id = emp_id;
      ELSE
         RAISE invalid_salary;
      END IF;
   EXCEPTION
      WHEN invalid_salary
      THEN
         DBMS_OUTPUT.PUT_LINE ('The salary is out of the specified range.');
   END raise_salary;

   FUNCTION nth_highest_salary (n NUMBER)
      RETURN EmpRecTyp
   IS
      emp_rec   EmpRecTyp;
   BEGIN
      OPEN desc_salary;

      FOR i IN 1 .. n
      LOOP
         FETCH desc_salary INTO emp_rec;
      END LOOP;

      CLOSE desc_salary;

      RETURN emp_rec;
   END nth_highest_salary;
BEGIN                                   -- initialization part of package body
   INSERT INTO LOG (date_of_action, user_id, package_name)
        VALUES (SYSDATE, USER, 'EMP_ADMIN');

   number_hired := 0;
END emp_admin;
/

-- Invoke packages subprograms in anonymous block:

DECLARE
   new_emp_id   NUMBER (6);
BEGIN
   new_emp_id :=
      emp_admin.hire_employee ('Belden',
                               'Enrique',
                               'EBELDEN',
                               '555.111.2222',
                               'ST_CLERK',
                               2500,
                               .1,
                               101,
                               110);
   DBMS_OUTPUT.PUT_LINE ('The employee id is ' || TO_CHAR (new_emp_id));
   emp_admin.raise_salary (new_emp_id, 100);
   DBMS_OUTPUT.PUT_LINE (
         'The 10th highest salary is '
      || TO_CHAR (emp_admin.nth_highest_salary (10).sal)
      || ', belonging to employee: '
      || TO_CHAR (emp_admin.nth_highest_salary (10).emp_id));
   emp_admin.fire_employee (new_emp_id);
-- You can also delete the newly added employee as follows:
-- emp_admin.fire_employee('EBELDEN');
END;

/
Posted by at
Labels: ,