oracle 在匿名块内显示选择结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6455566/
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
display select results inside anonymous block
提问by Pascal
I'm trying to debug a SELECT inside a procedure, and I'm trying to this using a anonymous block. I would like that SQL Developer simply return the last SELECT statement, but I get the error:
我正在尝试在过程中调试 SELECT，并且我正在尝试使用匿名块。我希望 SQL Developer 只返回最后一个 SELECT 语句，但出现错误：
ORA-06550: line 21, column 5:
PLS-00428: an INTO clause is expected in this SELECT statement
Inside the procedure, I have an INTO for that select, but is there a simple way that I can simply get the results for the last SELECT statement for my debugging? I'm using anonymous block and variables so that the code is as similar as possible from what's actually inside the procedure, so that I don't have to change the code
在程序内部，我有一个 INTO 用于该选择，但是有没有一种简单的方法可以简单地获取最后一个 SELECT 语句的结果以进行调试？我使用匿名块和变量，以便代码尽可能与过程内部的实际内容相似，这样我就不必更改代码
set serveroutput on format wrapped;
DECLARE
p_cd_doc_type number;
p_dc_doc_code varchar2(200);
p_dt_base date;
p_qt_days number;
p_vl_mov_total number;
p_qt_transac number;
v_dt_max date;
v_dt_min date;
begin
p_dt_base := sysdate;
p_qt_days := 1;
v_dt_max := trunc(p_dt_base) + 1;
v_dt_min := v_dt_max - p_qt_days;
p_vl_mov_total := 0;
DBMS_OUTPUT.PUT_LINE('v_dt_max = ' || v_dt_max);
DBMS_OUTPUT.PUT_LINE('v_dt_min = ' || v_dt_min);
select *
from tb_cad_cliente a join tb_trn_transacao b
on a.cd_cliente = b.cd_cliente
where a.cd_doc_type = p_cd_doc_type
and a.dc_doc_code = p_dc_doc_code
and b.dt_row between v_dt_min and v_dt_max
and b.cd_status = 3;
end;
回答by Aitor
You can try with this, to print your result easily:
你可以试试这个，轻松打印你的结果：
declare
your_variable varchar2(19);
BEGIN
DBMS_OUTPUT.PUT_LINE('init..');
FOR x IN (SELECT your_column
FROM you_table
where rownum<2
order by 1)
LOOP
DBMS_OUTPUT.PUT_LINE(x.your_column);
END LOOP;
END;
回答by Cos Callis
In order to return the value of the select it needs to be selected intoa container (a reference cursor or REF CURSOR). In your Declare you should include ref_cursor_out SYS_REFCURSOR;and change your select to:
为了返回 select 的值，它需要被选择到一个容器中（参考光标或 REF CURSOR）。在您的声明中，您应该包括ref_cursor_out SYS_REFCURSOR;并将您的选择更改为：
select * into ref_cursor_out ...
In SQL Developer there is an option (I am a Toad user, so I forget where in SD) that tells the IDE to load the result set into a grid to view.
在 SQL Developer 中有一个选项（我是 Toad 用户，所以我忘记了在 SD 中的哪个位置）告诉 IDE 将结果集加载到网格中进行查看。
[edit: per comment from @DCookie, Thanks for the catch!]
[编辑：来自@DCookie 的每条评论，感谢您的关注！]
回答by zep
Error
错误
For what I know, whatever tool you use to debug, the pl/sql blocks(anonymous and named) should be validfor the PL/SQL compiler. The fact is that your block doesn't result valid for the PL/SQL compiler, and your erroris there to tell you, and is coming out from the PL/SQL compilerand notfrom the Sql Developer!
据我所知，无论您使用什么工具进行调试，pl/sql 块（匿名和命名）都应该对PL/SQL 编译器有效。事实是，你的块不会导致有效的PL / SQL编译器，和你的错误有没有告诉你，是从走出PL / SQL编译器和不从SQL开发！
PLS-00428: an INTO clause is expected in this SELECT statement Cause: The INTO clause of a SELECT INTO statement was omitted. For example, the code might look like SELECT deptno, dname, loc FROM dept WHERE ... instead of SELECT deptno, dname, loc INTO dept_rec FROM dept WHERE ... In PL/SQL, only a subquery is written without an INTO clause. Action: Add the required INTO clause
PLS-00428：此 SELECT 语句中需要 INTO 子句原因：省略了 SELECT INTO 语句的 INTO 子句。例如，代码可能看起来像 SELECT deptno, dname, loc FROM dept WHERE ... 而不是 SELECT deptno, dname, loc INTO dept_rec FROM dept WHERE ... 在 PL/SQL 中，只编写一个没有 INTO 子句的子查询. 行动：添加所需的INTO条款
and
和
ORA-06550: line string, column string: string Cause: Usually a PL/SQL compilation error. Action: None
ORA-06550：行字符串，列字符串：字符串 原因：通常是 PL/SQL 编译错误。行动：无
Why error
为什么会出错
When an Pl/sql error appear, you only have the choice to investigate in the codeand in the manuals:Resolution of names in static SQL statements
当出现 Pl/sql 错误时，您只能选择在代码和手册中进行调查：Resolution of names in static SQL statements
PS:The route is always the same:
PS：路线总是一样的：
回答by ravkry
I recently changed from MSSQL to PLSQL and I miss the return values as tables from procedures for analytical purposes. I wrote simply dynamic query that return table by two steps. Maybe someone use it:
我最近从 MSSQL 更改为 PLSQL，并且出于分析目的，我错过了作为表的过程的返回值。我写了简单的动态查询，分两步返回表。也许有人使用它：
/*
rkry20150929: Return table from anonymous block
*/
declare
v_stmt varchar2(1000);
c int;
BEGIN
select count(*) into c from user_tables where table_name = upper('tmp_result');
if c>0
THEN
v_stmt := 'truncate table tmp_result';
execute immediate v_stmt;
v_stmt := 'drop table tmp_result';
execute immediate v_stmt;
end if;
v_stmt :='CREATE GLOBAL TEMPORARY TABLE tmp_result on commit preserve rows AS ';
v_stmt:= v_stmt ||
/*-----THERE FILL SQL COMMAND-----------*/'
SELECT ''Result select to table in anonymous block '' MyColumn FROM DUAL
';/*-----THERE FILL SQL COMMAND-----------*/
execute immediate v_stmt;
End;
/*FIRST EXECUTE TO HERE */
SELECT * FROM tmp_result;
