oracle 有条件地定义一个游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4864404/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Conditionally define a Cursor
提问by MikeTWebb
I have a Procedure in Oracle that takes a varchar2paramater. 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.
我在 Oracle 中有一个过程，它需要一个varchar2参数。根据该参数的值，我需要定义一个游标。游标将根据参数的值对不同的表进行操作。
I wanted to do something like below but it throws an error in the CURSORdefinition piece of code. Any ideas?
我想做类似下面的事情，但它在CURSOR定义代码段中引发错误。有任何想法吗？
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;
回答by Vincent Malgrat
you will need a REF CURSOR and open it conditionaly, for example:
您将需要一个 REF CURSOR 并有条件地打开它，例如：
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
回答by Justin Cave
I would probably code something like this (where the two loops may 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 could also use dynamic SQL to open the cursor but that tends to get more complicated, particularly if there are only two options.
您也可以使用动态 SQL 来打开游标，但这往往会变得更加复杂，尤其是在只有两个选项的情况下。
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;
...
回答by J. Chomel
You can even use the condition inside the implicit for loop. Without cursor declaration or SYS_REFCURSOR(I dislike them sorry) - I mean you can use your variables, here v_action, inside implicit cursor declaration:
您甚至可以在隐式 for 循环中使用条件。没有游标声明或SYS_REFCURSOR（对不起，我不喜欢他们） - 我的意思是你可以在这里使用你的变量，在v_action隐式游标声明中：
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;
