On May 8, 2:02=A0pm, Urs Metzger <u...@[EMAIL PROTECTED]
> wrote:
> 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;'
> >>> =A0 =A0 i :=3D x.first;
> >>> =A0 =A0 loop
> >>> =A0 =A0 =A0 =A0 dbms_output.put_line(i);
> >>> =A0 =A0 =A0 =A0 exit when i =3D x.last;
> >>> =A0 =A0 =A0 =A0 i :=3D x.next(i);
> >>> =A0 =A0 end loop;
> >>> Many TIA!
> >>> Mark
> >> This will break for empty collections. =A0You can do
>
> >> SQL> set serverout on
> >> SQL> DECLARE =A0TYPE population_type IS TABLE OF NUMBER INDEX BY
> >> VARCHAR2(64);
> >> =A0 2 =A0 =A0continent_population population_type;
> >> =A0 3 =A0 =A0which VARCHAR2(64);
> >> =A0 4 =A0BEGIN
> >> =A0 5 =A0 =A0dbms_output.put_line('-----------');
> >> =A0 6
> >> =A0 7 =A0 =A0which :=3D continent_population.FIRST;
> >> =A0 8 =A0 =A0while which is not null loop
> >> =A0 9 =A0 =A0 =A0dbms_output.put_line(which || ' -> ' ||
> >> continent_population(which));
> >> =A010 =A0 =A0 =A0which :=3D continent_population.NEXT(which);
> >> =A011 =A0 =A0end loop;
> >> =A012
> >> =A013 =A0 =A0dbms_output.put_line('-----------');
> >> =A014
> >> =A015 =A0 =A0continent_population('Australia') :=3D 30000000;
> >> =A016 =A0 =A0continent_population('Antarctica') :=3D 1000; -- Creates
n=
ew
> >> entry
> >> =A017 =A0 =A0continent_population('Antarctica') :=3D 1001; --
Replaces
> >> previous value
> >> =A018
> >> =A019 =A0 =A0which :=3D continent_population.FIRST;
> >> =A020 =A0 =A0while which is not null loop
> >> =A021 =A0 =A0 =A0dbms_output.put_line(which || ' -> ' ||
> >> continent_population(which));
> >> =A022 =A0 =A0 =A0which :=3D continent_population.NEXT(which);
> >> =A023 =A0 =A0end loop;
> >> =A024
> >> =A025 =A0 =A0dbms_output.put_line('-----------');
> >> =A026 =A0END;
> >> =A027 =A0/
> >> -----------
> >> -----------
> >> Antarctica -> 1001
> >> Australia -> 30000000
> >> -----------
>
> >> PL/SQL procedure successfully completed.
>
> >> SQL>
>
> >> Cheers
>
> >> robert
>
> >>
seehttp://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/colle=
c......
>
> > I think I would consider the For I in 1..n construct
>
> > UT1 > l
> > =A0 1 =A0declare
> > =A0 2 =A0type t_array is table of varchar2(10) index by
binary_integer;
> > =A0 3 =A0t_list =A0 t_array;
> > =A0 4 =A0begin
> > =A0 5 =A0t_list(1) :=3D 'one';
> > =A0 6 =A0t_list(2) :=3D 'two';
> > =A0 7 =A0t_list(3) :=3D 'three';
> > =A0 8 =A0t_list(4) :=3D 'four';
> > =A0 9 =A0t_list(5) :=3D 'five';
> > =A010 =A0for I in 1..t_list.last loop
> > =A011 =A0 =A0dbms_output.put_line(t_list(I));
> > =A012 =A0end loop;
> > =A013* 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
> =A0 =A02 =A0 =A0 type t_array is table of varchar2(10) index by
binary_int=
eger;
> =A0 =A03 =A0 =A0 t_list =A0 t_array;
> =A0 =A04 =A0begin
> =A0 =A05 =A0 =A0 t_list(1) :=3D 'one';
> =A0 =A06 =A0 =A0 t_list(2) :=3D 'two';
> =A0 =A07 =A0 =A0 -- t_list(3) :=3D 'three';
> =A0 =A08 =A0 =A0 t_list(4) :=3D 'four';
> =A0 =A09 =A0 =A0 t_list(5) :=3D 'five';
> =A0 10 =A0 =A0 for I in 1..t_list.last loop
> =A0 11 =A0 =A0 =A0 =A0dbms_output.put_line(t_list(I));
> =A0 12 =A0 =A0 end loop;
> =A0 13 =A0end;
> =A0 14 =A0/
> 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
> =A0 =A02 =A0 =A0 type t_array is table of varchar2(10) index by
binary_int=
eger;
> =A0 =A03 =A0 =A0 t_list =A0 t_array;
> =A0 =A04 =A0 =A0 i =A0 =A0 =A0 =A0binary_integer;
> =A0 =A05 =A0begin
> =A0 =A06 =A0 =A0 t_list(1) :=3D 'one';
> =A0 =A07 =A0 =A0 t_list(2) :=3D 'two';
> =A0 =A08 =A0 =A0 t_list(4) :=3D 'four';
> =A0 =A09 =A0 =A0 t_list(50) :=3D 'fifty';
> =A0 10
> =A0 11 =A0 =A0 i :=3D t_list.first;
> =A0 12 =A0 =A0 while i is not null loop
> =A0 13 =A0 =A0 =A0 =A0dbms_output.put_line(t_list(I));
> =A0 14 =A0 =A0 =A0 =A0i :=3D t_list.next(i);
> =A0 15 =A0 =A0 end loop;
> =A0 16 =A0end;
> =A0 17 =A0/
> 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 cir***stances and not just with the data in front of you.
Thanks.
-- Mark D Powell --
|
