将所有查询结果转储到plsql块内的文件中 [英] Dump all query results in file inside a plsql block
问题描述
我正面临以下问题.我想执行查询并将所有结果导出到文件中.我的查询另存为varchar2类型,我可以立即对其执行并给我所有结果.现在,这些结果如何将它们全部转储到文件中.我不想从查询或其他任何内容中选择特定的列名.这就像大量结果的批量收集并将其转储到文件中一样.这是我正在处理的一些伪代码:
I am facing the following issue. I want to execute a query and export all the results in a file. My query is saved as in varchar2 type and i can execute immediate on it and give me all the results. Now these results how can i dump them all in a file. I don't want to select specific column names from the query or anything. It is like a bulk collect of many results and dump them to a file. Here is some pseudocode of what i was working on:
这是一种行不通的方法
Declare
filehandle1 UTL_FILE.FILE_TYPE;
myquery varchar2(4000) := 'Select column1'||'~'||'Column2'||'~'||' from mytable';
Begin
filehandle1 := UTL_FILE.FOPEN ('MYDIR','mydata','W');
FOR emp IN (execute immediate (myquery)) LOOP --This part of course is not working
UTL_FILE.PUT (filehandle1, emp.*); --i dont want to write each column
END LOOP; --i have the columns and formatting ready of the query
UTL_FILE.FCLOSE (filehandle1);
End;
这又是另一种行不通的方式
This is another way again that will not work
Declare
filehandle1 UTL_FILE.FILE_TYPE;
myquery varchar2(4000) := 'Select column1'||'~'||'Column2'||'~'||' from mytable';
Begin
filehandle1 := UTL_FILE.FOPEN ('MYDIR','mydata','W');
UTL_FILE.PUT (filehandle1, myquery); --i want just to execute the query and write
UTL_FILE.FCLOSE (filehandle1); --everything in the file
End;
我怎么能得到所有东西并将它们扔到文件中?另外,我必须在plsql块中完成此操作,因此我无法将其假脱机处理
How can i just get everything and throw them in the file? Also i this has to be done in a plsql block therefore i cannot spool them Thanks in advance
推荐答案
您可以在游标上循环:
declare
filehandle1 utl_file.file_type;
myquery varchar2(4000) := 'select column1 ||''~''|| column2 from mytable';
mycursor sys_refcursor;
myresult varchar2(4000);
begin
filehandle1 := utl_file.fopen ('D42', 'mydata', 'w');
open mycursor for myquery;
loop
fetch mycursor into myresult;
exit when mycursor%notfound;
utl_file.put_line(filehandle1, myresult);
end loop;
close mycursor;
utl_file.fclose(filehandle1);
end;
/
或更简单地说,如果您不需要查询为字符串(例如,我不确定您是否实际上是从另一个表中获取它,否则我不确定为什么要使用它作为varchar2
变量):
Or more simply if you don't need the query to be a string (I'm not sure if you're actually getting it from another table, for example, otherwise I'm not sure why you'd have it as a varchar2
variable):
declare
filehandle1 utl_file.file_type;
begin
filehandle1 := utl_file.fopen ('D42', 'mydata', 'w');
for myrec in (select column1 ||'~'|| column2 as result from mytable)
loop
utl_file.put_line(filehandle1, myrec.result);
end loop;
utl_file.fclose(filehandle1);
end;
/
如果我将表创建并填充为:
If I create and populate the table as:
create table mytable (column1 number, column2 varchar2(10));
insert into mytable (column1, column2) values (1, 'First');
insert into mytable (column1, column2) values (2, 'Second');
然后使用其中任何一个,mydata
文件包含:
Then with either of those, the mydata
file contains:
1~First
2~Second
如果要显式使用批量收集,可以执行以下操作:
If you want to explicitly use bulk collect you can do this:
declare
filehandle1 utl_file.file_type;
myquery varchar2(4000) := 'select column1 ||''~''|| column2 from mytable';
mycursor sys_refcursor;
myresults sys.odcivarchar2list;
begin
filehandle1 := utl_file.fopen ('D42', 'mydata', 'w');
open mycursor for myquery;
loop
fetch mycursor bulk collect into myresults limit 100;
for idx in myresults.first..myresults.last loop
utl_file.put_line(filehandle1, myresults(idx));
end loop;
exit when mycursor%notfound;
end loop;
close mycursor;
utl_file.fclose(filehandle1);
end;
/
这篇关于将所有查询结果转储到plsql块内的文件中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!