10-13. Navigating Collections
Problem
You need a routine to display sales totaled by region, which is stored in a collection of numbers, but the collection is indexed by a character string. Using a LOOP from 1 to the maximum size will not work.
Solution
Use the FIRST and LAST method to traverse the collection allowing PL/SQL to supply the proper index values. In this example, sales amounts are stored in a TABLE indexed by a string.
DECLARE
TYPE ntype IS TABLE OF NUMBER INDEX BY VARCHAR2(5);
nlist ntype;
idx VARCHAR2(5);
total integer := 0;
BEGIN
nlist('North') := 100;
nlist('South') := 125;
nlist('East') := 75;
nlist('West') := 75;
idx := nlist.FIRST;
LOOP
EXIT WHEN idx is null;
DBMS_OUTPUT.PUT_LINE (idx || ' = ' || nlist(idx) );
total := total + nlist(idx);
idx := nlist.NEXT(idx);
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Total: ' || total);
END;
Executing this block of code produces the following results:
East = 75
North = 100
South = 125
West = 75
Total: 375
How It Works
The FIRST method returns the lowest index value in the collection. In this case, the value is East, because the collection is sorted alphabetically. The loop is entered with idx initialized to the first value in the collection. The NEXT method returns the next index value alphabetically in the collection. The loop continues executing until the NEXT method returns a null value, which occurs after the last index value in the collect is retrieved.
To traverse the collection in reverse alphabetical order, simply initialize the idx value to nlist.LAST. Then replace the nlist.NEXT with nlist.PRIOR.
Note The FIRST, NEXT, PRIOR, and LAST methods are most useful with associative arrays but also work with collections indexed by an integer.