Oracle PL/SQL：将查询结果转储到文件中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2551210/
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
Oracle PL/SQL: Dump query result into file
提问by CC.
I'm working on a pl sql stored procedure. What I need is to do a select, use a cursor and for every record build a string using values. At the end I need to write this into a file. I try to use dbms_output.put_line("toto") but the buffer size is to small because I have about 14 millions lines. I call my procedure from a unix ksh. I'm thinking at something like using "spool on" (on the ksh side) to dump the result of my procedure, but I don' know how to do it (if this is possible)
我正在研究 pl sql 存储过程。我需要做的是选择，使用游标，并为每条记录使用值构建一个字符串。最后，我需要将其写入文件。我尝试使用 dbms_output.put_line("toto") 但缓冲区大小太小，因为我有大约 1400 万行。我从 unix ksh 调用我的程序。我正在考虑使用“spool on”（在 ksh 端）来转储我的程序结果，但我不知道该怎么做（如果可能的话）
Anyone has any idea?
任何人有任何想法？
回答by Peter Lang
Unless it is really necessary, I would not use a procedure.
除非真的有必要，否则我不会使用程序。
If you call the script using SQLPlus, just put the following into your test.sql(the SETs are from SQLPlus FAQto remove noise):
如果您使用 SQL Plustest.sqlSET调用脚本，只需将以下内容放入您的（这些s 来自SQLPlus FAQ以消除噪音）：
SET ECHO OFF
SET NEWPAGE 0
SET SPACE 0
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
SET TAB OFF
Select owner || ';' || object_name
From all_objects;
QUIT
and redirect output to a file (test.txt):
并将输出重定向到文件 ( test.txt)：
sqlplus user/passwd@instance @ test.sql > test.txt
If you really need to do stuff in PL/SQL, consider putting that into a function and call it per record:
如果你真的需要在 PL/SQL 中做一些事情，考虑把它放到一个函数中并按记录调用它：
Create Or Replace Function calculate_my_row( in_some_data In Varchar2 )
Return Varchar2
As
Begin
Return in_some_data || 'something-complicated';
End calculate_my_row;
Call:
称呼：
Select owner || ';' || calculate_my_row( object_name )
From all_objects;
Performance could suffer, but it should work. Make sure, that what you try can't be done in pure SQL, though.
性能可能会受到影响，但它应该可以工作。但是，请确保您尝试的操作不能在 pure 中完成SQL。
Reading your comment I think that Analytic Function Lagis what you need.
阅读您的评论，我认为 Analytic FunctionLag正是您所需要的。
This example appends *in case the value of valhas changed:
*如果 的值val已更改，则附加此示例：
With x As (
Select 1 id, 'A' val FROM dual
Union Select 2 id, 'A' val FROM dual
Union Select 3 id, 'B' val FROM dual
Union Select 4 id, 'B' val FROM dual
)
--# End of test-data
Select
id,
val,
Case When ( val <> prev_val Or prev_val Is Null ) Then '*' End As changed
From (
Select id, val, Lag( val ) Over ( Order By id ) As prev_val
From x
)
Order By id
Returns
退货
ID V C
---------- - -
1 A *
2 A
3 B *
4 B
回答by Erich Kitzmueller
If every line of your output is the result of an operation on one row in the table, then a stored function, combined with Peter Lang's answer, can do what you need.
如果输出的每一行都是对表中一行的操作的结果，那么存储函数与 Peter Lang 的答案相结合，可以满足您的需求。
create function create_string(p_foobar foobar%rowtype) return varchar2 as
begin
do_some_stuff(p_foobar);
return p_foobar.foo || ';' ||p_foobar.bar;
end;
/
If it is more complicated than that, maybe you can use a pipelined table function
如果比这更复杂，也许您可以使用流水线表函数
create type varchar_array
as table of varchar2(2000)
/
create function output_pipelined return varchar_array PIPELINED as
v_line varchar2(2000);
begin
for r_foobar in (select * from foobar)
loop
v_line := create_string(r_foobar);
pipe row(v_line);
end loop;
return;
end;
/
select * from TABLE(output_pipelined);
回答by Rob van Laarhoven
utl_file is your friend http://www.adp-gmbh.ch/ora/plsql/utl_file.htmlBut is writes the data to the filesystem on the server so you probably need your DBA's help for this.
utl_file 是您的朋友 http://www.adp-gmbh.ch/ora/plsql/utl_file.html但它会将数据写入服务器上的文件系统，因此您可能需要 DBA 的帮助。
