oracle PL/SQL - 如何创建条件游标？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3969066/
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
PL/SQL - How to create a conditional cursor?
提问by Topera
I need to have a conditional cursor, like:
我需要有一个条件游标，例如：
- If a row exists (using WHEN EXISTSor something like this), then my cursor is:
- CURSOR varCursor IS SELECT 1 a FROM DUAL;
- Else
- CURSOR varCursor IS SELECT 2 a FROM DUAL;
- 如果一行存在（使用WHEN EXISTS或类似的东西），那么我的光标是：
- CURSOR varCursor IS SELECT 1 a FROM DUAL;
- 别的
- 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;
回答by Harrison
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)
除非将其放入一个查询中（如 Tony 建议的那样），因为您需要一个游标结果，您可以这样做（这会将游标切换到您需要的逻辑 --> 一个游标解决方案）
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
回答by Tony Andrews
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.
但是，拥有 2 个游标并打开任何合适的游标会更简单，也可能更有效。
