SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
05-07-2008, 10:16 AM
| ||||
| ||||
 most idiomatic way to iterate over an associative array? 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 -- Mark Harrison Pixar Animation Studios      |
|
05-07-2008, 10:16 AM
| |||
| |||
| Re: most idiomatic way to iterate over an associative array? On May 7, 6:51 am, m...@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 see http://download.oracle.com/docs/cd/B...htm#sthref1022 http://download.oracle.com/docs/cd/B...htm#sthref1146 |
|
05-07-2008, 06:20 PM
| |||
| |||
| Re: most idiomatic way to iterate over an associative array? On May 7, 4:07*am, Robert Klemme <shortcut...@googlemail.com> wrote: > On May 7, 6:51 am, m...@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/B.../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 -- |
|
05-10-2008, 02:03 PM
| |||
| |||
| Re: most idiomatic way to iterate over an associative array? On May 7, 5:28 pm, Mark D Powell <Mark.Pow...@eds.com> wrote: > On May 7, 4:07 am, Robert Klemme <shortcut...@googlemail.com> wrote: > > > > > On May 7, 6:51 am, m...@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). |
|
05-10-2008, 02:03 PM
| |||
| |||
| Re: most idiomatic way to iterate over an associative array? On May 7, 4:37*pm, "stephen O'D" <stephen.odonn...@gmail.com> wrote: > On May 7, 5:28 pm, Mark D Powell <Mark.Pow...@eds.com> wrote: > > > > > > > On May 7, 4:07 am, Robert Klemme <shortcut...@googlemail.com> wrote: > > > > On May 7, 6:51 am, m...@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 -- |
|
05-10-2008, 02:03 PM
| |||
| |||
| Re: most idiomatic way to iterate over an associative array? On May 7, 8:19*pm, Mark D Powell <Mark.Pow...@eds.com> wrote: > On May 7, 4:37*pm, "stephen O'D" <stephen.odonn...@gmail.com> wrote: > > > > > > > On May 7, 5:28 pm, Mark D Powell <Mark.Pow...@eds.com> wrote: > > > > On May 7, 4:07 am, Robert Klemme <shortcut...@googlemail.com> wrote: > > > > > On May 7, 6:51 am, m...@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 -- |
|
05-10-2008, 02:03 PM
| |||
| |||
| Re: most idiomatic way to iterate over an associative array? Mark D Powell schrieb: > On May 7, 4:07 am, Robert Klemme <shortcut...@googlemail.com> wrote: >> On May 7, 6:51 am, m...@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/B.../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 |
|
05-10-2008, 02:04 PM
| |||
| |||
| Re: most idiomatic way to iterate over an associative array? On May 8, 7:02 pm, Urs Metzger <u...@ursmetzger.de> wrote: > Mark D Powell schrieb: > > > On May 7, 4:07 am, Robert Klemme <shortcut...@googlemail.com> wrote: > >> On May 7, 6:51 am, m...@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. |
|
05-11-2008, 08:41 PM
| ||||
| ||||
| Re: most idiomatic way to iterate over an associative array? On May 8, 2:02*pm, Urs Metzger <u...@ursmetzger.de> wrote: > Mark D Powell schrieb: > > > > > On May 7, 4:07 am, Robert Klemme <shortcut...@googlemail.com> wrote: > >> On May 7, 6:51 am, m...@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- Hide quoted text - > > - Show quoted text - The recommendation to use table.next to move throught the array is an excellent point whenever the array could be empty or have holes in it. Most of the time the arrays I have worked with are populated earlier in the code so there are never gaps between first and last. Still one should always try to write code that will work in all possible circumstances and not just with the data in front of you. Thanks. -- Mark D Powell -- |
Linear Mode
