Oracle Spool經典教程
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,並將檔案輸出到終端裝置上,如印表機(可能有些作業系統不支援)。
我們從語法上可以看到,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
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,並將檔案輸出到終端裝置上,如印表機(可能有些作業系統不支援)。
我們從語法上可以看到,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.引數指南
對於SPOOL資料的SQL,最好要自己定義格式,以方便程式直接匯入,SQL語句如:
select taskindex||'|'||commonindex||'|'||tasktype||'|'||to_number(to_char(sysdate,'YYYYMMDD')) from ssrv_sendsms_task;
spool常用的設定
set colsep' '; //域輸出分隔符
set echo off; //顯示start啟動的指令碼中的每個sql命令,預設為on
set feedback off; //回顯本次sql命令處理的記錄條數,預設為on
set heading off; //輸出域標題,預設為on
set pagesize 0; //輸出每頁行數,預設為24,為了避免分頁,可設定為0。
set termout off; //顯示指令碼中的命令的執行結果,預設為on
set trimout on; //去除標準輸出每行的拖尾空格,預設為off
set trimspool on; //去除重定向(spool)輸出每行的拖尾空格,預設為off
匯出文字資料的建議格式:
SQL*PLUS環境設定SET NEWPAGE NONE
SET HEADING OFF
SET SPACE 0
SET PAGESIZE 0
SET TRIMOUT ON
SET TRIMSPOOL ON
SET LINESIZE 2500
注:LINESIZE要稍微設定大些,免得資料被截斷,它應和相應的TRIMSPOOL結合使用防止匯出的文字有太多的尾部空格。但是如果LINESIZE設定太大,會大大降低匯出的速度,另外在WINDOWS下匯出最好不要用PLSQL匯出,速度比較慢,直接用COMMEND下的SQLPLUS命令最小化視窗執行。
對於欄位內包含很多回車換行符的應該給與過濾,形成比較規矩的文字檔案。通常情況下,我們使用SPOOL方法,將資料庫中的表匯出為文字檔案的時候會採用兩種方法,如下述:
方法一:採用以下格式指令碼
set colsep '|' --設定|為列分隔符
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
set num 18
set feedback off
spool 路徑+檔名
select * from tablename;
spool off
方法二:採用以下指令碼
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool 路徑+檔名
select col1||','||col2||','||col3||','||col4||'..' from tablename;
spool off
比較以上方法,即方法一採用設定分隔符然後由sqlplus自己使用設定的分隔符對欄位進行分割,方法二將分隔符拼接在SELECT語句中,即手工控制輸出格式。
在實踐中,發現透過方法一匯出來的資料具有很大的不確定性,這種方法匯出來的資料再由sqlldr匯入的時候出錯的可能性在95%以上,尤其對大批次的資料表,如100萬條記錄的表更是如此,而且匯出的資料檔案狂大。
而方法二匯出的資料檔案格式很規整,資料檔案的大小可能是方法一的1/4左右。經這種方法匯出來的資料檔案再由sqlldr匯入時,出錯的可能性很小,基本都可以匯入成功。
因此,實踐中我建議大家使用方法二手工去控制spool檔案的格式,這樣可以減小出錯的可能性,避免走很多彎路。
自測例:將ssrv_sendsms_task表中的資料匯出到文字(資料庫Oracle 9i 作業系統 SUSE LINUX Enterprise Server 9)
spool_test.sh指令碼如下:
#!/bin/sh
DB_USER=zxdbm_ismp #DB USER
DB_PWD=zxin_smap #DB PASSWORD
DB_SERV=zx10_40_43_133 #DB SERVICE NAME
sqlplus -s $DB_USER/$DB_PWD@$DB_SERV<
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool promt.txt
select taskindex||'|'||commonindex||'|'||tasktype||'|'||to_number(to_char(sysdate,'YYYYMMDD')) from ssrv_sendsms_task;
spool off
EOF
執行./spool_test.sh後生成sp_test.txt,內容如下:
83|115|1|20080307
85|115|11|20080307
86|115|10|20080307
84|115|2|20080307
6|5|14|20080307
7|5|12|20080307
9|5|15|20080307
注:上面自測例中,spool promt.txt中的目標生成檔案promt.txt,在HP-UNX環境下的shell指令碼中呼叫Oracle的spool函式,如果將上述邏輯程式碼封裝為一個function,然後來呼叫這個function的話,則在shell指令碼中最終是不會生成promt.txt檔案的。只能直接執行邏輯程式碼,封裝後則spool函式失效。
對於promt.txt在相對路徑下,下面2中方法在shell環境中執行時,兩者只能擇一,兩者並存則spool函式會失效。假設promt.txt檔案生成的路徑為:/home/zxin10/zhuo/batchoperate/spoolfile
方式[1]
echo "start spool in shell.."
sqlplus -s zxdbm_ismp/zxin_smap<
set pagesize 0
set echo off feed off term off heading off trims off
set colsep '|'
set trimspool on
set linesize 10000
set trimspool on
set linesize 120
set newpage 1
spool /home/zxin10/zhuo/batchoperate/spoolfile/promt.txt
select batchindex||'|'||productid||'|'||contentid||'|'||optype||'|'||uploadfile from zxdbm_700.s700_batch_operation where status=1;
spool off
EOF
echo "end.."
方式[2]
echo "start spool in shell.."
cd /home/zxin10/zhuo/batchoperate/spoolfile
sqlplus -s zxdbm_ismp/zxin_smap<
set pagesize 0
set echo off feed off term off heading off trims off
set colsep '|'
set trimspool on
set linesize 10000
set trimspool on
set linesize 120
set newpage 1
spool promt.txt
select batchindex||'|'||productid||'|'||contentid||'|'||optype||'|'||uploadfile from zxdbm_700.s700_batch_operation where status=1;
spool off
EOF
echo "end.."
對於SPOOL資料的SQL,最好要自己定義格式,以方便程式直接匯入,SQL語句如:
select taskindex||'|'||commonindex||'|'||tasktype||'|'||to_number(to_char(sysdate,'YYYYMMDD')) from ssrv_sendsms_task;
spool常用的設定
set colsep' '; //域輸出分隔符
set echo off; //顯示start啟動的指令碼中的每個sql命令,預設為on
set feedback off; //回顯本次sql命令處理的記錄條數,預設為on
set heading off; //輸出域標題,預設為on
set pagesize 0; //輸出每頁行數,預設為24,為了避免分頁,可設定為0。
set termout off; //顯示指令碼中的命令的執行結果,預設為on
set trimout on; //去除標準輸出每行的拖尾空格,預設為off
set trimspool on; //去除重定向(spool)輸出每行的拖尾空格,預設為off
匯出文字資料的建議格式:
SQL*PLUS環境設定SET NEWPAGE NONE
SET HEADING OFF
SET SPACE 0
SET PAGESIZE 0
SET TRIMOUT ON
SET TRIMSPOOL ON
SET LINESIZE 2500
注:LINESIZE要稍微設定大些,免得資料被截斷,它應和相應的TRIMSPOOL結合使用防止匯出的文字有太多的尾部空格。但是如果LINESIZE設定太大,會大大降低匯出的速度,另外在WINDOWS下匯出最好不要用PLSQL匯出,速度比較慢,直接用COMMEND下的SQLPLUS命令最小化視窗執行。
對於欄位內包含很多回車換行符的應該給與過濾,形成比較規矩的文字檔案。通常情況下,我們使用SPOOL方法,將資料庫中的表匯出為文字檔案的時候會採用兩種方法,如下述:
方法一:採用以下格式指令碼
set colsep '|' --設定|為列分隔符
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
set num 18
set feedback off
spool 路徑+檔名
select * from tablename;
spool off
方法二:採用以下指令碼
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool 路徑+檔名
select col1||','||col2||','||col3||','||col4||'..' from tablename;
spool off
比較以上方法,即方法一採用設定分隔符然後由sqlplus自己使用設定的分隔符對欄位進行分割,方法二將分隔符拼接在SELECT語句中,即手工控制輸出格式。
在實踐中,發現透過方法一匯出來的資料具有很大的不確定性,這種方法匯出來的資料再由sqlldr匯入的時候出錯的可能性在95%以上,尤其對大批次的資料表,如100萬條記錄的表更是如此,而且匯出的資料檔案狂大。
而方法二匯出的資料檔案格式很規整,資料檔案的大小可能是方法一的1/4左右。經這種方法匯出來的資料檔案再由sqlldr匯入時,出錯的可能性很小,基本都可以匯入成功。
因此,實踐中我建議大家使用方法二手工去控制spool檔案的格式,這樣可以減小出錯的可能性,避免走很多彎路。
自測例:將ssrv_sendsms_task表中的資料匯出到文字(資料庫Oracle 9i 作業系統 SUSE LINUX Enterprise Server 9)
spool_test.sh指令碼如下:
#!/bin/sh
DB_USER=zxdbm_ismp #DB USER
DB_PWD=zxin_smap #DB PASSWORD
DB_SERV=zx10_40_43_133 #DB SERVICE NAME
sqlplus -s $DB_USER/$DB_PWD@$DB_SERV<
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool promt.txt
select taskindex||'|'||commonindex||'|'||tasktype||'|'||to_number(to_char(sysdate,'YYYYMMDD')) from ssrv_sendsms_task;
spool off
EOF
執行./spool_test.sh後生成sp_test.txt,內容如下:
83|115|1|20080307
85|115|11|20080307
86|115|10|20080307
84|115|2|20080307
6|5|14|20080307
7|5|12|20080307
9|5|15|20080307
注:上面自測例中,spool promt.txt中的目標生成檔案promt.txt,在HP-UNX環境下的shell指令碼中呼叫Oracle的spool函式,如果將上述邏輯程式碼封裝為一個function,然後來呼叫這個function的話,則在shell指令碼中最終是不會生成promt.txt檔案的。只能直接執行邏輯程式碼,封裝後則spool函式失效。
對於promt.txt在相對路徑下,下面2中方法在shell環境中執行時,兩者只能擇一,兩者並存則spool函式會失效。假設promt.txt檔案生成的路徑為:/home/zxin10/zhuo/batchoperate/spoolfile
方式[1]
echo "start spool in shell.."
sqlplus -s zxdbm_ismp/zxin_smap<
set pagesize 0
set echo off feed off term off heading off trims off
set colsep '|'
set trimspool on
set linesize 10000
set trimspool on
set linesize 120
set newpage 1
spool /home/zxin10/zhuo/batchoperate/spoolfile/promt.txt
select batchindex||'|'||productid||'|'||contentid||'|'||optype||'|'||uploadfile from zxdbm_700.s700_batch_operation where status=1;
spool off
EOF
echo "end.."
方式[2]
echo "start spool in shell.."
cd /home/zxin10/zhuo/batchoperate/spoolfile
sqlplus -s zxdbm_ismp/zxin_smap<
set pagesize 0
set echo off feed off term off heading off trims off
set colsep '|'
set trimspool on
set linesize 10000
set trimspool on
set linesize 120
set newpage 1
spool promt.txt
select batchindex||'|'||productid||'|'||contentid||'|'||optype||'|'||uploadfile from zxdbm_700.s700_batch_operation where status=1;
spool off
EOF
echo "end.."
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28929558/viewspace-1218753/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle spoolOracle
- Spring 經典教程Spring
- oracle之spoolOracle
- Kafka入門經典教程Kafka
- Oracle 經典圖書Oracle
- Oracle 優化經典Oracle優化
- ORACLE SQL命令經典OracleSQL
- Oracle spool用方法Oracle
- Oracle 行列轉換 經典Oracle
- 最經典的黑客入門教程黑客
- oracle常用經典sql查詢OracleSQL
- 關於oracle的Spool命令Oracle
- MySQL tee實現Oracle SpoolMySqlOracle
- Makefile經典教程(掌握這些足夠)
- oracle約束學習經典案例Oracle
- oracle優化常用經典參考Oracle優化
- oracle常用經典SQL查詢 (轉)OracleSQL
- oracle常用經典SQL查詢(zt)OracleSQL
- Ps經典例項教程3000例
- wolf最近cisco認證視訊教程經典
- oracle最佳化常用經典參考Oracle
- oracle中cursor的使用經典資料Oracle
- 哪有python入門經典教程資源分享?Python
- oracle經典亂碼問題——靠靠靠靠Oracle
- Oracle_AWR報告分析指南(經典版)Oracle
- 經典入門教程:在ASP中使用SQL 語句SQL
- SICP 經典
- Oracle Reading List國外oracle經典書籍bookOracle
- oracle臨時表的經典學習資料Oracle
- Oracle RAC系列之:ASM基本操作維護(經典)OracleASM
- Oracle的AWR報告分析(經典串聯版)Oracle
- Oracle 最常用功能函式經典彙總Oracle函式
- 視訊教程-Python機器學習經典案例實戰-PythonPython機器學習
- ■■■照片後期處理經典例項教程1500篇■■■
- 人工智慧研究:經典推理和非經典推理人工智慧
- Oracle 10G中spool新增加的方法Oracle 10g
- linux教程:管理/var/spool/clientmqueue/下的大檔案LinuxclientMQ
- 經典面試題面試題