Как вернуть набор результатов/курсор из анонимного блока Oracle PL/SQL, который выполняет Dynamic SQL?

У меня есть эта таблица:

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 (она не печатает в коде выше, но это еще одна проблема))

Я буду вызывать этот Dynamic SQL из кода (ODBC, С++), и мне нужно, чтобы он вернул курсор.
Как мне это сделать? Я в тупике.

Ответ 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';

Эта строка должна анализироваться всякий раз, когда вызывается функция, которая обычно будет медленнее и скрывает ошибки в вашем запросе до времени выполнения.

Обязательно используйте bind-variables, где это возможно, чтобы избежать жестких партитур:

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

Ответ 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

Ответ 3

Вы должны уметь объявлять курсор как переменную связывания (называемую параметрами в другой СУБД)

как писал Винсент, вы можете сделать что-то вроде этого:

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

Вам нужно связать 3 vars с этим script. Строка ввода для "someField", значение для "someFilterValue" и курсор для "yourCursor", который должен быть объявлен как выход var.

К сожалению, я понятия не имею, как вы это сделаете с С++. (Можно сказать, к счастью, для меня, однако.;-))

В зависимости от того, какую библиотеку доступа вы используете, это может быть королевская боль или прямо.

Ответ 4

Этот параметр должен быть установлен:

SET SERVEROUTPUT ON