1  CREATE OR REPLACE PROCEDURE give_raises_in_department (
 2     dept_in IN lots_of_employees.department_id%TYPE
 3   , newsal IN lots_of_employees.salary%TYPE
 4  )
 5  IS
 6     TYPE employee_aat IS TABLE OF lots_of_employees.employee_id%TYPE
 7        INDEX BY PLS_INTEGER;
 8     TYPE salary_aat IS TABLE OF lots_of_employees.salary%TYPE
 9        INDEX BY PLS_INTEGER;
10     TYPE hire_date_aat IS TABLE OF lots_of_employees.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     TYPE guide_aat IS TABLE OF PLS_INTEGER
18        INDEX BY PLS_INTEGER;
19
20     approved_list guide_aat;
21     denied_list guide_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 lots_of_employees
29         WHERE (department_id = dept_in OR dept_IN IS NULL);
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_list (indx) := indx;
40           ELSE
41              denied_list (indx) := indx;
42           END IF;
43        END LOOP;
44     END;
45
46     PROCEDURE add_to_history
47     IS
48     BEGIN
49        FORALL indx IN VALUES OF denied_list
50           INSERT INTO employee_history
51                       (employee_id
52                      , salary
53                      , hire_date, activity
54                       )
55                VALUES (employee_ids (indx)
56                      , salaries (indx)
57                      , hire_dates (indx)
58                      , 'RAISE DENIED'
59                       );
60     END;
61
62     PROCEDURE give_the_raise
63     IS
64     BEGIN
65        FORALL indx IN VALUES OF approved_list
66           UPDATE lots_of_employees
67              SET salary = newsal
68                , hire_date = hire_dates(indx)
69            WHERE employee_id = 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_department4;