Can I set one cursor equal to another in a stored proc?

[ddiamond]

I have 2 static cursors, WITHOUT_UNITS_CUR and WITH_UNITS_CUR. Depending on the variable, V_INCLUDE_UNITS, I want to use one or the other. Currently, I am using an if statement everytime I need to reference one of the cursors. For example:

IF V_INCLUDE_UNITS = 0 THEN
  OPEN WITHOUT_UNITS_CUR ;
  FETCH WITHOUT_UNITS_CUR INTO V_NIMANU , V_NIMASE , V_NINACD , V_NINUCD ;
ELSE
  OPEN WITH_UNITS_CUR ;
  FETCH WITH_UNITS_CUR INTO V_NIMANU , V_NIMASE , V_NINACD , V_NINUCD ;
END IF ;

What I'd love to be able to do is assign a 3rd cursor to one of them once, and then use that 3rd cursor instead of constantly repeating the if-else block. For example:

IF V_INCLUDE_UNITS = 0 THEN
  MY_CUR = WITHOUT_UNITS_CUR ;
ELSE
  MY_CUR = WITH_UNITS_CUR ;
END IF ;

OPEN MY_CUR ;
FETCH MY_CUR INTO V_NIMANU , V_NIMASE , V_NINACD , V_NINUCD ;

Is that possible?

 

[MarcLodge]

Dan,
Don't think you can do what you want in the way that you are describing, although, as always, happy to be told I'm wrong.

What might be more possible, although haven't seen cursors obviously, is to combine both cursors into one, with a clever CASE statement.

Just a thought. Any chance?

Marc

 

[ddiamond]

Marc,

Yes I can combine them into one cursor. In fact that's how I originally coded it. My only problem was performance. The without_units cursor is much faster than the with_units cursor. Since 80% of the time the user doesn't want the units, I thought it was worth creating a separate cursor, without_units, that performed faster for that scenerio.

I also explored using a dynamic cursor, but that again took a performance hit because the system doesn't appear to cache dynamic sql.

- Dan

 

[MarcLodge]

Dan,
My next suggestion was going to be dynamic SQl but it looks like you're way ahead of me and have already ruled it out.

Think you are stuck with the if-else block.

Marc