most idiomatic way to iterate over an associative array?

#1

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

#2

On May 7, 6:51 am, m... (AT) pixar (DOT) com wrote:

Quote:

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

#3

On May 7, 6:51 am, m... (AT) pixar (DOT) com wrote:

Quote:

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

#4

On May 7, 6:51 am, m... (AT) pixar (DOT) com wrote:

Quote:

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

#5

On May 7, 6:51 am, m... (AT) pixar (DOT) com wrote:

Quote:

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

#6

On May 7, 4:07*am, Robert Klemme <shortcut... (AT) googlemail (DOT) com> wrote:

Quote:

On May 7, 6:51 am, m... (AT) pixar (DOT) 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 --

#7

On May 7, 4:07*am, Robert Klemme <shortcut... (AT) googlemail (DOT) com> wrote:

Quote:

On May 7, 6:51 am, m... (AT) pixar (DOT) 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 --

#8

On May 7, 4:07*am, Robert Klemme <shortcut... (AT) googlemail (DOT) com> wrote:

Quote:

On May 7, 6:51 am, m... (AT) pixar (DOT) 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 --

#9

On May 7, 4:07*am, Robert Klemme <shortcut... (AT) googlemail (DOT) com> wrote:

Quote:

On May 7, 6:51 am, m... (AT) pixar (DOT) 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 --

#10

On May 7, 5:28 pm, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:

Quote:

On May 7, 4:07 am, Robert Klemme <shortcut... (AT) googlemail (DOT) com> wrote:

On May 7, 6:51 am, m... (AT) pixar (DOT) 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).

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

most idiomatic way to iterate over an associative array?

comp.databases.oracle.misc comp.databases.oracle.misc

most idiomatic way to iterate over an associative array? - 05-06-2008 , 11:51 PM

Re: most idiomatic way to iterate over an associative array? - 05-07-2008 , 03:07 AM

Re: most idiomatic way to iterate over an associative array? - 05-07-2008 , 03:07 AM

Re: most idiomatic way to iterate over an associative array? - 05-07-2008 , 03:07 AM

Re: most idiomatic way to iterate over an associative array? - 05-07-2008 , 03:07 AM

Re: most idiomatic way to iterate over an associative array? - 05-07-2008 , 11:28 AM

Re: most idiomatic way to iterate over an associative array? - 05-07-2008 , 11:28 AM

Re: most idiomatic way to iterate over an associative array? - 05-07-2008 , 11:28 AM

Re: most idiomatic way to iterate over an associative array? - 05-07-2008 , 11:28 AM

Re: most idiomatic way to iterate over an associative array? - 05-07-2008 , 03:37 PM