问题描述
我想从声明/开始/结束块中的选择语句返回行.我可以在 T-SQL 中做到这一点,但我想知道如何在 PL/SQL 中做到这一点.
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;
推荐答案
一个匿名 PL/SQL 块,就像你展示的那样,不能返回"任何东西.然而,它可以通过绑定变量与调用者交互.
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 块中为所需查询打开它,并让调用应用程序从中获取行.在 SQLPlus 中,这看起来像:
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
如果您将 PL/SQL 重新转换为存储函数,那么它可能会返回值.在这种情况下,您可能想要做的是创建一个集合类型,将所有行提取到该类型的变量中,然后返回:
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;
/
这篇关于如何从 Oracle 中的声明/开始/结束块返回行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!