spool 用法小結

先看一個文章：http://www.itpub.net/thread-317699-1-1.html

使用SQLPLUS的SPOOL功能我們可以獲得非常強大和豐富的報表輸出。SQLPLUS可以設定極為複雜的格式資訊，但是很多時候會有一些資訊是你不想要的。

在生産中常會遇到需要将數量比較大的表值導入到本地文本檔案中. 方法有很多種,比較常用的就是spool指令，本文将為大家介紹這個指令的實作，以及兩種實作方法的對比。

要輸出符合要求格式的資料檔案隻需在select時用字元連接配接來規範格式。比如有如下表

要輸出符合1,John,1234,這樣的資料格式就用select id||','||username||','||password||',' from myuser這樣的語句。

寫個下面這樣的腳本就行可以輸出符合要求格式的資料至檔案中，不會含有其它不需要東西，隻有資料部分。

－－腳本檔案名為expmyusr.sql，存放在e盤下。

 存資料的檔案名為e:\exp.txt

－－執行過程

SQL>; @e:\expmyusr.sql

*** Spooling to e:\exp.txt

1,John,"1234"

2,Jack,"12345"

3,Rose,"2345"

4,Joe,"384657"

5,Tom,"384655"

6,Jordan,"384455"

檢查可知結果符合要求。

我自己的測試結果如下：

首先建立exptest.sql腳本在e盤下：其内容為：

set echo on

set feedback on

set heading on

set verify off

set trimspool off

set pagesize 1000

set linesize 50

define fil= 'e:\exp.txt'

prompt *** Spooling to &fil

spool &fil

select object_id||','||object_name||','||'"'||owner||'"' from t;

spool off; 

執行上述腳本：

SQL> @e:\exptest.sql

SQL> set echo on 

SQL> set feedback on

SQL> set heading on

SQL> set verify off

SQL> set trimspool off

SQL> set pagesize 1000

SQL> set linesize 50

SQL> define fil= 'e:\exp.txt'

SQL> prompt *** Spooling to &fil

SQL> spool &fil

SQL> select object_id||','||object_name||','||'"'||owner||'"' from t;

OBJECT_ID||','||OBJECT_NAME||','||'"'||OWNER||'"'

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

20,ICOL$,"SYS"

44,I_USER1,"SYS"

28,CON$,"SYS"

15,UNDO$,"SYS"

29,C_COBJ#,"SYS"

3,I_OBJ#,"SYS"

25,PROXY_ROLE_DATA$,"SYS"

39,I_IND1,"SYS"

51,I_CDEF2,"SYS"

26,I_PROXY_ROLE_DATA$_1,"SYS"

17,FILE$,"SYS"

13,UET$,"SYS"

9,I_FILE#_BLOCK#,"SYS"

41,I_FILE1,"SYS"

48,I_CON1,"SYS"

38,I_OBJ3,"SYS"

7,I_TS#,"SYS"

53,I_CDEF4,"SYS"

19,IND$,"SYS"

14,SEG$,"SYS"

已選擇20行。

SQL> spool off;

—— 腳本執行結束

Oracle SPOOL的兩種方法之對比 

通常情況下，我們使用SPOOL方法，将資料庫中的表導出為文本檔案的時候會采用兩種方法，如下述：

方法一：采用以下格式腳本　

方法二：采用以下腳本

比較以上方法，即方法一采用設定分隔符然後由sqlplus自己使用設定的分隔符對字段進行分割，方法二将分隔符拼接在SELECT語句中，即手工控制輸出格式。

在實踐中，我發現通過方法一導出來的資料具有很大的不确定性，這種方法導出來的資料再由sql ldr導入的時候出錯的可能性在95%以上，尤其對大批量的資料表，如100萬條記錄的表更是如此，而且導出的資料檔案狂大。

而方法二導出的資料檔案格式很規整，資料檔案的大小可能是方法一的1/4左右。經這種方法導出來的資料檔案再由sqlldr導入時，出錯的可能性很小，基本都可以導入成功。

是以，實踐中我建議大家使用方法二手工去控制spool檔案的格式，這樣可以減小出錯的可能性，避免走很多彎路。 

一些關于spool的介紹參看：http://space.itpub.net/action-tag-tagname-spool