Oracle spool 應用

標籤：

關於spool(spool是SQLPLUS的命令，不是SQL文法裡面的東西。) 
對於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命令處理的記錄條數“已選擇XX行”，預設為on。set heading off;　　 -- 輸出域標題，預設為on。set pagesize 0;　　　-- 輸出每頁行數，預設為24，當值設為0時，輸出內容為一頁且不顯示欄位標題，為了避免分頁，可設定為0。set termout off;　　 -- 顯示指令碼中的命令的執行結果，預設為on。set trimout on;　　　-- 去除標準輸出每行的拖尾空格，預設為off。set trimspool on;　　-- 去除重新導向（spool）輸出每行的拖尾空格，預設為off。set linesize 1000;   -- 設定一行可以容納的字元數{80|n}，輸出內容大於設定的值則折行顯示。據實際字元設定，過大匯出速度慢。set wrap on;         -- 輸出行長度大於設定行長度時(用set linesize n命令設定);值為on時,多餘的字元另起一行顯示,否則多餘的字元將被切除，不予顯示，預設為換行。set timing on;       -- 設定顯示“經過時間：XXXX”。set serveroutput on; -- 設定允許顯示輸出類似dbms_output。set numwidth 12;　   -- 輸出number類型域長度，預設為10。set null text        -- 顯示時，用text值代替NULL值；set newpage none;    -- 設定頁與頁之間的分隔{1|n|NONE};當值為0時在每頁開頭有一個小的黑方框;當值為n時在頁和頁之間隔著n個空行;當為none時，會在頁和頁之間沒有任何間隔。set verify off;      -- 是否顯示替代變數被替代前後的語句。set space 0;         -- 設定各列間的空格數。

 

匯出文本資料的建議格式：

set newpage noneset heading offset space 0set pagesize 0set trimout onset trimspool onset linesize 2500

 

註：linesize要稍微設定大些，免得資料被截斷，它應和相應的TRIMSPOOL結合使用防止匯出的文本有太多的尾部空格。但是如果LINESIZE設定太大，會大大降低匯出的速度，另外在WINDOWS下匯出最好不要用PLSQL匯出，速度比較慢，直接用COMMEND下的SQLPLUS命令已最小化的視窗執行。

對於欄位內包含很多斷行符號分行符號的應該給予過濾，形成比較規矩的文字檔。通常情況下，我們使用SPOOL方法，將資料庫中的表匯出為文字檔的時候會採用兩種方法，如下述：

方法一：採用以下格式指令碼　

set colsep ‘|‘ --設定|為資料行分隔符號set trimspool onset linesize 120set pagesize 2000set newpage 1set heading offset term offset num 18set feedback offspool 路徑+檔案名稱select * from tablename;spool off 

 

方法二：採用以下指令碼

set trimspool onset linesize 120set pagesize 2000set newpage 1set heading offset term offspool 路徑+檔案名稱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/shDB_USER=zxdbm_ismp #DB USERDB_PWD=zxin_smap #DB PASSWORDDB_SERV=zx10_40_43_133 #DB SERVICE NAMEsqlplus -s $DB_USER/[email protected]$DB_SERV<<EOF # -s 參數屏蔽列印到螢幕上的其他資訊，只顯示sql執行後從DB中查詢出來的資訊，過濾掉spool函數執行時在檔案中寫入的其他資訊。set trimspool onset linesize 120set pagesize 2000set newpage 1set heading offset term offspool promt.txtselect taskindex||‘|‘||commonindex||‘|‘||tasktype||‘|‘||to_number(to_char(sysdate,‘YYYYMMDD‘)) from ssrv_sendsms_task;spool offEOF

 

執行./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<<EOFset pagesize 0set echo off feed off term off heading off trims offset colsep ‘|‘set trimspool onset linesize 10000set trimspool onset linesize 120set newpage 1spool /home/zxin10/zhuo/batchoperate/spoolfile/promt.txtselect batchindex||‘|‘||productid||‘|‘||contentid||‘|‘||optype||‘|‘||uploadfile from zxdbm_700.s700_batch_operation where status=1;spool offEOFecho "end.." 

 

方式[2]

echo "start spool in shell.."cd /home/zxin10/zhuo/batchoperate/spoolfilesqlplus -s zxdbm_ismp/zxin_smap<<EOFset pagesize 0set echo off feed off term off heading off trims offset colsep ‘|‘set trimspool onset linesize 10000set trimspool onset linesize 120set newpage 1spool promt.txtselect batchindex||‘|‘||productid||‘|‘||contentid||‘|‘||optype||‘|‘||uploadfile from zxdbm_700.s700_batch_operation where status=1;spool offEOFecho "end.." 

 

 

Oracle spool 應用