PL/SQL - How to create a conditional cursor?
12,924
Solution 1
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
Solution 2
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.
![Topera](https://i.stack.imgur.com/of4bM.png?s=256&g=1)
Author by
Topera
I like JVM languages: Java / Groovy / Kotlin and love clean and fast code. Addicted to DevOps.
Updated on June 04, 2022Comments
-
Topera about 2 years
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;
- If a row exists (using WHEN EXISTS or something like this), then my cursor is: