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

问题描述

如何获取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之类的

SELECT foo, bar FROM foobar;

提示:

BEGIN
SELECT foo, bar FROM foobar;
END;

不起作用.

推荐答案

这取决于您需要的结果.

It depends on what you need the result for.

如果您确定只有1行，请使用隐式光标:

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;

如果要选择多于1行，则可以使用显式游标:

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;

这篇关于是否可以从PL/SQL块输出SELECT语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！