oracle 带 IF 条件的 PL/SQL 游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26144230/
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 cursor with IF condition
提问by Shyam534
I Have below cursor in the code.
我在代码中有以下光标。
CURSOR cur1
IS
SELECT a, b, c, d,
FROM EMP;
BEGIN
--Stored procedure logic
END
This curosr is getting information from EMP table.
这个光标正在从 EMP 表中获取信息。
But I need to change is as per below
但我需要改变如下
- There is a table (Table1) with Key Value pairs.
- If the Table1 value is TRUE then the cursor should be created with STUDENT table
- If the table1 value is FALSE then the cursor should be created with EMP table.
- 有一个包含键值对的表 (Table1)。
- 如果 Table1 值为 TRUE，则应使用 STUDENT 表创建游标
- 如果 table1 值为 FALSE，则应使用 EMP 表创建游标。
I can check the Value in the Table1 as below
我可以检查表1中的值如下
select t.value into variable1 from Table1 t where s.key='xxxxx';
And I want write something like
我想写一些类似的东西
IF variable1 := 'true'
curosr created with STUDENT
ELSE
curosr created with EMP
END IF
BEGIN
--Stored procedure logic
END
How to do it?
怎么做？
回答by Rahul
Using if .. elseconstruct is not proper (neither supported). You can use REF cursorto achieve the same like below.
使用if .. else构造不正确（都不支持）。您可以使用以下REF cursor方法实现相同的效果。
DECLARE type cur1 REF CURSOR;
c1 cur1;
BEGIN
IF (variable1 := 'true') THEN
OPEN c1 FOR 'SELECT * FROM STUDENT';
ELSE
OPEN c1 FOR 'SELECT * FORM EMP';
END IF ;
END;
Idea taken from Oracle Community Forum Post
取自Oracle 社区论坛帖子的想法
NOTE:I didn't included the entire code block (I mean cursor processing, closing etc) cause the main concern here is "How he will declare/define conditional cursor". So, pointed that particular in my code snippet. Since, rest of the part like processing the cursor and closing can be directly be found in Oracle specification.
注意：我没有包含整个代码块（我的意思是游标处理、关闭等），因为这里的主要问题是“他将如何声明/定义条件游标”。所以，在我的代码片段中指出了这一点。因为，其余部分如处理游标和关闭可以直接在 Oracle 规范中找到。
For a complete code block, you can refer the answer given by Harsh
完整的代码块，可以参考Harsh给出的答案
回答by Allan
I would prefer to solve this without using dynamic SQL. If the code to process the results is the same for both tables, then it is reasonable to assume that the columns are the same (or equivalent) as well. My inclination would be to solve this using UNIONand sub-queries:
我更愿意在不使用动态 SQL 的情况下解决这个问题。如果处理两个表的结果的代码相同，那么假设列也相同（或等效）是合理的。我倾向于使用UNION和子查询来解决这个问题：
DECLARE
CURSOR cur1 IS
SELECT a, b, c, d
FROM emp
WHERE NOT EXISTS
(SELECT *
FROM table1
WHERE s.key = 'xxxxx' AND t.VALUE = 'true')
UNION ALL
SELECT a, b, c, d
FROM student
WHERE EXISTS
(SELECT *
FROM table1
WHERE s.key = 'xxxxx' AND t.VALUE = 'true');
BEGIN
--Stored procedure logic
END;
回答by Koga
In another way you can just keep two CURSORS for those two scenarios and OPEN them on the condition. Declaring two CURSORS will not affect to the performance; you should be careful when OPEN a CURSOR and FETCHING from it.
在另一种方式中，您可以为这两个场景保留两个 CURSORS 并在条件下打开它们。声明两个 CURSORS 不会影响性能；打开 CURSOR 并从中获取时应该小心。
PROCEDURE Get_Details_On_Condition ( name_ OUT VARCHAR2, isEmp IN BOOLEAN )
IS
CURSOR get_emp IS
SELECT name
FROM EMP;
CURSOR get_std IS
SELECT name
FROM STUDENT;
BEGIN
IF isEmp THEN
OPEN get_emp ;
FETCH get_emp INTO name_ ;
CLOSE get_emp ;
ELSE
OPEN get_std ;
FETCH get_std INTO name_ ;
CLOSE get_std ;
END IF;
RETURN name_;
END Get_Details_On_Condition;
回答by Harsh
The link provided by Rahul indicates the correct way to solve the problem. From the Oracle community forum post posted by Rahul, I have taken the code snippet through which the code could run successfully.
Rahul 提供的链接指出了解决问题的正确方法。从 Rahul 发布的 Oracle 社区论坛帖子中，我获取了代码可以成功运行的代码片段。
Rahul: Please do not take this as a redundant answer as I could not comment on your answer to help shyam to take the code snippet in the link posted by you.
Rahul：请不要将此作为多余的答案，因为我无法对您的答案发表评论，以帮助 shyam 获取您发布的链接中的代码片段。
Declare
TYPE cv_typ IS REF CURSOR;
cv cv_typ;
Begin
If(condition1 is TRUE) then
open cv FOR
'Select * from table_name1';
EXIT WHEN cv%NOTFOUND;
ELSE
open cv FOR
'Select * from table_name2';
EXIT WHEN cv%NOTFOUND;
End If;
CLOSE cv;
END;
Thanks & Regards, Harsh
感谢和问候，苛刻
