spool 2

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的主法比较简单，其实就相当于oracle10g中spool的replace选项，因此本文主要介绍oracle 10g的选项。

file_name[.txt]
保存查询结果集的的路径和文件名，如果没有指定后缀名，默认名一般为.lst或.lis。如果指定系统文件为/dev/null and/dev/stderr，则不会添加后缀名。

off
完成spool。

out
停止spool,并将文件输出到终端设备上，如打印机(可能有些操作系统不支持)。

我们从语法上可以看到，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.利用sqlplusspool将表导成csv文件

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

viewplain copy toclipboard print ?

1. SET HEADING OFF  
2. SET ECHO OFF  
3. SET FEEDBACK OFF  
4. SET PAGES 0  
5. SET LINESIZE 32766  
6. SET LONG 1999999  
7. SET TRIMOUT ON  
8. SET TRIMSPOOL ON  
9. SET NEWPAGE NONE  
10. SET SQLBLANKLINES OFF  
11. SET TRIMS ON  
12. SET TIMING OFF  
13. SET SERVEROUTPUT ON  
14. SET VERIFY OFF  
15. COLUMN SQL_TEXT FORMAT A32766 WORD WRAPPED  
16. SPOOL data_to_csv.sql  
17.   
18. SELECT 'SELECT ''"''||' ||  
19.        REPLACE(wmsys.wm_concat(COLUMN_NAME), ',', '||''","''||'||chr(10)) ||  
20.        '||''"'' SQL_TEXT FROM DBA_SEQUENCES;' SQL_TEXT  
21.   FROM DBA_TAB_COLUMNS  
22.  WHERE TABLE_NAME = 'DBA_SEQUENCES'  
23.  order by column_id;  
24. spool off  
25.   
26. spool data_to_csv.csv  
27. @data_to_csv.sql  
28. spool off   

运行如下：

17:10:26 dw@dw>@A.SQL
SELECT '"'||SEQUENCE_OWNER||'","'||
SEQUENCE_NAME||'","'||
MIN_VALUE||'","'||
MAX_VALUE||'","'||
INCREMENT_BY||'","'||
CYCLE_FLAG||'","'||
ORDER_FLAG||'","'||
CACHE_SIZE||'","'||
LAST_NUMBER||'"' SQL_TEXT FROM DBA_SEQUENCES;
















"SYS","UGROUP_SEQUENCE","0","999999999999999999999999999","1","N","Y","10","1"
"SYS","OBJECT_GRANT","1","999999999999999999999999999","1","N","Y","20","26432"
"SYS","SYSTEM_GRANT","1","999999999999999999999999999","1","N","Y","20","1046"
"SYS","PROFNUM$","0","999999999999999999999999999","1","N","N","0","2"
"SYS","AUDSES$","1","2000000000","1","Y","N","10000","581721"
"SYS","SNAPSHOT_ID$","1","2147483647","1","N","N","20","4"
"SYS","SNAPSITE_ID$","1","4294967295","1","N","N","20","1"
"SYS","JOBSEQ","1","999999999","1","Y","N","20","21"
"SYS","RGROUPSEQ","1","999999999","1","Y","N","20","1"
"SYS","ORA_TQ_BASE$","1","4294967","1","Y","N","0","83"
"SYS","PARTITION_NAME$","1","999999999999999999999999999","1","N","N","20","21"
"SYS","CDC_SUBSCRIBE_SEQ$","1","999999999999999999999999999","1","N","N","20","1"
"SYS","CDC_RSID_SEQ$","1","999999999999999999999999999","1","N","Y","10000","10001"
"SYS","LOG$SEQUENCE","0","999999999999999999999999999","1","N","Y","10","1"
"SYS","PSINDEX_SEQ$","1","18446744073709551615","1","N","N","1000","2100"
"SYS","AWSEQ$","1","4294967295","1","N","N","0","1000"
"SYS","STREAMS$_CAPTURE_INST","1","4294967295","1","Y","N","0","1"
.....