Mark D Powell schrieb:
> On May 7, 4:07 am, Robert Klemme <shortcut...@[EMAIL PROTECTED]
> wrote:
>> On May 7, 6:51 am, m...@[EMAIL PROTECTED]
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/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
|
