概述

spool是oracle sqlplus提供的一个控制命令。可以利用spool和select语句的组合动态的生成一些sql脚本或者一些数据。

sqlplus中可以执行的语句分类大概有两种，一种是最常见的dml,dcl,ddl。这些语句在执行以后会存放在一个sqlplus的buffer中，而且每次只会存放最近执行的一条语句，以方便再次执行。另外的一种就是称为“命令语句”了，诸如set linesize之类的，这些语句往往是处理终端显示格式的一些命令，调用后语句不会存放入sqlplus buffer中。

spool语句就是命令语句的其中之一。在调用spool语句后，其后的select语句所产生的所有终端显示，包括select语句本身和一些行数信息都会写入spool语句定义的文件中。在语句执行完后，一定要调用spool off命令，不然输出的信息只是在数据缓存中，不会写入文件。

1.spool的作用是什么？

spool的作用可以用一句话来描述：在sqlplus中用来保存或打印查询结果。

2.spool在oracle 9i、10g和11g下的语法及使用示例

oracle 9i spool语法

SPO[OL] [file_name[.ext]|OFF|OUT]

oracle 10g、11g spool语法

SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]

oracle 9i spool的主法比较简单，其实就相当于oracle 10g中spool的replace选项，因此本文主要介绍oracle 10g的选项。

file_name[.txt]
保存查询结果集的的路径和文件名，如果没有指定后缀名，默认名一般为.lst或.lis。如果指定系统文件为/dev/null and /dev/stderr，则不会添加后缀名。
off
完成spool。
out
停止spool,并将文件输出到终端设备上，如打印机(可能有些操作系统不支持)。“OUT = OFF + PRINT”。
我们从语法上可以看到，oracle在10g、11g中对spool增加了create、replace、append选项，
create
创建指定文件名的新文件；如指定文件存在，则报文件存在错误。
replace
如果指定文件存在则覆盖替换；如指定文件不存在，则创建，replace为spool默认选项。
append
向指定文件名中追加内容；如指定文件不存在,则创建。

sqlplus spool的create、replace、append选项示例

spool spool.ora create
spool spool.ora repalce
spool spool.ora append

3.利用sqlplus spool将表导成csv文件

本sql将dba_sequence的所有数据导成csv文件。

利用spool动态生成删除某用户模式下面所有表的sql脚本：

例如：

set echo on --是否显示执行的命令内容 
set feedback off --是否显示 * rows selected 
set heading off --是否显示字段的名称
set verify off --是否显示替代变量被替代前后的语句。fil
set trimspool off --去字段空格
set pagesize 1000 --页面大小
set linesize 50//linesize设定尽量根据需要来设定，大了生成的文件也大
define fil= 'e:\exp.txt'
prompt *** Spooling to &fil
spool &fil
select id||','||username||','||'"'||password||'"' from myuser;
spool off;

a. 创建gen_drop_table.sql文件，包含如下语句：

set echo on --是否显示执行的命令内容
set feedback off --是否显示 * rows selected 
set newpage none
set pagesize 5000
set linesize 500
set verify off
set pagesize 0
set term off
set trims on
set linesize 600
set heading off
set timing off
set verify off
set numwidth 38
SPOOL c:\drop_table.sql
SELECT 'DROP TABLE '|| table_name ||';' FROM user_tables;
SPOOL OFF

b. 以SCOTT用户登录数据库

SQLPLUS > @ …..\gen_dorp_table.sql

c. 在c盘根目录下会生成文件drop_table.sql文件，包含删除所有表的语句，如下所示:

DROP TABLE DEPT;
DROP TABLE EMP;
DROP TABLE PARENT;
DROP TABLE STAT_VENDER_TEMP;
DROP TABLE TABLE_FORUM;

d. 在scott用户下运行dorp_table.sql文件，删除scott用户下所有的表。

SQLPLUS > @ c:\dorp_table.sql

为了防止spool打出的文件中包含sql语句和行数这些不便的信息，可以通过set来关闭这些信息在终端上的显示，然后再执行spool。

spool是oracle必须会的一个知识点，对oracle有兴趣的话一定要多熟悉这个命令，后面会分享更多关于DBA的一些内容，感兴趣的朋友可以关注下~

小编学的比较杂，所以每天更多是看兴趣发，大家如果有什么想要看的内容也可以在下方留言。