是否可以从 PL/SQL 块中输出 SELECT 语句?

2021-12-05 00:00:00 sql oracle oracle10g plsql oracle-apex

如何让 PL/SQL 块以与执行普通 SELECT 相同的方式输出 SELECT 语句的结果?

How can I get a PL/SQL block to output the results of a SELECT statement the same way as if I had done a plain SELECT?

例如如何做一个 SELECT 像:

For example how to do a SELECT like:

SELECT foo, bar FROM foobar;

提示:

BEGIN
SELECT foo, bar FROM foobar;
END;

不起作用.

推荐答案

您可以在 Oracle 12.1 或更高版本中执行此操作:

You can do this in Oracle 12.1 or above:

declare
    rc sys_refcursor;
begin
    open rc for select * from dual;
    dbms_sql.return_result(rc);
end;

我没有要测试的 DBVisualizer,但这应该是您的起点.

I don't have DBVisualizer to test with, but that should probably be your starting point.

有关更多详细信息,请参阅Oracle 12.1 新特性中的隐式结果集指南、Oracle Base等

For more details, see Implicit Result Sets in the Oracle 12.1 New Features Guide, Oracle Base etc.

对于早期版本,根据工具的不同,您可能能够使用像 SQL*Plus 中的这个示例那样的引用游标绑定变量:

For earlier versions, depending on the tool you might be able to use ref cursor bind variables like this example from SQL*Plus:

set autoprint on

var rc refcursor

begin
    open :rc for select count(*) from dual;
end;
/

PL/SQL procedure successfully completed.


  COUNT(*)
----------
         1

1 row selected.

相关文章