SQL 如何在pl sql中使用匿名块打印出整个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2348319/
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
how to print out the whole table using anonymous block in pl sql?
提问by n0ob
I want to use DBMS_OUTPUT.PUT_LINE, but the number of rows exceeds just 1. There is nothing in the table which increments sequentially, so I can't use a loop. Is there some way to print out each row in the table?
我想使用DBMS_OUTPUT.PUT_LINE，但行数仅超过 1。表中没有任何按顺序递增的内容，因此我无法使用循环。有没有办法打印出表格中的每一行？
回答by Jonathan
try with something like this.
尝试这样的事情。
SET SERVEROUTPUT ON
BEGIN
-- A PL/SQL cursor
FOR cursor1 IN (SELECT * FROM table1)
LOOP
DBMS_OUTPUT.PUT_LINE('Column 1 = ' || cursor1.column1 ||
', Column 2 = ' || cursor1.column2);
END LOOP;
END;
/
回答by APC
The quick and dirtiest way of doing this is actually through SQL*Plus:
这样做的最快和最肮脏的方法实际上是通过 SQL*Plus：
SQL> set lines 200
SQL> set heading off
SQL> set feedback off
SQL> spool $HOME/your_table.out
SQL> select * from your_table;
SQL> spool off
SQL*Plus has some neat if basic reporting functionality; we can even generate HTML files.
SQL*Plus 有一些简洁的基本报告功能；我们甚至可以生成 HTML 文件。
If you have a very long table (many rows) or a wide one (many columns) you may be better off outputting directly to a file, like this.
如果你有一个很长的表（很多行）或一个很宽的表（很多列），你最好直接输出到一个文件，像这样。
declare
fh utl_file.file_type;
begin
fh := utl_file.fopen('TARGET_DIRECTORY', 'your_table.lst', 'W');
for lrec in ( select * from your_table )
loop
utl_file.put( fh, id );
utl_file.put( fh, '::' );
utl_file.put( fh, col_1 );
utl_file.put( fh, '::' );
utl_file.put( fh, col_2 );
utl_file.put( fh, '::' );
utl_file.put( fh, to_char ( col_3, 'dd-mm-yyyy hh24:mi:ss' ) );
utl_file.new_line(fh);
end loop;
utl_file.fclose(fh);
end;
/
This may look like a chore, but the PUT() calls can be generated from USER_TAB_COLUMNS. There are a couple of gotchas with UTL_FILE so read the documentation.
这可能看起来很麻烦，但可以从 USER_TAB_COLUMNS 生成 PUT() 调用。UTL_FILE 有几个问题，所以请阅读文档。
You coulduse the same control structure with DBMS_OUTPUT....
您可以对 DBMS_OUTPUT 使用相同的控制结构....
begin
for lrec in ( select * from your_table )
loop
dbms_output.put( id );
dbms_output.put( '::' );
dbms_output.put( col_1 );
dbms_output.put( '::' );
dbms_output.put( col_2 );
dbms_output.put( '::' );
dbms_output.put( to_char ( col_3, 'dd-mm-yyyy hh24:mi:ss' ) );
dbms_output.new_line;
end loop;
end;
/
... but if you are going to spool out from a SQL*Plus, why not use the easier option?
...但如果您打算从 SQL*Plus 中脱机，为什么不使用更简单的选项呢？
回答by Md Sultan
This may help:
这可能有帮助：
BEGIN
FOR MY_CURSOR IN (SELECT COLUMN1,COLUMN2,COLUMN3 FROM MY_TABLE)
LOOP
DBMS_OUTPUT.PUT_LINE('COLUMN1 = ' || MY_CURSOR.COLUMN1 ||',
COLUMN2 = ' || MY_CURSOR.COLUMN2 ||',
COLUMN3 = ' || MY_CURSOR.COLUMN3);
END LOOP;
END;
