上次寫了篇利用pl/sql讀文件(簡單)后，如果不清楚utl_file怎么讀文件的，可以參考我上次的博文，今天我寫下怎么使用utl_file寫文件，首先帶上我參考過的文章的鏈接：

http://stackoverflow.com/questions/3750192/how-to-write-to-files-using-utl-file-in-oracle

http://blog.csdn.net/liqfyiyi/article/details/7043942

http://qingyujingyu427.iteye.com/blog/402151

http://www.morganslibrary.org/hci/hci004.html

廢話不多說，上代碼：

declare

fHandle UTL_FILE.FILE_TYPE;

begin

--文件不能使用中文名

fHandle := UTL_FILE.FOPEN('ORADIR_F_DIR', 'test_write_file.sql', 'w');

UTL_FILE.PUT(fHandle, '中文測試');

UTL_FILE.PUT(fHandle, '使用\r\n換行是不行的');

UTL_FILE.PUT_LINE(fHandle,'');

--換行方法一:使用chr(10)回車

UTL_FILE.PUT(fHandle, '我要換行方法一'||chr(10)||'第三行');

--換行方法二:使用chr(13)換行

UTL_FILE.PUT(fHandle, '我要換行方法二'||chr(13)||'第四行');

--換行方法三:使用PUT_LINE

UTL_FILE.PUT_LINE(fHandle, '我要換行方法三');

UTL_FILE.PUT_LINE(fHandle, '我是制表符'||chr(9)||'看起來舒服多了');

--空白行

UTL_FILE.NEW_LINE(fHandle,1);

UTL_FILE.PUT(fHandle, '測試');

UTL_FILE.FCLOSE(fHandle);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Exception: SQLCODE=' || SQLCODE || ' SQLERRM=' ||

SQLERRM);

RAISE;

end;

運行結果如下：

說明下：ORADIR_F_DIR是目錄，怎么建目錄的請參考我的博文利用pl/sql讀文件(簡單)，文件名不能為中文，fopen最后一個參數常用的有--r 讀 w寫 a追加 rb只讀字節 wb只寫字節，如果不想覆蓋文件，請使用a(追加),其他的代碼里寫的很清楚了。

utl_file寫文件一個常見的功能是記錄日志，個人也推薦使用utl_file記錄日志：

declare

fHandle UTL_FILE.FILE_TYPE;

v_out number(3);

begin

--w覆蓋寫

fHandle := UTL_FILE.FOPEN('ORADIR_F_DIR', 'syslog_'||to_char(sysdate,'yyyy_mm_dd')||'.log', 'a');

UTL_FILE.put_line(fHandle,'輸出日志信息');

v_out:=1/0;

UTL_FILE.FCLOSE(fHandle);

EXCEPTION

WHEN OTHERS THEN

IF utl_file.is_open(fHandle) THEN

utl_file.PUT_LINE(fHandle,'Exception: SQLCODE=' || SQLCODE || ' SQLERRM=' ||

SQLERRM);

utl_file.fclose(fHandle);

END IF;

RAISE;

end;

結果為：

寫文件不僅僅是寫日志文件，還可以把數據庫里的clob.blob或者查詢結果輸出為xml,jpg,xls,cvs等后綴的文件，下面一樣舉例說明：

(一)輸出clob為log文件

create or replace procedure proc_write_clob_demo(id number) IS

sql_stmt VARCHAR2(100);

l_content clob;

l_fHandler UTL_FILE.FILE_TYPE;

BEGIN

sql_stmt := 'select content from t_blob_test where id=:id';

EXECUTE IMMEDIATE sql_stmt

into l_content

using id;

dbms_xslprocessor.clob2file(l_content,'ORADIR_F_DIR','writeclob_'||id||'.log');

UTL_FILE.FCLOSE(l_fHandler);

EXCEPTION

WHEN OTHERS THEN

IF UTL_FILE.IS_OPEN(l_fHandler) THEN

UTL_FILE.FCLOSE(l_fHandler);

END IF;

DBMS_OUTPUT.PUT_LINE('Exception: SQLCODE=' || SQLCODE || ' SQLERRM=' ||

SQLERRM);

RAISE;

END;

測試方法:

call proc_write_clob_demo(148)

結果為：

這里我說明下，使用dbms_xslprocessor.clob2file輸出clob字段為log文件時候，個人測試輸出的log文件為1M,輸出時間為1.8s，個人感覺應該有速度更快的方法，請知道的朋友告訴我一下。

(二)輸出clob為xml文件

這里注意下，clob里面放的要是xml文件，或者是XMLTYPE類型的，其他的不行。

CREATE OR REPLACE PROCEDURE proc_write_xml_demo (id number) IS

xml_str clob;

xml_file Utl_File.file_type;

offset NUMBER := 1;

buffer varchar2(32767);

buffer_size number := 2000;

begin

xml_file := utl_file.fopen('ORADIR_F_DIR','writexml_demo.xml','w');

xml_str := DBMS_XMLGEN.getXML('select content from xmltype_table where id='||id);

while(offset < dbms_lob.getlength(xml_str))

loop

buffer := dbms_lob.substr(xml_str,buffer_size,offset);

utl_file.put(xml_file,buffer);

