Conditionally define a cursor

Question

I have a procedure in Oracle that takes a varchar2 parameter. Based on the value of this parameter, I need to define a cursor. The cursor will work in different tables based on the value of the parameter.

I wanted to do something like below, but this throws an error in the fragment of the CURSOR definition. Any ideas?

 PROCEDURE GET_RECORDS(v_action IN VARCHAR2) IS CURSOR get_records IS IF(v_action = 'DO THIS') THEN SELECT * from <THIS>; ELSE SELECT * from <THAT>; END IF; BEGIN OPEN get_records; FETCH get_records INTO v_thing; v_loop := 0; WHILE get_records%FOUND LOOP FETCH get_records INTO v_thing; END LOOP; CLOSE get_records; END; 

Answer 1

you need REF CURSOR and open it conditionally, for example:

 SQL> CREATE OR REPLACE PROCEDURE GET_RECORDS(v_action IN VARCHAR2) IS 2 v_thing VARCHAR2(10); 3 get_records SYS_REFCURSOR; 4 BEGIN 5 IF (v_action = 'DO THIS') THEN 6 OPEN get_records FOR 7 SELECT 1 FROM dual; 8 ELSE 9 OPEN get_records FOR 10 SELECT 2 FROM dual; 11 END IF; 12 13 LOOP 14 FETCH get_records INTO v_thing; 15 EXIT WHEN get_records%NOTFOUND; 16 /* do things */ 17 dbms_output.put_line(v_thing); 18 END LOOP; 19 CLOSE get_records; 20 END; 21 / Procedure created SQL> exec get_records ('DO THIS'); 1 PL/SQL procedure successfully completed SQL> exec get_records ('DO THAT'); 2 PL/SQL procedure successfully completed 

Answer 2

I would probably have a code like this (where two loops can call the same functions)

 BEGIN IF( v_action = 'DO THIS' ) THEN FOR this_cur IN (SELECT * FROM <THIS>) LOOP <<do something>> END LOOP; ELSE FOR that_cur IN (SELECT * FROM <THAT>) LOOP <<do something else>> END LOOP; END IF; END; 

You can also use dynamic SQL to open the cursor, but this tends to get complicated, especially if there are only two options.

 IS get_records SYS_REFCURSOR; l_sql_stmt VARCHAR2(100); BEGIN IF( v_action = 'DO THIS' ) THEN l_sql_stmt := 'SELECT * from <THIS>'; ELSE l_sql_stmt := 'SELECT * from <THAT>'; END IF; OPEN get_records FOR l_sql_stmt; ... 

Answer 3

You can even use a condition inside an implicit loop. Without a cursor declaration or SYS_REFCURSOR (I don’t like to excuse them) - I mean that you can use your variables here v_action , inside an implicit cursor declaration:

 BEGIN FOR this_cur IN ( SELECT * FROM <THIS> WHERE v_action = 'DO THIS' ) LOOP <<do something>> END LOOP; FOR that_cur IN ( SELECT * FROM <THIS> WHERE v_action <> 'DO THIS' ) LOOP <<do something else>> END LOOP; END IF; END;