Re: CURSOR QUESTION

From: Kenneth C Stahl <kstahl_at_lucent.com>
Date: Thu, 17 Jun 1999 10:27:45 -0400
Message-ID: <376905E1.7268B6D6@lucent.com>

I see that you didn't get any useful responses. Here is a way to do what you want:

declare

    some_condition boolean := true;
    some_other_condition boolean := false;     cursor c1 is
    select *
    from mytable
begin

    while some_condition = true loop

        for i in c1 loop
             if i.col1 = 'some_value' then
                goto end_of_loop;
            end if;
            some_condition = false;
            <<end_of_loop>>
            null;
        end loop;

    end loop;
end;

Either one should do the trick for you. Make sure that you add error trapping and other checks to make sure that things are going ok. Also, my illustration is extremely rudimentary. You'd need to flesh it out with your application-specific logic.

Basically what happens is that you start looping through rows. When some specific condition arises as you process rows you exit out of the loop with the cursor and when that happens you go back to the outer loop which re-opens the cursor and starts processing rows again. If you make it through the entire scope of the cursor and that specific condition doesn't arise then you set the condition of the outer loop to false and the process stops.

To me, this is a pretty simple solution. I'm not sure of the motivation of the guys who said that you can't do it - maybe they've never been in the situation like you describe.

Ken

yarch_at_hotmail.com wrote:

> Help!!
> Can I 'reset' a cursor to the beginning? Currently
> I open the cursor and fetch....I need to go back to the beginning
> and loop thru it again.
> Thanks
> Mike
> yarch_at_hotmail.com
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Thu Jun 17 1999 - 09:27:45 CDT