Re: CURSOR QUESTION

From: Jurij Modic <jmodic_at_src.si>
Date: Thu, 17 Jun 1999 22:56:00 GMT
Message-ID: <37697363.2513534@news.siol.net>

On Thu, 17 Jun 1999 10:27:45 -0400, Kenneth C Stahl <kstahl_at_lucent.com> wrote:

>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;

I believe you wrote the above snipped of code in a big rush, as it is logically incorrect and uses (totally unnecessary) a GOTO command which is commonly accepted as a "bad programming practice".

Instead of

> 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;

one should use much simpler form of:

       for i in c1 loop
           if i.col1 != 'some_value' then
             some_condition = false;
           end if;
       end loop;

And why I think your solutions for Mike's problem is logically false? Because if the specific condition arises inside a cursor loop, your solution simply forces a jump to the end of the loop statement, but that doesn't mean that the loop will be exited and a cursor closed. It will continue with fetching the next record from a cursor....

If you want to terminate the cursor end exit the loop, you must use an EXIT command.

declare

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

    while some_condition loop

        some_condition := false;
        for i in c1 loop
            if i.col1 = 'some_value' then
              some_condition := true;
              exit;
            end if;
            .......
        end loop;

    end loop;
end;

(Of course, if the above block wouldn't contain some additional processing logic, it would be a perfect candidate for an infinite loop.....)

>Ken

Regards,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Thu Jun 17 1999 - 17:56:00 CDT