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
append
sqlplus spool的create、replace、append选项示例
spool spool.ora create
spool spool.ora repalce
spool spool.ora append3.利用sqlplusspool将表导成csv文件
本sql将dba_sequence的所有数据导成csv文件。
- SET
HEADING OFF - SET
ECHO OFF - SET
FEEDBACK OFF - SET
PAGES 0 - SET
LINESIZE 32766 - SET
LONG 1999999 - SET
TRIMOUT ON - SET
TRIMSPOOL ON - SET
NEWPAGE NONE - SET
SQLBLANKLINES OFF - SET
TRIMS ON - SET
TIMING OFF - SET
SERVEROUTPUT ON - SET
VERIFY OFF - COLUMN
SQL_TEXT FORMAT A32766 WORD WRAPPED - SPOOL
data_to_csv.sql -
- SELECT
'SELECT ''"''||' || -
REPLACE(wmsys.wm_concat(COLUMN_NAME), ',', '||''","''||'||chr(10)) || -
'||''"'' SQL_TEXT FROM DBA_SEQUENCES;' SQL_TEXT -
FROM DBA_TAB_COLUMNS -
WHERE TABLE_NAME = 'DBA_SEQUENCES' -
order by column_id; - spool
off -
- spool
data_to_csv.csv - @data_to_csv.sql
- 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","999999999999999999999999 999","1","N","Y","20","26432" "SYS","SYSTEM_GRANT","1","999999999999999999999999 999","1","N","Y","20","1046" "SYS","PROFNUM$","0","999999999999999999999999 999","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","999999999999999999999999 999","1","N","N","20","21" "SYS","CDC_SUBSCRIBE_SEQ$","1","999999999999999999999999 999","1","N","N","20","1" "SYS","CDC_RSID_SEQ$","1","999999999999999999999999 999","1","N","Y","10000","10001" "SYS","LOG$SEQUENCE","0","999999999999999999999999 999","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" .....