spool的設定

標籤：ora

spool 的學習及使用

　　spool 常用的命令設定：

　　SET echo off　　　 　　　　 --在用start命令執行一個sql指令碼時,是否顯示指令碼中正在執行的SQL語句；

　　SET feedback off　 　　 --是否回顯本次sql命令處理的記錄條數，預設為on；

　　SET heading off　　　　 --是否顯示欄位標題，預設為on；

　　SET pagesize 50000　 --設定每頁有多少行數，預設為14。當值設為0時，輸出內容為一頁且不顯示欄位標題，為了避免分頁，我們通常可設定為0。

SET termout off　　　　 --是否在螢幕上顯示輸出的內容,或指令碼中的命令的執行結果，預設為on；

　　SET trimout on;　　　 --去除標準輸出每行後面多餘的空格，預設為off；

　　SET timing off　　　　　 --顯示每個sql語句花費的執行時間；

　　SET trimspool on　　 --去除重新導向（spool）輸出每行的拖尾空格，預設為off；

SET NULL text　　　 　　 --顯示時,用text值代替NULL值；

　　SET serveroutput off　 --是否顯示用DBMS_OUTPUT.PUT_LINE包進行輸出的資訊;--編寫預存程序時,大多會將必要的資訊輸出；

　　SET newpage none　 --設定頁與頁之間的分隔{1|n|NONE};當值為0時在每頁開頭有一個小的黑方框;當值為n時在頁和頁之間隔著n個空行;當為none 時，會在頁和頁之間沒有任何間隔；

SET linesize 1000　　　 --設定一行可以容納的字元數{80|n};輸出內容大於設定的行可容納的字元數,則折行顯示.據實際字元設定,過大匯出速度慢；

SET wrap on　　　　　　 --輸出行長度大於設定行長度時(用set linesize n命令設定);值為on時,多餘的字元另起一行顯示,否則多餘的字元將被切除，不予顯示；

　　SET verify off　　　　　 --是否顯示替代變數被替代前後的語句；

　　SET colsep‘ ‘;　　　 --域輸出分隔字元；

　　------------------------------------------------------------------

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

SQL*PLUS環境設定--

　　SET echo off　　　　　　　 --在用start命令執行一個sql指令碼時,不顯示指令碼中正在執行的SQL語句

　　SET heading off　　　　 --不顯示欄位的名稱　　　　　　　　

　　SET pagesize 0　　　　 --設定輸出每頁行數，為了避免分頁，可設定為0。設定為0時，輸出內容為一頁且不顯示欄位標題具有SET heading off　包含的功能。

　　SET newpage none　 --頁與頁之間沒有分隔　　　　　　　　　　　　　　　　　　　　

SET trimout on　　　 --去除標準輸出每行後面多餘的空格

SET trimspool on　　　 --去除重新導向（spool）輸出每行的拖尾空格

SET linesize 1000　　　 --設定一行可以容納的字元數

　　SET feedback off　　　　 --是否回顯本次sql命令處理的記錄條數，預設為on；

　　工作運用時匯出文本資料的常用格式：

　　SET echo off　

　　SET pagesize 0

　　SET feedback off

　　SET trimout on　

　　SET trimspool on　

　　SET linesize 1000

　　spool 路徑+檔案名稱

　　需要執行的SQL代碼

　　spool off

　　註：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匯入時，出錯的可能性很小，基本都可以匯入成功。

　　有網友在linux 伺服器上直接編輯指令碼實現：

　　將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/[email protected]$DB_SERV$amp;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$amp;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$amp;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檔案的格式，這樣可以減小出錯的可能性，避免走很多彎路。

spool的設定