SQL 如何从 Oracle 中的声明/开始/结束块返回行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3371408/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to return rows from a declare/begin/end block in Oracle?
提问by Thomas Bratt
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.
我想从声明/开始/结束块中的选择语句返回行。我可以在 T-SQL 中做到这一点，但我想知道如何在 PL/SQL 中做到这一点。
The code looks a bit like the following:
代码看起来有点像下面这样：
declare
blah number := 42;
begin
select *
from x
where x.value = blah;
end;
采纳答案by Dave Costa
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.
一个匿名的 PL/SQL 块，就像你展示的那样，不能“返回”任何东西。然而，它可以通过绑定变量与调用者交互。
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:
因此，在这种情况下，我将使用的方法是声明一个游标引用，在 PL/SQL 块中为所需查询打开它，并让调用应用程序从中获取行。在 SQLPlus 中，这看起来像：
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:
如果您将 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;
/
回答by Robert Giesecke
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.
您可以将任何 SQL 兼容类型作为参数返回。这包括复杂的 SQL 类型和集合类型。但是大多数库根本无法处理 Oracle 的对象类型。
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:
当您的访问库可以处理对象类型时，您可以简单地返回一个 PL/SQL 对象列表：
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;
