如何从 Oracle 中的声明/开始/结束块返回行？

发布于 2024-09-12 07:19:25 字数 220 浏览 3 评论 0原文

我想从声明/开始/结束块中的 select 语句返回行。我可以在 T-SQL 中执行此操作，但我想知道如何在 PL/SQL 中执行此操作。

代码看起来有点像下面这样：

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

原文

I want to return rows from a select statement within a declare/begin/end block. I can do this in T-SQL but I would like to know how to do it in PL/SQL.

The code looks a bit like the following:

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

夏夜暖风 2024-09-19 07:19:25

匿名 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;
/

An anonymous PL/SQL block, like the one you've shown, can't "return" anything. It can interact with the caller by means of bind variables, however.

So the method I would use in this case would be to declare a cursor reference, open it in the PL/SQL block for the desired query, and let the calling application fetch rows from it. In SQLPlus this would look like:

variable rc refcursor

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

print x

If you recast your PL/SQL as a stored function then it could return values. In this case what you might want to do is create a collection type, fetch all the rows into a variable of that type, and return it:

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

回复收藏 0 原文

夜巴黎 2024-09-19 07:19:25

嗯，这在很大程度上取决于您的数据访问库。

您可以返回任何与 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;

如果不能，您可以通过强制将此列表强制为 select 并返回其值来绕过它结果作为光标：

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

Well, this depends heavily on your data access library.

You can return any SQL-compatible type as a parameter. This includes complex SQL types and collection types.
But most libraries are simply not capable of handling Oracle's object types.

Either way, my examples will use these object types:

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

create type SomeTypeList as table of SomeType;

When your access library can handle object types, you could simply return a list of PL/SQL objects:

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

If not, you could hack around it by forcing this list into a select and return its result as a cursor:

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

回复收藏 0 原文

~没有更多了~