<pre>
                        

CREATE OR REPLACE PACKAGE comp_analysis
IS
   FUNCTION is_eligible (id_in IN lots_of_employees.employee_id%TYPE)
      RETURN BOOLEAN;
END comp_analysis;
/       
CREATE OR REPLACE PACKAGE BODY comp_analysis
IS
   FUNCTION is_eligible (id_in IN lots_of_employees.employee_id%TYPE)
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN MOD (id_in, 2) = 0;
   END;
END comp_analysis;
/       
CREATE OR REPLACE PROCEDURE give_raises_in_department1 (
   dept_in IN lots_of_employees.department_id%TYPE
 , newsal IN lots_of_employees.salary%TYPE
)
IS
   CURSOR emp_cur
   IS
      SELECT employee_id, salary, hire_date
        FROM lots_of_employees
       WHERE (department_id = dept_in OR dept_IN IS NULL);

   emp_rec emp_cur%ROWTYPE;
BEGIN
   OPEN emp_cur;

   LOOP
      FETCH emp_cur
       INTO emp_rec;

      EXIT WHEN emp_cur%NOTFOUND;

      IF comp_analysis.is_eligible (emp_rec.employee_id)
      THEN
         UPDATE lots_of_employees
            SET salary = newsal
          WHERE employee_id = emp_rec.employee_id;
      ELSE
         INSERT INTO employee_history
                     (employee_id, salary
                    , hire_date, activity
                     )
              VALUES (emp_rec.employee_id, emp_rec.salary
                    , emp_rec.hire_date, 'RAISE DENIED'
                     );
      END IF;
   END LOOP;
END give_raises_in_department1;
/
SHO ERR

REM
REM Pre-10g create multiple copies of collection
REM for different purposes.
REM

CREATE OR REPLACE PROCEDURE give_raises_in_department2 (
   dept_in IN lots_of_employees.department_id%TYPE
 , newsal IN lots_of_employees.salary%TYPE
)
IS
   TYPE employee_aat IS TABLE OF lots_of_employees.employee_id%TYPE
      INDEX BY PLS_INTEGER;
   TYPE salary_aat IS TABLE OF lots_of_employees.salary%TYPE
      INDEX BY PLS_INTEGER;
   TYPE hire_date_aat IS TABLE OF lots_of_employees.hire_date%TYPE
      INDEX BY PLS_INTEGER;
          
   employee_ids employee_aat;
   salaries salary_aat;
   hire_dates hire_date_aat;

   approved_employee_ids employee_aat;
   
   denied_employee_ids employee_aat;
   denied_salaries salary_aat;
   denied_hire_dates hire_date_aat;

   PROCEDURE retrieve_employee_info
   IS
   BEGIN
      SELECT employee_id, salary, hire_date
      BULK COLLECT INTO employee_ids, salaries, hire_dates
        FROM lots_of_employees
       WHERE (department_id = dept_in OR dept_IN IS NULL);
   END;

   PROCEDURE partition_by_eligibility
   IS
   BEGIN
      FOR indx IN employee_ids.FIRST .. employee_ids.LAST
      LOOP
         IF comp_analysis.is_eligible (employees (indx))
         THEN
            approved_employee_ids (indx) := employees (indx);
         ELSE
            denied_employee_ids (indx) := employees (indx);
            denied_salaries (indx) := salaries (indx);
            denied_hire_dates (indx) := hire_dates (indx);
                 END IF;
      END LOOP;
   END;

   PROCEDURE add_to_history
   IS
   BEGIN
      FORALL indx IN denied_employee_ids.FIRST .. denied_employee_ids.LAST
         INSERT INTO employee_history
                     (employee_id
                    , salary
                    , hire_date, activity
                     )
              VALUES (denied_employee_ids (indx)
                    , denied_salaries (indx)
                    , denied_hire_dates (indx), 'RAISE DENIED'
                     );
   END;

   PROCEDURE give_the_raise
   IS
   BEGIN
      FORALL indx IN approved_employee_ids.FIRST .. approved_employee_ids.LAST
         UPDATE lots_of_employees
            SET salary = newsal
          WHERE employee_id = approved_employee_ids (indx);
   END;
BEGIN
   retrieve_employee_info;
   partition_by_eligibility;
   add_to_history;
   give_the_raise;
END give_raises_in_department2;
/
SHO ERR

REM
REM 10g usage of INDICES OF
REM

