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

On May 8, 7:02 pm, Urs Metzger <u..._at_ursmetzger.de> wrote:
> 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......
>
> > 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

I think we can conclude that arrays are a pain in PLSQL!

More seriously, you really need to know the pitfalls when using them.

I tend to use

for i in 1 .. array.count loop
  ...
end loop;

When I know its an array with no gaps starting at 1.

I tend to use index by varchar2 arrays as hash lookup tables so never really iterate over them. Received on Fri May 09 2008 - 10:57:11 CDT