Re: howto traverse an associative array

"bonminh lam" <hansmayer1962_at_hotmail.com> wrote in message news:3c6b1bcf.0312190656.64bb9831_at_posting.google.com...
> Hello,
>
> since Oracle 9i, it is possible to use associativ array (or index by
> table) as in the following example taken from Oracle's PL/SQL User's
> Guide:
>
> SQL> DECLARE
> 2 TYPE population_type IS TABLE OF NUMBER INDEX BY
> VARCHAR2(64);
> 3 country_population population_type;
> 4 continent_population population_type;
> 5 howmany NUMBER;
> 6 which VARCHAR2(64);
> 7 BEGIN
> 8 country_population('Greenland') := 100000;
> 9 country_population('Iceland') := 750000;
> 10 howmany := country_population('Greenland');
> 11
> 12 continent_population('Australia') := 30000000;
> 13 continent_population('Antarctica') := 1000; -- Creates new
> entry
> 14 continent_population('Antarctica') := 1001; -- Replaces
> previous value
> 15 which := continent_population.FIRST; -- Returns
> 'Antarctica'
> 16 dbms_output.put_line('which :'||which);
> 17 -- as that comes first alphabetically.
> 18 which := continent_population.LAST; -- Returns 'Australia'
> 19 dbms_output.put_line('which :'||which);
> 20 howmany :=
> continent_population(continent_population.LAST);
> 21 -- Returns the value corresponding to the last key, in this
> 22 -- case the population of Australia.
> 23 END;
> 24 /
> which :Antarctica
> which :Australia
>
> PL/SQL procedure successfully completed.
>
>
>
> My question is how to traverse or loop through for example the table
> variable country_population. Since the first und last predicate
> returns a varchar2 value, the syntax like this "for i in foo.first ..
> foo.last loop" does not work.
>
> I guess associative array is not designed to be used this way, but it
> would still be a nice-to-have thing.
>
> Anyone knows a solution?
>
> Bon-Minh Lam

There's probably a better construction, but it's done like this:

 which := continent_population.FIRST;
 FOR i IN 1 .. continent_population.COUNT  LOOP
  dbms_output.put_line('which :'||which);   which := continent_population.NEXT(which);  END LOOP;
 dbms_output.put_line(continent_population.COUNT);

Regards,
Neil Received on Fri Dec 19 2003 - 14:21:55 CST