Re: conditional cursor selection

In article <f3159b28.0108190821.3b59f910_at_posting.google.com>, sgelberg_at_optonline.net says...
>
>cursor1.........
> WHERE (a.status in ('V', 'A')) and
> (a.symbol = b.symbol) AND
> (a.ord_type = 'L') AND
> ((a.side = 'B') AND (a.limit_price >= b.bid_price)) OR
> ((a.side = 'A') AND (a.limit_price <= b.ask_price))
>cursor2.........
> WHERE (a.status in ('V', 'A')) and
> (a.symbol = b.symbol) AND
> (a.ord_type = 'L') AND
> ((a.side = 'B') AND (a.limit_price < b.mid_price)) OR
> ((a.side = 'A') AND (a.limit_price > b.mid_price))
>cursor3.........
> WHERE (a.status in ('V', 'A')) and
> (a.symbol = b.symbol) AND
> (a.ord_type = 'L') AND
> ((a.side = 'B') AND (a.limit_price >= b.bidrel_price))
>OR
> ((a.side = 'A') AND (a.limit_price <= b.askrel_price))
>
>I would like to select one cursor or the other based upon some value,
>for example (psuedocode):
>
>If (X) then
> FOR activeRec in cursor1 LOOP
>else
>If (Y) then
> FOR activeRec in cursor1 LOOP
>else
>If (Z) then
> FOR activeRec in cursor1 LOOP
>
>Any thoughts..dynamic sql, use open and fetch instead of for...

ref cursors are perfect for this:

declare

   type rc is ref cursor;

   l_cursor rc;
begin

   if ( x ) then

        open l_cursor for select ....;
   elsif ( y ) then

        open l_cursor for select ....;
   elsif ( z ) then

        open l_cursor for select ....;
   else

        whatever...
   end if;  

   loop

       fetch l_cursor into ...;
       exit when l_cursor%notfound;

   end loop
   close l_cursor;

end;

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp