Oracle使用UTL_FILE文件包大批量数据导出到CSV(Excel)文件

Oracle使用UTL_FILE文件包大批量数据导出到CSV(Excel)文件1.创建测试表CREATE TABLE SCOTT.EMP1 AS SELECT * FROM SCOTT.EMP;CREATE TABLE SCOTT.EMP2 AS SELECT * FROM SCOTT.EMP;创建多张表，模拟多张表操作。2.创建初始化表CREATE TABLE SCOTT.EXP_TMP (T_SQL VARCHAR2 (100);这张表保存了存储过程执行过程中需要执行的sql语句，格式为select * from table_name（注意：一定不能加分号）。3.初始化数据INSERT INTO SCOTT.EXP_TMP SELECT SELECT * FROM | T.TABLE_NAME | FROM DBA_TABLES T WHERE REGEXP_LIKE (TABLE_NAME, EMP0-9*) AND owner = SCOTT ORDER BY 1; UPDATE SCOTT.EMP1 SET EMPNO=1 WHERE EMPNO=7369;UPDATE SCOTT.EMP1 SET EMPNO=2 WHERE EMPNO=7499;UPDATE SCOTT.EMP1 SET EMPNO=3 WHERE EMPNO=7521;UPDATE SCOTT.EMP2 SET EMPNO=4 WHERE EMPNO=7369;UPDATE SCOTT.EMP2 SET EMPNO=5 WHERE EMPNO=7499;UPDATE SCOTT.EMP2 SET EMPNO=6 WHERE EMPNO=7521;COMMIT;根据需要调整需要导出数据的表（通过调整sql语句实现）。4.创建目录及授权CREATE OR REPLACE directory dump_dir AS /home/oracle/;grant read,write on directory dump_dir to scott;如果目录已存在，只需要授权即可5.创建存储过程CREATE OR REPLACE PROCEDURE SCOTT.EXPORT_TO_CSV IS P_QUERY VARCHAR2(100); P_DIR VARCHAR2(10) := DUMP_DIR; P_FILENAME VARCHAR2(20) := emp.csv; L_OUTPUT UTL_FILE.FILE_TYPE; L_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR; L_COLUMNVALUE VARCHAR2(4000); L_STATUS INTEGER; L_COLCNT NUMBER := 0; L_SEPARATOR VARCHAR2(1); L_DESCTBL DBMS_SQL.DESC_TAB; L_COUNT NUMBER := 0; CURSOR MYCUR IS SELECT T_SQL FROM SCOTT.EXP_TMP ORDER BY 1;BEGIN -OPEN FILE L_OUTPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, a, 32767); -TABLE COUNT SELECT COUNT(*) INTO L_COUNT FROM SCOTT.EXP_TMP ORDER BY 1; -记录条数 -OPEN CURSOR OPEN MYCUR; FOR J IN 1 . L_COUNT LOOP -最外层循环为表的个数 FETCH MYCUR INTO P_QUERY; -DBMS_OUTPUT.PUT_LINE(P_QUERY); -DEFINE DATE FORMAT EXECUTE IMMEDIATE ALTER SESSION SET NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS; -OPEN CURSOR DBMS_SQL.PARSE(L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE); DBMS_SQL.DESCRIBE_COLUMNS(L_THECURSOR, L_COLCNT, L_DESCTBL); -DBMS_OUTPUT.PUT_LINE(L_THECURSOR); -DUMP TABLE COLUMN NAME L_SEPARATOR := ; FOR I IN 1 . L_COLCNT LOOP -循环为表的列数 UTL_FILE.PUT(L_OUTPUT, L_SEPARATOR | | L_DESCTBL(I).COL_NAME | ); -输出表字段 DBMS_SQL.DEFINE_COLUMN(L_THECURSOR, I, L_COLUMNVALUE, 4000); L_SEPARATOR := ,; END LOOP; UTL_FILE.NEW_LINE(L_OUTPUT); -输出表字段 -EXECUTE THE QUERY STATEMENT L_STATUS := DBMS_SQL.EXECUTE(L_THECURSOR); -DUMP TABLE COLUMN VALUE WHILE (DBMS_SQL.FETCH_ROWS(L_THECURSOR) 0) LOOP -取出表的所有记录 L_SEPARATOR := ; FOR K IN 1 . L_COLCNT LOOP DBMS_SQL.COLUMN_VALUE(L_THECURSOR, K, L_COLUMNVALUE); UTL_FILE.PUT(L_OUTPUT, L_SEPARATOR | | TRIM(BOTH FROM REPLACE(L_COLUMNVALUE, , ) | ); -输出表数据-TRIM(BOTH FROM REPLACE(CONVERT(L_COLUMNVALUE, AL32UTF8), , ) | ); -输出表数据为UTF8格式 L_SEPARATOR := ,; END LOOP; UTL_FILE.NEW_LINE(L_OUTPUT);-输出表数据 END LOOP;-DBMS_OUTPUTDBMS_OUTPUT.PUT_LINE(There are |J| table data have finished output at |sysdate|.); -打印输出表数据的信息 END LOOP; -CLOSE CURSOR DBMS_SQL.CLOSE_CURSOR(L_THECURSOR); -关闭游标 -CLOSE FILE UTL_FILE.FCLOSE(L_OUTPUT); -关闭文件EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE | - | SQLERRM); -输出异常信息E