Re: most idiomatic way to iterate over an associative array?

From: Urs Metzger <urs_at_ursmetzger.de>
Date: Thu, 08 May 2008 20:02:58 +0200
Message-ID: <fvvf7m$tqn$1@online.de>

Mark D Powell schrieb:

> On May 7, 4:07 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:

>> On May 7, 6:51 am, m..._at_pixar.com wrote:
>>
>>> This is what I'm doing now... is there a better way?
>>> It would be great if there were some construct such
>>> as 'for i in x begin ... end;'
>>> i := x.first;
>>> loop
>>> dbms_output.put_line(i);
>>> exit when i = x.last;
>>> i := x.next(i);
>>> end loop;
>>> Many TIA!
>>> Mark
>> This will break for empty collections. You can do
>>
>> SQL> set serverout on
>> SQL> DECLARE TYPE population_type IS TABLE OF NUMBER INDEX BY
>> VARCHAR2(64);
>> 2 continent_population population_type;
>> 3 which VARCHAR2(64);
>> 4 BEGIN
>> 5 dbms_output.put_line('-----------');
>> 6
>> 7 which := continent_population.FIRST;
>> 8 while which is not null loop
>> 9 dbms_output.put_line(which || ' -> ' ||
>> continent_population(which));
>> 10 which := continent_population.NEXT(which);
>> 11 end loop;
>> 12
>> 13 dbms_output.put_line('-----------');
>> 14
>> 15 continent_population('Australia') := 30000000;
>> 16 continent_population('Antarctica') := 1000; -- Creates new
>> entry
>> 17 continent_population('Antarctica') := 1001; -- Replaces
>> previous value
>> 18
>> 19 which := continent_population.FIRST;
>> 20 while which is not null loop
>> 21 dbms_output.put_line(which || ' -> ' ||
>> continent_population(which));
>> 22 which := continent_population.NEXT(which);
>> 23 end loop;
>> 24
>> 25 dbms_output.put_line('-----------');
>> 26 END;
>> 27 /
>> -----------
>> -----------
>> Antarctica -> 1001
>> Australia -> 30000000
>> -----------
>>
>> PL/SQL procedure successfully completed.
>>
>> SQL>
>>
>> Cheers
>>
>> robert
>>
>> seehttp://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collec...http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collec...

> 
> I think I would consider the For I in 1..n construct
> 
> UT1 > l
>   1  declare
>   2  type t_array is table of varchar2(10) index by binary_integer;
>   3  t_list   t_array;
>   4  begin
>   5  t_list(1) := 'one';
>   6  t_list(2) := 'two';
>   7  t_list(3) := 'three';
>   8  t_list(4) := 'four';
>   9  t_list(5) := 'five';
>  10  for I in 1..t_list.last loop
>  11    dbms_output.put_line(t_list(I));
>  12  end loop;
>  13* end;
> UT1 > /
> one
> two
> three
> four
> five
> 
> PL/SQL procedure successfully completed.
> 
> Again as Robert warned in his solution the array should not be empty.
> 
> HTH -- Mark D Powell --

Mark,

this will work if - and only if - your array has no gaps:

SQL> declare

   2     type t_array is table of varchar2(10) index by binary_integer;
   3     t_list   t_array;
   4  begin
   5     t_list(1) := 'one';
   6     t_list(2) := 'two';
   7     -- t_list(3) := 'three';
   8     t_list(4) := 'four';
   9     t_list(5) := 'five';
  10     for I in 1..t_list.last loop
  11        dbms_output.put_line(t_list(I));
  12     end loop;

  13 end;
  14 /
one
two
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 11

your approach won't work for arrays with index by varchar2.

The way to do is:

SQL> declare

   2     type t_array is table of varchar2(10) index by binary_integer;
   3     t_list   t_array;
   4     i        binary_integer;
   5  begin
   6     t_list(1) := 'one';
   7     t_list(2) := 'two';
   8     t_list(4) := 'four';
   9     t_list(50) := 'fifty';
  10
  11     i := t_list.first;
  12     while i is not null loop
  13        dbms_output.put_line(t_list(I));
  14        i := t_list.next(i);
  15     end loop;

  16 end;
  17 /
one
two
four
fifty

This will also work with empty collections or varchar2 indexes.

Hth,
Urs Metzger Received on Thu May 08 2008 - 13:02:58 CDT