pl/sql应用之利用utl_file写文件

53873039oycg

浏览: 824483 次
性别:

最近访客更多访客>>

nessiah

hao___feng

China丰

Rorlay

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

oracle

utl_file写文件

上次写了篇利用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;

测试方法为：