歸納幾條簡單方便的經驗之談吧。前幾天，一個同事為做專案和筆者討論將資料庫檢索處理結果輸出的方法。為了簡單明瞭，筆者按照不同的需求場景準備了兩種策略供同事進行選擇，記錄下來，權作不時之需的留存。

文字檔案輸出，特別是大資料量文字檔案輸出，是我們在實際需求領域中經常遇到的場景。文字檔案是我們最早接觸的檔案格式，格式單一，內容簡單。但是，也正是因為結構簡單，是很多“中間結構檔案”通常選擇的載體。從最早簡單的txt、csv，到現在越來越多出現xml，本質上都是以文字格式檔案進行儲存。

所謂“中間結構檔案”，也就是我們通常所說的介面檔案。如果系統之間需要進行大規模資料傳輸、接入或者互動，雙方共同認可的“協議”也就是問題的關鍵。透過介面檔案格式外加自動化上傳、定位和檢索機制，是可以實現解耦方式的系統間資料互動。

Oracle環境中通常使用的文字生成方式傳統上有兩種，一種是藉助原生的sqlplus命令列工具，將資料轉出到客戶端目錄上。注意：sqlplus命令列系列預設輸出是螢幕。另一種是藉助utl_file工具包將資料輸出到資料庫服務端（Server Side）。兩種方法各有利弊優缺點，各有適應的場景。下面分別進行討論。

1、實驗環境介紹

筆者使用Oracle 11gR2版本進行測試，具體版本為11.2.0.4。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

2、Sqlplus的Spool方法

Spool方法是指令碼工程師比較常用的策略。思路其實也比較簡單：在傳統的程式設計結構中，資料處理結果的輸出是有一個導向定位機制的。預設情況下，Sqlplus系工具的輸出是螢幕視窗。Spool方法就是重新設定輸出方式，將結果文字內容輸出到磁碟檔案中。

使用spool檔案有兩個方面需要關注：首先是檔案位置。生成的檔案，無論是Linux/AIX檔案還是Windows系列，都是在客戶端所在的計算機（執行程式的機器）上生成。另一個就是文字量限制，無論是使用sqlplus、還是PL/SQL Developer的Command Windows視窗，都會遇到潛在的緩衝區buffer溢位風險。這也就限制了生成檔案的大小。

下面我們透過一個簡單實驗來進行證明，實驗資料表結構如下：

SQL> desc test_user;

Name Type Nullable Default Comments

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

OBJECT_ID CHAR(32) String - Object Id

USER_ID CHAR(32) Y String - Unique User ID

FIRSTNAME CHAR(50) Y String - Users first name

LASTNAME CHAR(50) Y String - Users last name

SHORTNAME CHAR(5) Y String - Users short name

IS_ACTIVE CHAR(1) Y Character - Boolean

在sqlplus命令列中依次執行：

SQL> spool d:\spool_test.txt

Started spooling to d:\spool_test.txt

SQL>

SQL> set echo off;

SQL> set feedback off;

SQL> set newpage none;

SQL> set linesize 1000;

SQL> set pagesize 0;

SQL> set term off;

SQL> set timing off;

SQL> set verify off;

SQL> select trim(USER_ID)||','||trim(FIRSTNAME)||','||trim(LASTNAME)||','||trim(IS_ACTIVE)

2 from test_user;

SQL> spool off;

Stopped spooling to d:\spool_test.txt

注意幾個細節問題：

首先在SQL>命令提示符，使用spool命令，就可以啟動/關閉檔案寫入磁碟動作。Spool on就是預設開啟命令，而spool後加入路徑就指定了檔案目錄名稱。關閉寫入spool off後，檔案寫入動作自動停止。注意：從檔案大小角度看，只有在spool off的時候，通常系統才將結果從緩衝區寫入到檔案中。

其次是sqlplus引數配置。Sqlplus易用難精，有很多控制引數用於輸出輸入資料方式。對於一般使用者而言，可以直接保留一份固定的控制引數模板，對相同需求的命令可以直接使用。

生成檔案之後，我們就可以在客戶端機器的目錄上找到對應檔案。其中內容恰好是滿足逗號分隔資料要求的。

111,222,222,Y

111,2222,33,Y

11,222,33,Y

（篇幅原因，有省略…..）

Spool方法的優點是很明顯的，就是簡單易用，對使用使用者許可權要求低，只要能夠使用sqlplus工具，就可以生成檔案。同時，生成檔案在客戶端，也不需要DBA和系統管理員設定目錄許可權管理空間消耗。

同時，spool方法的缺點也是比較明顯的，就是生成檔案大小限制。進入11g之後，sqlplus在緩衝區上有所擴大，但是依然還是很大的工作隱患。站在系統自動化的角度，生成的介面檔案存放在客戶端也不是一種規範的做法，不利於後續自動化傳輸處理。

