Как вернуть строки из блока declare/begin/end в Oracle?

Я хочу вернуть строки из инструкции select в блоке declare/begin/end. Я могу сделать это в T-SQL, но я хотел бы знать, как это сделать в PL/SQL.

Код выглядит примерно так:

declare
     blah number := 42;
begin
     select *
     from x
     where x.value = blah;
end;

Ответ 1

Анонимный блок PL/SQL, как тот, который вы показали, не может "вернуть" что-либо. Однако он может взаимодействовать с вызывающим абонентом посредством переменных привязки.

Поэтому метод, который я использовал бы в этом случае, должен был бы объявить ссылку на курсор, открыть его в блоке PL/SQL для требуемого запроса и позволить вызывающему приложению извлекать из него строки. В SQLPlus это будет выглядеть так:

variable rc refcursor

declare
     blah number := 42;
begin
  open :rc for
     select *
     from x
     where x.value = blah;
end;
/

print x

Если вы переработаете свой PL/SQL в качестве хранимой функции, тогда он сможет вернуть значения. В этом случае вы можете создать тип коллекции, вывести все строки в переменную этого типа и вернуть его:

CREATE TYPE number_table AS TABLE OF NUMBER;

CREATE FUNCTION get_blah_from_x (blah  INTEGER)
  RETURN number_table
  IS
    values  number_table;
  BEGIN
    SELECT id
      BULK COLLECT INTO values
      FROM x
      WHERE x.value = blah;
    RETURN values;
  END;
/

Ответ 2

Хорошо, это сильно зависит от вашей библиотеки доступа к данным.

Вы можете вернуть любой SQL-совместимый тип в качестве параметра. Сюда входят сложные типы SQL и типы сбора. Но большинство библиотек просто не способны обрабатывать типы объектов Oracle.

В любом случае мои примеры будут использовать эти типы объектов:

create type SomeType as object(Field1 VarChar(50));

create type SomeTypeList as table of SomeType;

Когда ваша библиотека доступа может обрабатывать типы объектов, вы можете просто вернуть список объектов PL/SQL:

begin
  :list := SomeTypeList(SomeType('a'),SomeType('b'),SomeType('c'));
end;

Если нет, вы можете взломать его, заставив этот список выбрать и вернуть его результат как курсор:

declare
  list SomeTypeList;
begin
  list := SomeTypeList(SomeType('a'),SomeType('b'),SomeType('c'));
  open :yourCursor for
    SELECT A
    FROM   table(list);
end;