PL / SQL - How to create a conditional cursor?

Question

I need to have a conditional cursor , for example:

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

But look, I do not want to change the result of the column, I want to change the entire cursor.

The following is an example.

Thank!

---

Cm:

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

forbidding you to put it in one query (as Tony recommends), since you want to get one cursor result, you can do it as such (this will switch the cursor to the desired logic → 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

Answer 2

:

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

, , 2 , .