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

On May 7, 8:19 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On May 7, 4:37 pm, "stephen O'D" <stephen.odonn..._at_gmail.com> wrote:
>
>
>
>
>
> > On May 7, 5:28 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
> > > 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 --
>
> > I am fairly sure if you do
>
> > for i in 1 .. v_array.count loop
> >   null;
> > end loop;
>
> > It will happily handle an empty array (don't have access to Oracle
> > right now to check).- Hide quoted text -
>
> > - Show quoted text -
>
> Your are probably correct.  My quick test failed however using
> table.count instead of table.last like I did would probably be a
> better idea and would be worth testing.  It has been over 3 years
> since I wrote any PL/SQL code worth mentioning.  After posting I
> though I should have tested table.first .. table.last also.  Maybe
> tomorrow I will find some spare time.
>
> -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -

I ran a test and a for loop with table.count works with an empty set. The same test with table.first .. table.last does not though first to last works fine with data.

Just use my previously posted code to duplicate.

• Mark D Powell --