简介
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;