使用utl_file做選擇性資料匯出

在平時的資料匯出中使用exp/expdp能夠滿足絕大部分的資料匯出任務。如果有一些表的資料不多，但是查詢條件要複雜一些，使用exp/expdp就很吃力了。
或者在和外部系統的互動中，使用xml或者文字檔案是一個很相容的選擇，這個時候使用exp/expdp也滿足不了要求。
這個時候可以考慮使用utl_file的提供的一些功能來做選擇性的資料匯出。
先來使用utl_file做一個簡單的例子，輸出兩行文字內容到output.txt檔案中。一行Hello,一行hello word
declare
v_filehandle UTL_FILE.FILE_TYPE;
begin
v_filehandle:=utl_file.fopen('/u01/ora11g/test/test','output.txt','w');
UTL_FILE.PUTF (v_filehandle,' REPORT: GENERATED ON%s\n', SYSDATE);
UTL_FILE.NEW_LINE (v_filehandle);
UTL_FILE.PUTF (v_filehandle, '%s\n','hello ');
UTL_FILE.PUTF (v_filehandle, 'hello: %s\n','world ');
UTL_FILE.FCLOSE (v_filehandle);
end;
/

執行pl/sql之後的輸出如下：

[ora11g@rac1 test]$ cat output.txt
 REPORT: GENERATED ON14-SEP-14

hello
hello: world

這個地方需要說明一下，我在  /u01/ora11g/test/test 輸出了檔案output.txt,事先沒有建立任何的directory。因為utl_file_dir這個引數的預設值是*
SQL> show parameter utl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines               string
utl_file_dir                         string      *

我們來做一個更有實際意義的。
從表data中輸出100行資料到output.txt中。
declare
v_filehandle UTL_FILE.FILE_TYPE;
begin
v_filehandle:=utl_file.fopen('/u01/ora11g/test/test','output.txt','w');
UTL_FILE.PUTF (v_filehandle,'---export data from table data:', SYSTIMESTAMP);
UTL_FILE.NEW_LINE (v_filehandle);
for i in(select * from data where rownum<100) loop
UTL_FILE.PUTF (v_filehandle, '%s,%s\n',i.zhu,i.ke);
end loop;
UTL_FILE.FCLOSE (v_filehandle);
end;
/

輸出內容如下，可以看到都是按照逗號分隔。顯示的情況還不錯。
[ora11g@rac1 test]$ cat output.txt
---export data from table data:
0,2
2,1
1,2
1,0
3,1
0,1
0,3
2,2
6,2
1,0
0,2
0,0
3,0
1,0
1,2
0,1


因為utl_file在新版本中一直都是推薦使用directory來替代的，我們也可以使用directory物件來實現。黃色的部分TEST就是directory的名字，指向'/u01/ora11g/test/test'

declare
v_filehandle UTL_FILE.FILE_TYPE;
begin
v_filehandle:=utl_file.fopen('TEST','output.txt','w');
UTL_FILE.PUTF (v_filehandle,'---export data from table data:', SYSTIMESTAMP);
UTL_FILE.NEW_LINE (v_filehandle);
for i in(select * from data where rownum<100) loop
UTL_FILE.PUTF (v_filehandle, '%s,%s\n',i.zhu,i.ke);
end loop;
UTL_FILE.FCLOSE (v_filehandle);
end;
/


輸出的結果沒有任何變化。

有的人可能說是用spool也可以實現，而且更靈活，在一定程度上是的，不過還是和utl_file有一定的區別。
比如我沒有設定NLS_LANG的變數值，在sqlplus中檢視中文可能就有問題。但是系統層面沒有任何影響。

可以看到在sqlplus中顯示是亂碼的形式，但是在輸出檔案中顯示的是正確的中文格式。
SQL> select *from test;

        ID NAME
---------- -----------------------------
         1 ??????

SQL> declare
  2  v_filehandle UTL_FILE.FILE_TYPE;
  3  begin
  4  v_filehandle:=utl_file.fopen('/u01/ora11g/test/test','output.txt','w');
  5  UTL_FILE.PUTF (v_filehandle,'---export data from table data:', SYSTIMESTAMP);
  6  UTL_FILE.NEW_LINE (v_filehandle);
  7  for i in(select * from test where rownum<100) loop
  8  UTL_FILE.PUTF (v_filehandle, '%s,%s\n',i.id,i.name);
  9  end loop;
 10  UTL_FILE.FCLOSE (v_filehandle);
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> host
[ora11g@rac1 test]$ cat output.txt
---export data from table data:
1,突破玩法界限