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

Date: Fri, 9 May 2008 08:57:11 -0700 (PDT)

Message-ID: <685dc2e1-718f-4e7a-bd4f-811d0ca6c60b@j22g2000hsf.googlegroups.com>

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