是否可以从 PL/SQL 块中输出 SELECT 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/351489/
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
Is it possible to output a SELECT statement from a PL/SQL block?
提问by GameFreak
How can I get a PL/SQL block to output the results of a SELECTstatement the same way as if I had done a plain SELECT?
我怎样才能得到一个 PL/SQL 块来输出一个SELECT语句的结果，就像我做了一个普通的一样SELECT？
For example how to do a SELECTlike:
例如如何做一个SELECT喜欢：
SELECT foo, bar FROM foobar;
Hint :
暗示 ：
BEGIN
SELECT foo, bar FROM foobar;
END;
doesn't work.
不起作用。
回答by Sergey Stadnik
It depends on what you need the result for.
这取决于您需要结果的用途。
If you are sure that there's going to be only 1 row, use implicit cursor:
如果您确定只有 1 行，请使用隐式游标：
DECLARE
v_foo foobar.foo%TYPE;
v_bar foobar.bar%TYPE;
BEGIN
SELECT foo,bar FROM foobar INTO v_foo, v_bar;
-- Print the foo and bar values
dbms_output.put_line('foo=' || v_foo || ', bar=' || v_bar);
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- No rows selected, insert your exception handler here
WHEN TOO_MANY_ROWS THEN
-- More than 1 row seleced, insert your exception handler here
END;
If you want to select more than 1 row, you can use either an explicit cursor:
如果要选择多于 1 行，可以使用显式游标：
DECLARE
CURSOR cur_foobar IS
SELECT foo, bar FROM foobar;
v_foo foobar.foo%TYPE;
v_bar foobar.bar%TYPE;
BEGIN
-- Open the cursor and loop through the records
OPEN cur_foobar;
LOOP
FETCH cur_foobar INTO v_foo, v_bar;
EXIT WHEN cur_foobar%NOTFOUND;
-- Print the foo and bar values
dbms_output.put_line('foo=' || v_foo || ', bar=' || v_bar);
END LOOP;
CLOSE cur_foobar;
END;
or use another type of cursor:
或使用另一种类型的游标：
BEGIN
-- Open the cursor and loop through the records
FOR v_rec IN (SELECT foo, bar FROM foobar) LOOP
-- Print the foo and bar values
dbms_output.put_line('foo=' || v_rec.foo || ', bar=' || v_rec.bar);
END LOOP;
END;
回答by William Robertson
You can do this in Oracle 12.1 or above:
您可以在 Oracle 12.1 或更高版本中执行此操作：
declare
rc sys_refcursor;
begin
open rc for select * from dual;
dbms_sql.return_result(rc);
end;
I don't have DBVisualizer to test with, but that should probably be your starting point.
我没有要测试的 DBVisualizer，但这应该是您的起点。
For more details, see Implicit Result Sets in the Oracle 12.1 New Features Guide, Oracle Baseetc.
有关更多详细信息，请参阅Oracle 12.1 新特性指南中的隐式结果集、Oracle Base等。
For earlier versions, depending on the tool you might be able to use ref cursor bind variables like this example from SQL*Plus:
对于早期版本，根据工具的不同，您可能能够使用 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.
回答by Igor Zelaya
Create a function in a package and return a SYS_REFCURSOR:
在包中创建一个函数并返回一个 SYS_REFCURSOR：
FUNCTION Function1 return SYS_REFCURSOR IS
l_cursor SYS_REFCURSOR;
BEGIN
open l_cursor for SELECT foo,bar FROM foobar;
return l_cursor;
END Function1;
回答by David Aldridge
From an anonymous block? I'd like to now more about the situation where you think that to be required, because with subquery factoring clauses and inline views it's pretty rare that you need to resort to PL/SQL for anything other than the most complex situations.
来自匿名区块？我现在想更多地了解您认为需要的情况，因为对于子查询分解子句和内联视图，除了最复杂的情况之外，您很少需要求助于 PL/SQL。
If you can use a named procedure then use pipelined functions. Here's an example pulled from the documentation:
如果您可以使用命名过程，则使用流水线函数。这是从文档中提取的示例：
CREATE PACKAGE pkg1 AS
TYPE numset_t IS TABLE OF NUMBER;
FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;
END pkg1;
/
CREATE PACKAGE BODY pkg1 AS
-- FUNCTION f1 returns a collection of elements (1,2,3,... x)
FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS
BEGIN
FOR i IN 1..x LOOP
PIPE ROW(i);
END LOOP;
RETURN;
END;
END pkg1;
/
-- pipelined function is used in FROM clause of SELECT statement
SELECT * FROM TABLE(pkg1.f1(5));
回答by Dinesh Katwal
The classic “Hello World!” block contains an executable section that calls the DBMS_OUTPUT.PUT_LINEprocedure to display text on the screen:
经典的“Hello World！” 块包含一个可执行部分，该部分调用DBMS_OUTPUT.PUT_LINE过程以在屏幕上显示文本：
BEGIN
DBMS_OUTPUT.put_line ('Hello World!');
END;
You can checkout it here: http://www.oracle.com/technetwork/issue-archive/2011/11-mar/o21plsql-242570.html
您可以在此处查看：http: //www.oracle.com/technetwork/issue-archive/2011/11-mar/o21plsql-242570.html
回答by Ahsan Habib
if you want see select query output in pl/sql you need to use a explicit cursor. Which will hold active data set and by fetching each row at a time it will show all the record from active data set as long as it fetches record from data set by iterating in loop. This data will not be generated in tabular format this result will be in plain text format. Hope this will be helpful. For any other query you may ask....
如果您想在 pl/sql 中查看选择查询输出，您需要使用显式游标。它将保存活动数据集，并通过一次获取每一行，只要它通过循环迭代从数据集中获取记录，它就会显示活动数据集中的所有记录。此数据不会以表格格式生成，此结果将以纯文本格式生成。希望这会有所帮助。对于任何其他查询，您可能会问......
set serveroutput on;
declare
cursor c1 is
select foo, bar from foobar;
begin
for i in c1 loop
dbms_output.put_line(i.foo || ' ' || i.bar);
end loop;
end;
回答by David ???? Markovitz
For versions below 12c, the plain answer is NO, at least not in the manner it is being done is SQL Server.
You can print the results, you can insert the results into tables, you can return the results as cursors from within function/procedure or return a row set from function -
but you cannotexecute SELECT statement, without doing something with the results.
对于 12c 以下的版本，简单的答案是NO，至少不是 SQL Server 的方式。
您可以打印结果，您可以将结果插入表中，您可以从函数/过程中将结果作为游标返回或从函数返回一个行集 -
但是您不能执行 SELECT 语句，而不对结果做任何事情。
SQL Server
数据库服务器
begin
select 1+1
select 2+2
select 3+3
end
/* 3 result sets returned */
/* 返回 3 个结果集 */
Oracle
甲骨文
SQL> begin
2 select 1+1 from dual;
3 end;
4 /
select * from dual;
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement
回答by Art
You need to use Native dynamic SQL. Also, you do not need BEGIN-END to run SQL command:
您需要使用 Native 动态 SQL。此外，您不需要 BEGIN-END 来运行 SQL 命令：
declare
l_tabname VARCHAR2(100) := 'dual';
l_val1 VARCHAR2(100):= '''foo''';
l_val2 VARCHAR2(100):= '''bar''';
l_sql VARCHAR2(1000);
begin
l_sql:= 'SELECT '||l_val1||','||l_val2||' FROM '||l_tabname;
execute immediate l_sql;
dbms_output.put_line(l_sql);
end;
/
Output:
SELECT 'foo','bar' FROM dual
回答by Vamsi Praveen Karanam
use execute immediate statement
使用立即执行语句
like:
喜欢：
declare
var1 integer;
var2 varchar2(200)
begin
execute immediate 'select emp_id,emp_name from emp'
into var1,var2;
dbms_output.put_line(var1 || var2);
end;
回答by Issam El omri
Even if the question is old but i will share the solution that answers perfectly the question :
即使问题很老，但我会分享完美回答问题的解决方案：
SET SERVEROUTPUT ON;
DECLARE
RC SYS_REFCURSOR;
Result1 varchar2(25);
Result2 varchar2(25);
BEGIN
OPEN RC FOR SELECT foo, bar into Result1, Result2 FROM foobar;
DBMS_SQL.RETURN_RESULT(RC);
END;
