如何从执行动态 SQL 的 Oracle PL/SQL 匿名块返回结果集/游标？

Question

我有这个表：

ALLITEMS
---------------
ItemId  | Areas
---------------
1       | EAST
2       | EAST
3       | SOUTH
4       | WEST

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');

在 MSSQL 中，要从动态 SQL 中获取游标，我可以这样做：

DECLARE @v_sqlStatement VARCHAR(2000);
SET @v_Sqlstatement = 'SELECT * FROM ALLITEMS';
EXEC (@v_sqlStatement); --returns a resultset/cursor, just like calling SELECT 

在 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

**但我得到的是

匿名区块已完成”。

我如何让它返回光标？

（我知道如果我执行 AUTOPRINT，它会打印出 REFCURSOR 中的信息（它不会在上面的代码中打印，但这是另一个问题） ）

我将从代码（ODBC，C++）调用此动态SQL，并且我需要它返回游标如何？

Answer 1

您可以编写一个 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;

这将返回游标。

确保尽可能不要将 SELECT-String 放入 PL/SQL 中的引号中。将其放入字符串中意味着无法在编译时对其进行检查，并且每次使用时都必须对其进行解析。

---

如果您确实需要使用动态 SQL，您可以将查询放在单引号中：

  OPEN my_cursor FOR 'SELECT * FROM allitems';

每当调用函数时都必须解析该字符串，这通常会比较慢，并且会在运行时隐藏查询中的错误。

确保尽可能使用绑定变量，以避免 硬解析：

  OPEN my_cursor FOR 'SELECT * FROM allitems WHERE id = :id' USING my_id;

Answer 2

在 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 succÞs.

SQL> print x;

D
-
X

Answer 3

您应该能够将游标声明为绑定变量（在其他 DBMS 中称为参数），

就像 Vincent 所写的那样，您可以执行以下操作：

begin
  open :yourCursor
    for 'SELECT "'|| :someField ||'" from yourTable where x = :y'
      using :someFilterValue;
end;

您必须将 3 个变量绑定到该脚本。 “someField”的输入字符串、“someFilterValue”的值和“yourCursor”的游标（必须声明为输出变量）。

不幸的是，我不知道如何从 C++ 中做到这一点。 （不过，有人可以说对我来说是幸运的。;-)）

根据您使用的访问库，这可能是一种巨大的痛苦，也可能是直接的。

Answer 4

需要设置此设置：

SET SERVEROUTPUT ON