Re: Tricky Cursor Question

From: damorgan <dan.morgan_at_ci.seattle.wa.us>
Date: Wed, 27 Feb 2002 19:12:37 GMT
Message-ID: <3C7D2FA7.2AF80201@ci.seattle.wa.us>

You could use a REF CURSOR but I would advise against it as they have resource problems and should only be used for returning values to a calling program.

What I would do is as follows:

CURSOR a IS

   SELECT ... CURSOR b IS

   SELECT ... CURSOR c IS

   SELECT ... myrec a%ROWTYPE;

BEGIN
   IF something THEN

      OPEN a
   ELSIF something THEN

      OPEN b
   ELSIF something THEN

      OPEN c
   END IF;
   LOOP

      IF something THEN
         FETCH a INTO myrec;
         EXIT WHEN a%NOTFOUND;
      ELSIF something THEN
         FETCH b INTO myrec;
         EXIT WHEN b%NOTFOUND;
     ELSIF something THEN
         FETCH c INTO myrec;
         EXIT WHEN c%NOTFOUND;
      END IF;
      ... do stuff

   END LOOP
   repeat the IF statement to close the cursor you opened.

The only requirement is that the records created by the cursors be identical. Otherwise ...
three separate procs is likely the best.

Daniel Morgan

Ravi Lal wrote:

> Hi,
>
> I have three big cursors and depending on a value I would like to choose
> which one of them to loop with the same for-loop code... like this
>
> cursor1
> cursor2
>
> if value =1 then cursortouse = cursor1
> elsif value =2 then cursortouse = cursor1
> end
>
> for c_row in cursortouse loop
> same code
> end loop
>
> the only way to get round this seems to have a different procedure for each
> cursor but i dont think this is a good way to do this (the same code bit is
> quite large). variable cursors dont seem to help here. i need some type of
> cursor variable that can be assigned a cursor at runtime....
>
> any ideas, thanks in advance....
Received on Wed Feb 27 2002 - 13:12:37 CST