Code Listing 2: Mimicking primary key and unique indexes

1  DECLARE

 2     TYPE name_t IS TABLE OF employee%ROWTYPE INDEX BY employee.last_name%TYPE;
 3
 4     TYPE id_t IS TABLE OF employee%ROWTYPE INDEX BY PLS_INTEGER;
 5
 6     by_name   name_t;

 7     by_ssn    name_t;
 8     by_id     id_t;
 9
10         ceo_name employee.last_name%TYPE := 'SMITH';
11
12     PROCEDURE load_arrays IS

13     BEGIN
14        FOR rec IN  (SELECT * FROM employee)
15        LOOP
16           -- Load up all three arrays in single pass to database table.
17           by_name (rec.last_name) := rec;
18           by_ssn (rec.ssn) := rec;
19           by_id (rec.employee_id) := rec;

20        END LOOP;
21     END;
22  BEGIN
23     load_arrays;
24
25     -- Now I can retrieve information by name or ID:
26
27     IF by_name (ceo_name).salary > by_id (7645).salary

28     THEN
29        make_adjustment (by_name);
30     END IF;
31  END;