使用Oracle内建包UTL_FILE包生成Excel或文本

简介

UTL_FILE是oracle提供的一个标准的工具包，用来读写文件使用。

1、创建用于文件操作的目录

CREATE OR REPLACE directory MY_DIR AS 'D:\test';   --此为windows系统路径，若是Linux请改为Linux路径

2、授予读写权限

grant read,write on directory MY_DIR to user;   --路径授权，添加对路径读、写权限

3、utl_file包授权

grant EXECUTE ON utl_file TO ETL;   --utl_file包授权，添加执行权限

4、执行

/* ******************************* 导出为csv文件最快******************/
DECLARE
  VSFILE       UTL_FILE.FILE_TYPE; --定义用于接收文件句柄的类型
  V_CNT        NUMBER; --统计每个文件加载行数
  V_Date       Varchar(20);
  --字段列表
  v_empno      NUMBER(20);
  v_ename      VARCHAR2(20);
  v_job        VARCHAR2(20);
  v_deptno     VARCHAR2(20);
  v_hiredate   VARCHAR2(60);

BEGIN
  --DBMS_OUTPUT.ENABLE(1000000); -->避免报错ORA-20000: ORU-10027: BUFFER OVERFLOW, LIMIT OF 10000 BYTES

  --文件命名规则..把表数据时间当做文件命名...
  V_Date := TO_CHAR(sysdate, 'YYYY_MM_dd_hh24MiSS');
  --开始打开文件，EXP_DIR为对应的目录
  VSFILE := UTL_FILE.FOPEN('MY_DIR', V_Date || '.txt', 'W');

  --文件字段标头打印
  UTL_FILE.PUT_LINE(VSFILE, 'empno,ename,job,deptno,hiredate');

  --每个文件加载行数[每次进入循环都赋值为0].排除标头部分
  V_CNT := 0;
  --将FOR循环查询的内容
  FOR SQL_ IN (select t.empno, t.ename, t.job, t.deptno, t.hiredate
                 from SCOTT.EMP t) LOOP
    --字段列表
    v_empno  := SQL_.empno;
    v_ename := SQL_.ename;
    v_job    := SQL_.job;
    v_deptno     := SQL_.deptno;
    v_hiredate     := SQL_.hiredate;
  
    ----UTL_FILE.PUT_LINE 若需要EXCEL格式，需要每字段用逗号隔开,,WINDOWS EXCEL工具打开默认就是EXCEL格式
    UTL_FILE.PUT_LINE(VSFILE,
                      v_empno || ',' || v_ename || ',' || v_job || ',' ||
                      v_deptno || ',' || v_hiredate);
    --下面语句可以输出带引号的格式                  
    --UTL_FILE.PUT_LINE(VSFILE, '"'||v_empno || '","' || v_ename || '","' || v_job || '","' || v_deptno || '","' || v_hiredate || '"');
  
    --计数器，每一条数据都循环+1
    V_CNT := V_CNT + 1;
  END LOOP;

  --打印每个文件 LOAD ROWS
  DBMS_OUTPUT.PUT_LINE(V_Date || '.txt文件LOAD ROWS：' || V_CNT);

  --放在LOOP 后，否则报错 ORA-29282: 文件 ID 无效/ORA-06512: 在 "SYS.UTL_FILE", LINE 878
  --若不写如下 强制输出缓冲/关闭句柄，可能存在导出数据少于查询条目
  UTL_FILE.FFLUSH(VSFILE);
  UTL_FILE.FCLOSE(VSFILE);

END;