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

发布于 2024-07-10 03:57:51 字数 269 浏览 6 评论 0原文

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

例如，如何执行 SELECT 操作：

SELECT foo, bar FROM foobar;

提示：

BEGIN
SELECT foo, bar FROM foobar;
END;

不起作用。

原文

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?

For example how to do a SELECT like:

SELECT foo, bar FROM foobar;

Hint :

BEGIN
SELECT foo, bar FROM foobar;
END;

doesn't work.

分享到QQ

分享到微博

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

发布评论

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

私藏温柔 2024-07-17 03:57:51

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

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

我没有用于测试的 DBVisualizer，但这可能应该是您的起点。

有关更多详细信息，请参阅Oracle 12.1 新功能指南、Oracle Base 等。

对于早期版本，取决于您可以使用该工具来绑定变量，就像 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.

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;

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

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

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.

回复收藏 0 原文

南…巷孤猫 2024-07-17 03:57:51

这取决于您需要结果的目的。

如果您确定只有 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;

如果您想选择多于 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;

或使用其他类型的游标：

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;

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:

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:

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;

回复收藏 0 原文

深陷 2024-07-17 03:57:51

在包中创建一个函数并返回 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;

Create a function in a package and return a 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;

回复收藏 0 原文

云归处 2024-07-17 03:57:51

来自匿名块？我现在想更多地了解您认为需要的情况，因为使用子查询分解子句和内联视图，除了最复杂的情况之外，您很少需要诉诸 PL/SQL。

如果可以使用命名过程，则使用管道函数。这是从文档中提取的示例：

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

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.

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

回复收藏 0 原文

一身骄傲 2024-07-17 03:57:51

如果你想在 pl/sql 中查看 select 查询输出，你需要使用显式游标。它将保存活动数据集，并且通过一次获取每一行，只要它通过循环迭代从数据集中获取记录，它将显示活动数据集中的所有记录。此数据不会以表格格式生成，此结果将以纯文本格式生成。希望这会有所帮助。对于任何其他疑问，您可能会问......

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;

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....

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;

回复收藏 0 原文

花想c 2024-07-17 03:57:51

经典的“你好世界！” 块包含一个可执行部分，该部分调用DBMS_OUTPUT.PUT_LINE过程在屏幕上显示文本：

BEGIN
  DBMS_OUTPUT.put_line ('Hello World!');
END;

您可以在此处查看它：
http://www.oracle.com/ technetwork/issue-archive/2011/11-mar/o21plsql-242570.html

The classic “Hello World!” block contains an executable section that calls the DBMS_OUTPUT.PUT_LINE procedure to display text on the screen:

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

回复收藏 0 原文

怪我入戏太深 2024-07-17 03:57:51

对于低于 12c 的版本，简单的答案是否，至少不是 SQL Server 所采用的方式。
您可以打印结果，可以将结果插入表中，可以从函数/过程中将结果作为游标返回，或者从函数返回行集 -
但是您无法执行 SELECT 语句而不对结果执行某些操作。

SQL Server

begin
    select 1+1
    select 2+2
    select 3+3
end

/* 返回 3 个结果集 */

Oracle

SQL> begin
  2  select * 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

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 cannot execute SELECT statement, without doing something with the results.

SQL Server

begin
    select 1+1
    select 2+2
    select 3+3
end

/* 3 result sets returned */

Oracle

SQL> begin
  2  select * 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

回复收藏 0 原文

寂寞花火° 2024-07-17 03:57:51

您需要使用本机动态 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

You need to use Native dynamic SQL. Also, you do not need BEGIN-END to run SQL command:

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

回复收藏 0 原文

酒儿 2024-07-17 03:57:51

使用立即执行语句，

例如：

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;

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;

回复收藏 0 原文

怪我太投入 2024-07-17 03:57:51

即使问题很老，但我会分享完美回答问题的解决方案：

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;

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;

回复收藏 0 原文

转身泪倾城 2024-07-17 03:57:51

当您的选择查询返回多行时，将使用游标。因此，当您需要聚合或单个行数据时，您可以使用没有游标的过程/函数，而不是使用游标，

  Create Procedure sample(id 
    varchar2(20))as 
    Select count(*) into x from table 
    where 
       Userid=id;
     End ;

然后只需调用该过程

   Begin
   sample(20);
   End

这是过程/函数的实际使用，主要包装和存储复杂或复杂的查询需要使用相同的逻辑但不同的数据进行重复操作

Cursors are used when your select query returns multiple rows. So, rather using cursor in case when you want aggregates or single rowdata you could use a procedure/function without cursor as well like

  Create Procedure sample(id 
    varchar2(20))as 
    Select count(*) into x from table 
    where 
       Userid=id;
     End ;

And then simply call the procedure

   Begin
   sample(20);
   End

This is the actual use of procedure/function mostly wrapping and storing queries that are complex or that requires repeated manipulation with same logic but different data

回复收藏 0 原文

~没有更多了~