utl_file.fflush(xml_file);

offset := offset + buffer_size;

end loop;

utl_file.fclose(xml_file);

dbms_lob.freetemporary(xml_str);

end;

測試方法為：

call proc_write_xml_demo(4)

結果為：

(三)輸出blob為img

CREATE OR REPLACE PROCEDURE PROC_GET_PIC_BLOB (i_xh VARCHAR2) IS

l_file UTL_FILE.FILE_TYPE;

l_buffer RAW(32767);

l_amount BINARY_INTEGER := 32767;

l_pos INTEGER := 1;

l_blob BLOB;

l_blob_len INTEGER;

BEGIN

SELECT image INTO L_BLOB FROM BXXX WHERE id = i_xh;

l_blob_len := DBMS_LOB.GETLENGTH(l_blob);

l_file := UTL_FILE.FOPEN('ORADIR_F_DIR',i_xh || '.jpg','WB',32767);

WHILE l_pos < l_blob_len LOOP

DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer);

UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE);

l_pos := l_pos + l_amount;

END LOOP;

UTL_FILE.FCLOSE(l_file);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.put_line('no data : ' || i_xh);

WHEN OTHERS THEN

IF UTL_FILE.IS_OPEN(l_file) THEN

UTL_FILE.FCLOSE(l_file);

RAISE;

END IF;

END PROC_GET_PIC_BLOB;

測試方法為：

select * from bxxx

call PROC_GET_PIC_BLOB(2)

結果為：

(四)輸出select結果為cvs文件

create or replace procedure proc_write_cvs_demo as

v_file UTL_FILE.FILE_TYPE;

v_buffer VARCHAR2(100);

begin

v_file := UTL_FILE.FOPEN('ORADIR_F_DIR',

'cvsfile' || to_char(sysdate, 'yyyy_mm_dd') ||

'.csv',

'w',

32767);

v_buffer := '員工編號,姓名,職位,上級,工作時間,薪水,部門';

UTL_FILE.PUT_LINE(v_file, v_buffer);

for v in (select '"' || empno || '","' || ename || '","' || job || '","' || mgr ||

'","' || to_char(hiredate, 'yyyy-mm-dd') || '","' || sale ||

'","' || deptno || '" ' result

from emp) loop

UTL_FILE.PUT_LINE(v_file, v.result);

end loop;

UTL_FILE.FCLOSE(v_file);

exception

when others then

DBMS_OUTPUT.PUT_LINE('Exception: SQLCODE=' || SQLCODE || ' SQLERRM=' ||

SQLERRM);

RAISE;

end;

測試方法為：

call proc_write_cvs_demo()

結果為：

(五)輸出select結果為xls文件

create or replace procedure proc_write_xls_demo as

v_file UTL_FILE.FILE_TYPE;

v_buffer varchar2(100);

type type_emp is record(

empno varchar2(15),

ename varchar2(30),

job varchar2(15),

mgr varchar2(10),

hiredate varchar2(12),

sale varchar2(10),

deptno varchar2(10));

type_empinfo type_emp;

cursor cur_emp is

select empno||chr(9),

ename||chr(9),

job||chr(9),

mgr||chr(9),

to_char(hiredate, 'yyyy-mm-dd')||chr(9),

sale||chr(9),

deptno

from emp

where rownum <= 10;

begin

--oracle導出到excel時不同的字段用chr(9)就可以起到將不同字段存到不同的excel列上

v_buffer:='員工編號'||chr(9)||'姓名'||chr(9)||'職位'||chr(9)||'上級'||chr(9)||'工作時間'||chr(9)||'薪水'||chr(9)||'部門';

v_file := UTL_FILE.FOPEN('ORADIR_F_DIR', 'xlsfile' || to_char(sysdate, 'yyyy_mm_dd')||'.xls', 'w', 32767);

utl_file.put_line(v_file, v_buffer);

open cur_emp;

loop

fetch cur_emp

into type_empinfo;

exit when cur_emp%notfound;

utl_file.put(v_file, type_empinfo.empno);

utl_file.put(v_file, type_empinfo.ename);

utl_file.put(v_file, type_empinfo.job);

utl_file.put(v_file, type_empinfo.mgr);

utl_file.put(v_file, type_empinfo.hiredate);

utl_file.put(v_file, type_empinfo.sale);

utl_file.put_line(v_file, type_empinfo.deptno);

-- utl_file.new_line(v_file,1);

--強制刷新到文件

--utl_file.fflush(v_file);

end loop;

utl_file.fclose(v_file);

close cur_emp;

exception

when others then

DBMS_OUTPUT.PUT_LINE('Exception: SQLCODE=' || SQLCODE || ' SQLERRM=' ||

SQLERRM);

RAISE;

end;

測試方法為：

call proc_write_xls_demo()

結果為：

博文只是簡單的介紹了如何使用utl_file輸出為文件，對于utl_file其他的用法如復制、得到文件屬性等沒有介紹，感興趣的朋友請仔細搜索。

文章寫到這里也該結束了，本文系原創，轉載請注明出處，對本文有不同意見的請留言指教，謝謝。

總結

以上是生活随笔為你收集整理的mysql utl_file_pl/sql应用之利用utl_file写文件的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。