Oracle PL/SQL Collections and Associative Arrays
Associative Arrays
DECLARE TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64); country_population population_type; continent_population population_type; howmany NUMBER; which VARCHAR2(64); BEGIN country_population('Greenland') := 100000; country_population('Iceland') := 750000; howmany := country_population('Greenland'); continent_population('Australia') := 30000000; continent_population('Antarctica') := 1000; -- Creates new entry continent_population('Antarctica') := 1001; -- Replaces previous value dbms_output.put_line('Count: ' || continent_population.count); which := continent_population.FIRST; -- Returns 'Antarctica' -- as that comes first alphabetically. which := continent_population.LAST; -- Returns 'Australia' howmany := continent_population(continent_population.LAST); -- Returns the value corresponding to the last key, in this -- case the population of Australia. -- Traverse Continents Associative Array dbms_output.put_line('Elements in continent_population: ' || continent_population.count); which := continent_population.FIRST; WHILE which IS NOT NULL LOOP dbms_output.put_line(which || ': ' || continent_population(which)); which := continent_population.NEXT(which); END LOOP; -- Outputs: -- Count: 2 -- Elements in continent_population: 2 -- Antarctica: 1001 -- Australia: 30000000 END;
Extend Oracle PL/SQL Collection
DECLARE TYPE CourseList IS TABLE OF VARCHAR2(10); courses CourseList := CourseList(); BEGIN dbms_output.put_line('Initial count= ' || courses.count || ', first= ' || courses.first || ', last= ' || courses.last || ''); courses.extend(5); dbms_output.put_line('After extend(5) count= ' || courses.count || ', first= ' || courses.first || ', last= ' || courses.last || ''); dbms_output.put_line('After extend(5) count: ' || courses.count); END;
Initial count= 0, first= , last= After extend(5) count= 5, first= 1, last= 5 After extend(5) count: 5
