Re: PL/SQL For-loops vs explicit cursors

Eric Hartzenberg <Eric_at_erichome.demon.co.uk> wrote:

>Brothers & Sisters in Oracle:
>
> declare cursor mycursor is select * from mytable;
 

> Method 1:
 

> for c1 in mycursor loop
> ...
> ...
> end loop;
 

> Method 2:
 

> open mycursor;
> loop
> fetch mycursor into xyz;
> exit when mycursor%notfound;
> ...
> ...
> end loop;
> close mycursor;
>
 

>I find method 1 more concise & readable. Does anyone have any strong feelings on
>the subject? This may appear a little feeble to you, but it's been bugging me

Right, method 1 IS short and sweet, and if every thing goes well, its the same as method 2.

But never forget Murphy's Law.
Method 2 leaves room for you to replace the fetch line with

<<fetch_mycursor>>
begin
  fetch mycursor into xyz;
exception
  when ? then begin ... end;
  when others raise;
end fetch_mycursor;

Now you can have an opportunity for error recovery, both from Oracle system type errors, and from problematic data.

--
Doug Henderson, Glen Coulee Consulting, Calgary, Alberta, Canada
[Sent using Free Agent 1.0]