如何从执行动态 SQL 的 Oracle PL/SQL 匿名块返回结果集/游标？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2153053/
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
How to return a resultset / cursor from a Oracle PL/SQL anonymous block that executes Dynamic SQL?
提问by Liao
I have this table:
我有这张表：
ALLITEMS
---------------
ItemId | Areas
---------------
1 | EAST
2 | EAST
3 | SOUTH
4 | WEST
The DDL:
DDL：
drop table allitems;
Create Table Allitems(ItemId Int,areas Varchar2(20));
Insert Into Allitems(Itemid,Areas) Values(1,'east');
Insert Into Allitems(ItemId,areas) Values(2,'east');
insert into allitems(ItemId,areas) values(3,'south');
insert into allitems(ItemId,areas) values(4,'east');
In MSSQL, to get a cursor from a dynamic SQL I can do:
在 MSSQL 中，要从动态 SQL 中获取游标，我可以执行以下操作：
DECLARE @v_sqlStatement VARCHAR(2000);
SET @v_Sqlstatement = 'SELECT * FROM ALLITEMS';
EXEC (@v_sqlStatement); --returns a resultset/cursor, just like calling SELECT
In Oracle, I need to use a PL/SQL Block:
在 Oracle 中，我需要使用 PL/SQL 块：
SET AUTOPRINT ON;
DECLARE
V_Sqlstatement Varchar2(2000);
outputData SYS_REFCURSOR;
BEGIN
V_Sqlstatement := 'SELECT * FROM ALLITEMS';
OPEN outputData for v_Sqlstatement;
End;
--result is : anonymous block completed
**But all I get is
**但我得到的只是
anonymous block completed".
匿名块完成”。
How do I get it to return the cursor?
我如何让它返回光标？
(I know that if I do AUTOPRINT, it will print out the information in the REFCURSOR (it's not printing in the code above, but thats another problem))
（我知道如果我做 AUTOPRINT，它会打印出 REFCURSOR 中的信息（上面的代码中没有打印，但那是另一个问题））
I will be calling this Dynamic SQL from code (ODBC,C++), and I need it to return a cursor. How?
我将从代码（ODBC、C++）中调用这个动态 SQL，我需要它来返回一个游标。如何？
回答by Peter Lang
You can write a PL/SQL function to return that cursor (or you could put that function in a package if you have more code related to this):
您可以编写一个 PL/SQL 函数来返回该游标（或者，如果您有更多与此相关的代码，您可以将该函数放在一个包中）：
CREATE OR REPLACE FUNCTION get_allitems
RETURN SYS_REFCURSOR
AS
my_cursor SYS_REFCURSOR;
BEGIN
OPEN my_cursor FOR SELECT * FROM allitems;
RETURN my_cursor;
END get_allitems;
This will return the cursor.
这将返回光标。
Make sure not to put your SELECT-String into quotes in PL/SQL when possible. Putting it in strings means that it can not be checked at compile time, and that it has to be parsed whenever you use it.
SELECT如果可能，请确保不要将-String 放入 PL/SQL 中的引号中。将它放在字符串中意味着在编译时无法检查它，并且在使用它时必须对其进行解析。
If you really need to use dynamic SQL you can put your query in single quotes:
如果您确实需要使用动态 SQL，则可以将查询放在单引号中：
OPEN my_cursor FOR 'SELECT * FROM allitems';
This string has to be parsed whenever the function is called, which will usually be slower and hides errors in your query until runtime.
每当调用函数时都必须解析此字符串，这通常会变慢并在运行时之前隐藏查询中的错误。
Make sure to use bind-variables where possible to avoid hard parses:
确保尽可能使用绑定变量以避免硬解析：
OPEN my_cursor FOR 'SELECT * FROM allitems WHERE id = :id' USING my_id;
回答by Vincent Malgrat
in SQL*Plus you could also use a REFCURSORvariable:
在 SQL*Plus 中，您还可以使用REFCURSOR变量：
SQL> VARIABLE x REFCURSOR
SQL> DECLARE
2 V_Sqlstatement Varchar2(2000);
3 BEGIN
4 V_Sqlstatement := 'SELECT * FROM DUAL';
5 OPEN :x for v_Sqlstatement;
6 End;
7 /
Procúdure PL/SQL terminúe avec succTs.
SQL> print x;
D
-
X
回答by Robert Giesecke
You should be able to declare a cursor to be a bind variable (called parameters in other DBMS')
您应该能够将游标声明为绑定变量（在其他 DBMS 中称为参数）
like Vincent wrote, you can do something like this:
就像文森特写的那样，你可以做这样的事情：
begin
open :yourCursor
for 'SELECT "'|| :someField ||'" from yourTable where x = :y'
using :someFilterValue;
end;
You'd have to bind 3 vars to that script. An input string for "someField", a value for "someFilterValue" and an cursor for "yourCursor" which has to be declared as output var.
您必须将 3 个变量绑定到该脚本。“someField”的输入字符串、“someFilterValue”的值和“yourCursor”的游标必须声明为输出变量。
Unfortunately, I have no idea how you'd do that from C++. (One could say fortunately for me, though. ;-) )
不幸的是，我不知道你会如何从 C++ 中做到这一点。（不过，可以说我很幸运。;-)）
Depending on which access library you use, it might be a royal pain or straight forward.
根据您使用的访问库，这可能是一种痛苦或直接的。
回答by jon
This setting needs to be set:
这个设置需要设置：
SET SERVEROUTPUT ON
