在 Oracle 11g 中使用 Spool
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21477797/
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
Using Spool in Oracle 11g
提问by user1918858
I want to use spool functionality in Oracle 11g.
我想在 Oracle 11g 中使用假脱机功能。
I want the entire output to be spooled into a file and a subset of the output to be spooled into a separatefile.
我希望将整个输出假脱机到一个文件中，并将输出的一个子集假脱机到一个单独的文件中。
In the below example I want temp_1.txtto contain data from A,B,C,D and E
在下面的示例中，我想temp_1.txt包含来自 A、B、C、D 和 E 的数据
In temp_2.txtI want data only for D.
在temp_2.txt我只想要 D 的数据。
sqlplus user/pass@inst
spool on temp_1.txt
select * from A;
select * from B;
select * from C;
spool on temp_2.txt
select * from D;
spool off temp_2.txt
select * from E;
exit;
Note:-Since this is very old legacy code I can't write a separate sqlplus session for D or re order the selects.
注意：-由于这是非常旧的遗留代码，我无法为 D 编写单独的 sqlplus 会话或重新排序选择。
回答by lit
How about doing it all in the sqlplus script. The host commands will need to change if you ever run on a different system (ie. Microsoft Windows). But, they would be required to change in a shell script as well.
如何在 sqlplus 脚本中完成所有操作。如果您曾在不同的系统（即 Microsoft Windows）上运行，则主机命令将需要更改。但是，它们也需要在 shell 脚本中进行更改。
spool all_queries.txt
select * from A;
select * from B;
select * from C;
spool off
spool only_d_query.txt
select * from D;
spool off
host cat only_d_query.txt >>all_queries.txt
spool all_queries.txt append
select * from E;
spool off
回答by Alex Poole
You can't do that. The SPOOLcommandonly allows one open file at a time; your second command, which would be spool temp_2.txt(without the on) would close the first file before opening and starting to write to the second one. And offdoesn't take any further parameters.
你不能那样做。该SPOOL命令一次只允许打开一个文件；您的第二个命令spool temp_2.txt（没有on）将在打开并开始写入第二个文件之前关闭第一个文件。并且off不接受任何进一步的参数。
Usage: SPOOL { <file> | OFF | OUT }
where <file> is file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]
One solution would be to spool the output of the statements to different files:
一种解决方案是将语句的输出假脱机到不同的文件：
spool temp_1.txt
select * from A;
select * from B;
select * from C;
spool temp_2.txt
select * from D;
spool temp_3.txt
select * from E;
spool off
... and then combine all three files into one from the operating system to get your 'master' output file, while still retaining the D-only file separately. e.g.:
...然后将操作系统中的所有三个文件合二为一以获得您的“主”输出文件，同时仍分别保留 D-only 文件。例如：
cat temp_2.txt >> temp_1.txt
cat temp_3.txt >> temp_1.txt
rm temp_3.txt`
Which leaves temp_1.txtand temp_2.txtwith the content you wanted, if I've understood you correctly. Different method if you're on Windows, of course.
这让temp_1.txt和temp_2.txt你想，如果我理解正确的话，你的内容。当然，如果您使用的是 Windows，则使用不同的方法。
Alternatively you could run the queries in a PL/SQL block and use UTL_FILEto write the results to one or both of two open files. But that's a bit more work, and would write the files on the server - so you'd need permissions on a DIRECTORYobject to write to, and access to the underlying filesystem directory that points to in order to retrieve the files.
或者，您可以在 PL/SQL 块中运行查询，UTL_FILE并将结果写入两个打开的文件中的一个或两个。但这需要更多的工作，并且会将文件写入服务器 - 因此您需要对DIRECTORY要写入的对象的权限，并访问指向的底层文件系统目录以检索文件。
