Re: How to use a different cursors in a single FOR loop

Lee wrote:

> This is exactly why I posted the question. From what I could figure
> out, both by testing and by documentation, it did not seem possible.
>
> However, I posted the question in the hope that I missed something.
>
> Anyway thanks for the confirmation.
>
> Lee.
>
>
> Sybrand Bakker <sybrandb_at_hccnet.nl> wrote in message news:<rdpio09hmi3nm88lca0cnb3dji5h9m6597_at_4ax.com>...
>

>>On 3 Nov 2004 14:29:33 -0800, lduhl_at_corp.realcomp.com (Lee) wrote:
>>
>>
>>>I would like to
>>>be able to use this same procedure for a couple of diffenet
>>>situations. 
>>
>>
>>Why on earth? You are only adding overhead. And if you would have
>>studied the syntax of a cursor for loop at all, you would have known
>>it isn't possible and have avoided the question.

I've done it in the past. Here's how:

CURSOR a_cur IS
SELECT ... CURSOR b_cur IS
SELECT ... CURSOR c_cur IS
SELECT ... BEGIN
    ... a bunch of code:
    IF <some_condition> THEN
      OPEN a_cur;
    ELSIF <some_condition> THEN
      OPEN b_cur;
    ELSE
      OPEN c_cur;
    END IF;

     LOOP
       IF <some_condition> THEN
         FETCH a_cur INTO a_rec;
         EXIT WHEN a_cur%NOTFOUND;
       ELSIF ...
         ...
       ELSE
         ...
       END IF;

       <the common code here>

    END LOOP;
    COMMIT;
    IF <some_condition> THEN
      CLOSE a_cur;
    etc. etc. etc.

But basically I doubt there is much real reason to do this. I did it as an academic exercise not because it is a good design.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)