PL/SQL - How to create a conditional cursor?

Question

Hi,

I need to have a conditional cursor, like:

• If a row exists (using WHEN EXISTS or something like this), then my cursor is:
  • CURSOR varCursor IS SELECT 1 a FROM DUAL;
• Else
  • CURSOR varCursor IS SELECT 2 a FROM DUAL;

But look, I don't want to change a column result, I want to change the entire cursor.

Bellow I put a bigger example.

Thanks!

---

See:

SET serveroutput ON SIZE 900000;
DECLARE
  CURSOR varCursor IS SELECT 1 a FROM DUAL;
  -- CURSOR varCursor IS SELECT 2 a FROM DUAL;
BEGIN
  FOR varRow IN varCursor LOOP
    dbms_output.put_line('row: ' || varRow.a);
  END LOOP;
  dbms_output.put_line('Done.');  
END;

Answer 1

Literally, you could do this:

CURSOR varCursor IS SELECT 1 a FROM DUAL WHERE EXISTS (...)
                    UNION
                    SELECT 2 a FROM DUAL WHERE NOT EXISTS (...);

However, it would be simpler and perhaps more efficient to have 2 cursors and open whichever is appropriate.

Answer 2

barring putting it into one query (as Tony recommends) since you want one cursor result, you can do it as such (this will switch the cursor to the logic you need --> one cursor solution)

DECLARE
  PROCEDURE CURSORCHOICE(ITEM IN NUMBER) IS
      L_REFCUR SYS_REFCURSOR;
    returnNum number;
    BEGIN
        IF NVL(ITEM,0) > 0 THEN
            OPEN L_REFCUR FOR
            SELECT ITEM * level  FROM DUAL 
            CONNECT BY LEVEL < ITEM ;
        ELSE
            OPEN L_REFCUR FOR
            SELECT  ITEM -  LEVEL  FROM DUAL 
            connect by level < -1 * ITEM ;  
        END IF;
        dbms_output.put_line('Results to item ' || item);
      loop
         fetch l_refcur into returnNum;
         exit when l_refcur%notfound;
         dbms_output.put_line(returnNum);
      end loop;
      CLOSE L_REFCUR;

    END ;
BEGIN
CURSORCHOICE(5);
CURSORCHOICE(-5);
end ;
/

Results to item 5
5
10
15
20
Results to item -5
-6
-7
-8
-9