Code Listing 2: Next—Speeding up with bulk processing

 1  CREATE OR REPLACE PROCEDURE give_raises_in_department (
 2     dept_in IN employee.department_id%TYPE
 3   , newsal IN employee.salary%TYPE
 4  )
 5  IS
 6     TYPE employee_aat IS TABLE OF employee.employee_id%TYPE
 7        INDEX BY PLS_INTEGER;
 8     TYPE salary_aat IS TABLE OF employee.salary%TYPE
 9        INDEX BY PLS_INTEGER;
10     TYPE hire_date_aat IS TABLE OF employee.hire_date%TYPE
11        INDEX BY PLS_INTEGER;
12
13     employee_ids employee_aat;
14     salaries salary_aat;
15     hire_dates hire_date_aat;
16
17     approved_employee_ids employee_aat;
18
19     denied_employee_ids employee_aat;
20     denied_salaries salary_aat;
21     denied_hire_dates hire_date_aat;
22
23     PROCEDURE retrieve_employee_info
24     IS
25     BEGIN
26        SELECT employee_id, salary, hire_date
27        BULK COLLECT INTO employee_ids, salaries, hire_dates
28          FROM employee
29         WHERE department_id = dept_in;
30     END;
31
32     PROCEDURE partition_by_eligibility
33     IS
34     BEGIN
35        FOR indx IN employee_ids.FIRST .. employee_ids.LAST
36        LOOP
37           IF comp_analysis.is_eligible (employee_ids (indx))
38           THEN
39              approved_employee_ids (indx) := employee_ids (indx);
40           ELSE
41              denied_employee_ids (indx) := employee_ids (indx);
42              denied_salaries (indx) := salaries (indx);
43              denied_hire_dates (indx) := hire_dates (indx);
44           END IF;
45        END LOOP;
46     END;
47
48     PROCEDURE add_to_history
49     IS
50     BEGIN
51        FORALL indx IN denied_employee_ids.FIRST .. denied_employee_ids.LAST
52           INSERT INTO employee_history
53                       (employee_id
54                      , salary
55                      , hire_date, activity
56                       )
57                VALUES (denied_employee_ids (indx)
58                      , denied_salaries (indx)
59                      , denied_hire_dates (indx), 'RAISE DENIED'
60                       );
61     END;
62
63     PROCEDURE give_the_raise
64     IS
65     BEGIN
66        FORALL indx IN approved_employee_ids.FIRST .. approved_employee_ids.LAST
67           UPDATE employee
68              SET salary = newsal
69            WHERE employee_id = approved_employee_ids (indx);
70     END;
71  BEGIN
72     retrieve_employee_info;
73     partition_by_eligibility;
74     add_to_history;
75     give_the_raise;
76 END give_raises_in_department;