Oracle PLSQL 从变量设置游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3346717/
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
Oracle PLSQL setting a cursor from a variable
提问by Eddieb
Im new to cursors in Oracle. I have a piece of SQL that is contained in a variable. I want to open a cursor with this sql. How do I do this? Seems simple but all the examples I find just have the sql typed directly below the "open cursor_name for" statement.
我是 Oracle 游标的新手。我有一段包含在变量中的 SQL。我想用这个 sql 打开一个游标。我该怎么做呢？看起来很简单，但我发现的所有示例都在“open cursor_name for”语句的正下方键入了 sql。
Here is what I would like to run (assume I have variable v_sql with my sql query):
这是我想要运行的（假设我的 sql 查询有变量 v_sql）：
open my_cursor for v_sql;
Oracle doesnt like this though. I also tried
甲骨文不喜欢这个虽然。我也试过
open my_cursor for
execute immediate v_sql;
Help please.
请帮忙。
回答by Rajesh Chamarthi
You need to declare it as a ref cursor and then open it for the your SQL statement. Please look at the example below. This, of course, is assuming you do not have any input bindings to your sql.
您需要将其声明为引用游标，然后为您的 SQL 语句打开它。请看下面的例子。当然，这假设您的 sql 没有任何输入绑定。
sql> ed
Wrote file afiedt.buf
1 declare
2 c1 sys_refcursor;
3 v_empno number;
4 v_ename varchar2(30);
5 begin
6 open c1 for 'select empno, ename from emp';
7 loop
8 fetch c1 into v_empno, v_ename;
9 dbms_output.put_line(v_empno || '--' || v_ename);
10 exit when c1%notfound;
11 end loop;
12 close c1;
13* end;
sql> /
7369--SMITH
7499--ALLEN
7521--WARD
7566--JONES
7654--MARTIN
7698--BLAKE
7782--CLARK
7788--SCOTT
7839--KING
7844--TURNER
7876--ADAMS
7900--JAMES
7902--FORD
7934--MILLER
7934--MILLER
Check this link... http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10807/11_dynam.htm#i13057
检查此链接... http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10807/11_dynam.htm#i13057
回答by Dan
The first snippet you have will work fine, as long as v_sql is a VARCHAR and my_cursor is declared as a REF CURSOR. You can then FETCH from that just like you would with a static cursor.
只要 v_sql 是一个 VARCHAR 并且 my_cursor 被声明为一个 REF CURSOR，你的第一个片段就可以正常工作。然后您可以像使用静态游标一样从中获取 FETCH。
But as OMG Ponies says, you have to be careful about where your SQL is coming from.
但正如 OMG Ponies 所说，您必须小心 SQL 的来源。
回答by Harrison
OMG Ponies is completely correct,
OMG 小马完全正确，
but here is just a different way to do the same thing
但这里只是做同样事情的不同方式
Var X Refcursor;
Begin
Open :X For
Select 1 Num, 'b' Co
From Dual
Union
Select 2 Num, 'c' Co
From Dual;
end;
/
print x;
Note when you do anything in Oracle like opening cursors or whatnot you will need to be within a BEGIN/END and you cannot simply do:
请注意，当您在 Oracle 中执行任何操作（例如打开游标等）时，您将需要在 BEGIN/END 中，并且您不能简单地执行以下操作：
Var X Refcursor;
Open X For
Select 1 Num, 'b' Co
From Dual
Union
Select 2 Num, 'c' Co
From Dual;
This will not Work! You must enclose the OPEN cursor within a BEGIN/END block (be it an anonomous block or a procedure...)
这行不通！您必须将 OPEN 游标括在 BEGIN/END 块中（无论是匿名块还是过程...）
Create or replace Procedure Ccc(X Out sys_Refcursor)
As
begin
Open X For
Select 1 Num, 'b' Co
From Dual
Union
Select 2 Num, 'c' Co
From Dual;
End Ccc;
/
Var X Refcursor;
Begin
Ccc(:X);
End;
/
print x;
note the :x in the begin/end in the anonomous blocks is to tell the sql engine you are utilizing a variable created outside the block. within packages/procs it is unnecessary.
请注意，匿名块中开始/结束中的 :x 是告诉 sql 引擎您正在使用在块外创建的变量。在包/过程中，这是不必要的。