總而言之，spool方法比較適用於小規模、簡單資料檔案的生成。

3、utl_file包使用

UTL_FILE是Oracle官方推薦的一種經典檔案生成方法，主要原則是透過utl_file包來進行文字檔案讀寫動作。與spool方法最大的區別，在於utl_file包主要是生成在資料庫伺服器端（也就是Oracle Instance執行的伺服器上），同時utl_file包對於檔案讀寫的控制更加細粒度化，以一種類似於C語言的方式進行檔案讀寫。

從目前看，utl_file生成檔案依然是從資料庫端生成檔案比較成熟的方案，特別是大介面檔案。在一些高效能需求的場景下，還是有競爭力的。

對於utl_file包，不能不說到引數utl_file_dir。在Oracle 9.2之前，這個引數是生成讀寫utl_file執行的最重要引數。

SQL> show parameter utl_file

NAME TYPE VALUE

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

utl_file_dir string

在現在我們在網路查詢資料時，還是能夠看到對utl_file_dir引數的設定要求。在9.2之前的版本中，如果進行檔案讀寫，都需要這個這個引數，將讀寫檔案的所在目錄新增到其上。否則Oracle就不能承認這個目錄下的檔案操作許可權。略麻煩的是，這個引數修改要在spfile中進行，生效就需要重啟伺服器。

這種場景在9.2版本之後有了變化，directory物件的出現，提供了更好的讀寫目錄管理和許可權管理。最大的一個好處，就是在程式碼中，可以不用硬編碼方式寫目錄結構。所以，當前utl_file_dir目錄基本不會再使用，只是出於系統相容性目的。

使用utl_file包方法使用如下步驟：

步驟1：建立directory目錄

[oracle@sicslife /]$ su - root

Password:

--確保作業系統層面的許可權！

[root@sicslife ~]# cd /

[root@sicslife /]# chown -R oracle:oinstall /upload/

[root@sicslife /]# ls -l | grep upload

drwxr-xr-x. 4 oracle oinstall 4096 Aug 6 21:21 upload

SQL> create directory utl_path as '/upload';

SQL>

SQL> select directory_name, directory_path from dba_directories;

DIRECTORY_NAME DIRECTORY_PATH

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

UTL_PATH /upload

使用directory要解決兩個層面許可權，一個是作業系統層面，要讓Oracle作業系統使用者可以使用目錄。另一個是directory物件使用權，要進行顯示的授權。

SQL> grant write on directory utl_path to scott;

Grant succeeded

SQL> grant execute on utl_file to scott;

Grant succeeded

最後，可以在程式碼中進行呼叫。

SQL> set serveroutput on size 1000;

SQL> declare

2 out_file utl_file.file_type; --檔案型別，也就是控制程式碼物件

3 vc_file_name varchar2(100);

4 vc_line varchar2(100);

5 i number;

6 begin

7 vc_file_name := 'utl_file_test.txt';

8

9 out_file := utl_file.fopen('UTL_PATH',vc_file_name,'w'); --寫方式開啟檔案

10

11 if (utl_file.is_open(out_file)) then

12 for i in 1..100 loop

13 vc_line := to_char(i)||','||i||'Lines~';

14 utl_file.put_line(out_file,vc_line);

15 end loop;

16 else

17 dbms_output.put_line('Open Failure~');

18 end if;

19

20 utl_file.fclose(out_file); --和C語言一樣，需要顯示進行關閉

21 end;

22 /

PL/SQL procedure successfully completed

最後，就可以在作業系統層面，找到對應檔案。

[root@sicslife /]# cd /upload/

[root@sicslife upload]# ls -l

total 12

drwxr-xr-x. 7 oracle oinstall 4096 Aug 27 2013 database

drwx------. 3 oracle oinstall 4096 Aug 5 17:26 igb-5.3.2

-rw-r--r--. 1 oracle oinstall 1184 Aug 7 02:19 utl_file_test.txt

[root@sicslife upload]# cat utl_file_test.txt

1,1Lines~

2,2Lines~

3,3Lines~

4,4Lines~

5,5Lines~

6,6Lines~

（篇幅原因，有省略…….）

[root@sicslife upload]#

Utl_file包是一種比較成熟的檔案讀寫方案，除了例項中操作的步驟方法之外，還定義了很多有用的讀寫方法、異常型別，這對於我們進行完善程式設計是很有意義的。同時，在實際應用中，utl_file有著更多的細節因素和限制特性，本篇不予累述。

3、結論

利用資料庫生成檔案，是非常常見的需求。在不借助第三方工具的情況下，spool和utl_file是不錯的工具選擇。

淺說兩種輸出Oracle字元檔案的方法

相關文章