CREATE OR REPLACE PROCEDURE give_raises_in_department3 (
   dept_in IN lots_of_employees.department_id%TYPE
 , newsal IN lots_of_employees.salary%TYPE
)
IS
   TYPE employee_aat IS TABLE OF lots_of_employees.employee_id%TYPE
      INDEX BY PLS_INTEGER;
   TYPE salary_aat IS TABLE OF lots_of_employees.salary%TYPE
      INDEX BY PLS_INTEGER;
   TYPE hire_date_aat IS TABLE OF lots_of_employees.hire_date%TYPE
      INDEX BY PLS_INTEGER;
          
   employee_ids employee_aat;
   salaries salary_aat;
   hire_dates hire_date_aat;

   TYPE guide_aat IS TABLE OF BOOLEAN
      INDEX BY PLS_INTEGER;

   approved_list guide_aat;
   denied_list guide_aat;

   PROCEDURE retrieve_employee_info
   IS
   BEGIN
      SELECT employee_id, salary, hire_date
      BULK COLLECT INTO employee_ids, salaries, hire_dates
        FROM lots_of_employees
       WHERE (department_id = dept_in OR dept_IN IS NULL);
   END;

   PROCEDURE partition_by_eligibility
   IS
   BEGIN
      FOR indx IN employee_ids.FIRST .. employee_ids.LAST
      LOOP
         IF comp_analysis.is_eligible (employees(indx))
         THEN
                    approved_list (indx) := TRUE;
         ELSE
            denied_list (indx) := TRUE;
                 END IF;
      END LOOP;
   END;

   PROCEDURE add_to_history
   IS
   BEGIN
      FORALL indx IN INDICES OF denied_list
         INSERT INTO employee_history
                     (employee_id
                    , salary
                    , hire_date, activity
                     )
              VALUES (employees (indx)
                    , salaries (indx)
                    , hire_dates (indx)
                                        , 'RAISE DENIED'
                     );
   END;

   PROCEDURE give_the_raise
   IS
   BEGIN
      FORALL indx IN INDICES OF approved_list
         UPDATE lots_of_employees
            SET salary = newsal
              , hire_date = hire_dates(indx)
          WHERE employee_id = employees(indx);
   END;
BEGIN
   retrieve_employee_info;
   partition_by_eligibility;
   add_to_history;
   give_the_raise;
END give_raises_in_department3;
/
SHO ERR

REM
REM 10g usage of VALUES OF
REM

CREATE OR REPLACE PROCEDURE give_raises_in_department4 (
   dept_in IN lots_of_employees.department_id%TYPE
 , newsal IN lots_of_employees.salary%TYPE
)
IS
   TYPE employee_aat IS TABLE OF lots_of_employees.employee_id%TYPE
      INDEX BY PLS_INTEGER;
   TYPE salary_aat IS TABLE OF lots_of_employees.salary%TYPE
      INDEX BY PLS_INTEGER;
   TYPE hire_date_aat IS TABLE OF lots_of_employees.hire_date%TYPE
      INDEX BY PLS_INTEGER;
          
   employee_ids employee_aat;
   salaries salary_aat;
   hire_dates hire_date_aat;

   TYPE guide_aat IS TABLE OF PLS_INTEGER
      INDEX BY PLS_INTEGER;

   approved_list guide_aat;
   denied_list guide_aat;

   PROCEDURE retrieve_employee_info
   IS
   BEGIN
      SELECT employee_id, salary, hire_date
      BULK COLLECT INTO employee_ids, salaries, hire_dates
        FROM lots_of_employees
       WHERE (department_id = dept_in OR dept_IN IS NULL);
   END;

   PROCEDURE partition_by_eligibility
   IS
   BEGIN
      FOR indx IN employee_ids.FIRST .. employee_ids.LAST
      LOOP
         IF comp_analysis.is_eligible (employees(indx))
         THEN
                    approved_list (indx) := indx;
         ELSE
            denied_list (indx) := indx;
                 END IF;
      END LOOP;
   END;

   PROCEDURE add_to_history
   IS
   BEGIN
      FORALL indx IN VALUES OF denied_list
         INSERT INTO employee_history
                     (employee_id
                    , salary
                    , hire_date, activity
                     )
              VALUES (employees (indx)
                    , salaries (indx)
                    , hire_dates (indx)
                                        , 'RAISE DENIED'
                     );
   END;

   PROCEDURE give_the_raise
   IS
   BEGIN
      FORALL indx IN VALUES OF approved_list
         UPDATE lots_of_employees
            SET salary = newsal
              , hire_date = hire_dates(indx)
          WHERE employee_id = employees(indx);
   END;
BEGIN
   retrieve_employee_info;
   partition_by_eligibility;
   add_to_history;
   give_the_raise;
END give_raises_in_department4;
/
SHO ERR

SET TIMING ON

BEGIN
   give_raises_in_department1 (NULL, 1000);
END;
/
BEGIN
   give_raises_in_department2 (NULL, 1000);
END;
/
BEGIN
   give_raises_in_department3 (NULL, 1000);
END;
/