有条件地定义一个游标
问题描述：
我在Oracle中有一个使用varchar2参数的过程.基于该参数的值，我需要定义一个游标.游标将根据参数的值在不同的表上进行操作.
I have a Procedure in Oracle that takes a varchar2 paramater. Based on the value of that parameter, I need to define a cursor. The cursor will operate on different tables based on the value of the parameter.
我想做以下类似的事情，但是它在CURSOR定义代码段中引发了错误.有什么想法吗?
I wanted to do something like below but it throws an error in the CURSOR definition piece of code. 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;
答
您将需要REF CURSOR并有条件地将其打开，例如:
you will need a REF CURSOR and open it conditionaly, 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