Oracle spool 用法小結__Oracle

 

對於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<<EOF
# -s 參數屏蔽列印到螢幕上的其他資訊，只顯示sql執行後從DB中查詢出來的資訊，過濾掉spool函數執行時在檔案中寫入的其他資訊。 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<<EOF 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<<EOF 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.."