spool 用法小结

先看一个帖子：http://www.itpub.net/thread-317699-1-1.html

使用SQLPLUS的SPOOL功能我们可以获得非常强大和丰富的报表输出。SQLPLUS可以设置极为复杂的格式信息，但是很多时候会有一些信息是你不想要的。

在生产中常会遇到需要将数量比较大的表值导入到本地文本文件中. 方法有很多种,比较常用的就是spool命令，本文将为大家介绍这个命令的实现，以及两种实现方法的对比。

要输出符合要求格式的数据文件只需在select时用字符连接来规范格式。比如有如下表

要输出符合1,John,1234,这样的数据格式就用select id||','||username||','||password||',' from myuser这样的语句。

写个下面这样的脚本就行可以输出符合要求格式的数据至文件中，不会含有其它不需要东西，只有数据部分。

－－脚本文件名为expmyusr.sql，存放在e盘下。

 存数据的文件名为e:\exp.txt

－－执行过程

SQL>; @e:\expmyusr.sql

*** Spooling to e:\exp.txt

1,John,"1234"

2,Jack,"12345"

3,Rose,"2345"

4,Joe,"384657"

5,Tom,"384655"

6,Jordan,"384455"

检查可知结果符合要求。

我自己的测试结果如下：

首先创建exptest.sql脚本在e盘下：其内容为：

set echo on

set feedback on

set heading on

set verify off

set trimspool off

set pagesize 1000

set linesize 50

define fil= 'e:\exp.txt'

prompt *** Spooling to &fil

spool &fil

select object_id||','||object_name||','||'"'||owner||'"' from t;

spool off; 

执行上述脚本：

SQL> @e:\exptest.sql

SQL> set echo on 

SQL> set feedback on

SQL> set heading on

SQL> set verify off

SQL> set trimspool off

SQL> set pagesize 1000

SQL> set linesize 50

SQL> define fil= 'e:\exp.txt'

SQL> prompt *** Spooling to &fil

SQL> spool &fil

SQL> select object_id||','||object_name||','||'"'||owner||'"' from t;

OBJECT_ID||','||OBJECT_NAME||','||'"'||OWNER||'"'

--------------------------------------------------

20,ICOL$,"SYS"

44,I_USER1,"SYS"

28,CON$,"SYS"

15,UNDO$,"SYS"

29,C_COBJ#,"SYS"

3,I_OBJ#,"SYS"

25,PROXY_ROLE_DATA$,"SYS"

39,I_IND1,"SYS"

51,I_CDEF2,"SYS"

26,I_PROXY_ROLE_DATA$_1,"SYS"

17,FILE$,"SYS"

13,UET$,"SYS"

9,I_FILE#_BLOCK#,"SYS"

41,I_FILE1,"SYS"

48,I_CON1,"SYS"

38,I_OBJ3,"SYS"

7,I_TS#,"SYS"

53,I_CDEF4,"SYS"

19,IND$,"SYS"

14,SEG$,"SYS"

已选择20行。

SQL> spool off;

—— 脚本执行结束

Oracle SPOOL的两种方法之对比 

通常情况下，我们使用SPOOL方法，将数据库中的表导出为文本文件的时候会采用两种方法，如下述：

方法一：采用以下格式脚本　

方法二：采用以下脚本

比较以上方法，即方法一采用设定分隔符然后由sqlplus自己使用设定的分隔符对字段进行分割，方法二将分隔符拼接在SELECT语句中，即手工控制输出格式。

在实践中，我发现通过方法一导出来的数据具有很大的不确定性，这种方法导出来的数据再由sql ldr导入的时候出错的可能性在95%以上，尤其对大批量的数据表，如100万条记录的表更是如此，而且导出的数据文件狂大。

而方法二导出的数据文件格式很规整，数据文件的大小可能是方法一的1/4左右。经这种方法导出来的数据文件再由sqlldr导入时，出错的可能性很小，基本都可以导入成功。

因此，实践中我建议大家使用方法二手工去控制spool文件的格式，这样可以减小出错的可能性，避免走很多弯路。 

一些关于spool的介绍参看：http://space.itpub.net/action-tag-tagname-spool