如何从Oracle的declare/begin/end块中返回行？

Question

我想在declare/begin/end代码块中从select语句返回行。我可以在T-SQL中实现这一点，但我想知道如何在PL/SQL中实现。

代码看起来有点像以下内容：

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

- Thomas Bratt

---

无法从匿名PLSQL块返回数据。 - Michael Pakhantsov

是的，你可以。例如在 node oracledb 中，你可以使用 oracledb.BIND_OUT，请查看 https://github.com/oracle/node-oracledb/blob/master/doc/api.md。 - Toolkit

Answer 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;
/

- Dave Costa

---

谢谢 - 回答得很好！但是在这种情况下使用游标会有什么性能影响呢？我总是尽量避免在T-SQL中使用它们，因为它们的性能不佳。 - Thomas Bratt

游标不过是对结果集的引用。由于涉及到获取每条记录之间的往返，因此会有性能损失。然而，你针对Oracle调用的每个select语句都将以游标的形式返回给你。在.NET中，它们被称为DataReaders。如果您对它们没有任何性能问题，那么使用游标也不会有性能问题。 - Robert Giesecke

Answer 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;

- Robert Giesecke