Oracle利用存储过程导出大数据

敏而好好学 2021-11-08

1079

测试环境：Windows Server 2003 Enterprise Edition SP2

数据库环境：Oracle 10.2

在日常数据处理中，我们常常需要将查询的结果集导出成表格。常用的方法是在PL/SQL Developer或其它数据库管理软件里编写SQL语句，再点击执行，然后点击Fetch last page(Alt+End)罗列出所有数据，最后Copy to Excel。该方法在处理数据量少速度还行，但在查询的数据集达到十万或百万级别（Excel 2007版本最多可以有1048576行）时，该方法在第三步罗列所有数据就要消耗大量时间。那么有没有其它的方法可以快速导出数据表格呢？

答案是肯定的。

第一步，编写SQL语句创建临时大数据表，本例是以SCOTT用户下的EMP表，利用卡迪尔积创建十万级别的基本表EMP_TEST。

第二步，创建存储过程，代码如下（摘自互联网）：

CREATE OR REPLACE PROCEDURE SQL_TO_CSV --custom stored procedure name
(
 P_QUERY IN VARCHAR2, -- sql query statement
 P_DIR IN VARCHAR2, -- the directory of file
 P_FILENAME IN VARCHAR2 -- the export filename
 )
 IS
  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;
  P_MAX_LINESIZE NUMBER := 32000; --max linesize,must less than 32787
BEGIN
  --OPEN FILE
  L_OUTPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, 'W', P_MAX_LINESIZE);
  --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);
  --DUMP TABLE COLUMN NAME
  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 I IN 1 .. L_COLCNT LOOP
      DBMS_SQL.COLUMN_VALUE(L_THECURSOR, I, L_COLUMNVALUE);
      UTL_FILE.PUT(L_OUTPUT,
                  L_SEPARATOR || '"' ||
                  TRIM(BOTH ' ' FROM REPLACE(L_COLUMNVALUE, '"', '""')) || '"');
      L_SEPARATOR := ',';
    END LOOP;
    UTL_FILE.NEW_LINE(L_OUTPUT);
  END LOOP;
  --CLOSE CURSOR
  DBMS_SQL.CLOSE_CURSOR(L_THECURSOR);
  --CLOSE FILE
  UTL_FILE.FCLOSE(L_OUTPUT);
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END;

第三步：创建导出路径（Windows目录）

create or replace directory OUT_PATH as 'D:\';

第四步：通过命令窗口执行导出语句，语法如下：

EXEC sql_to_csv（'SQL语句','目录名','导出文件名'）

SQL> EXEC system.sql_to_csv('select * from scott.emp_test','OUT_PATH','emp_test.csv');

该基本表含有28万条数据，导出结果对比：

执行时间：21.453s

导出的表格文件18.7MB

常用方法执行时间：109.578s

如果导出的数据查询SQL语句带有过滤条件，需要使用连接符 || 。以EMP_TEST表为例，提取条件是字段JOB值为CLERK的数据，执行语句如下：

EXEC system.sql_to_csv('select * from scott.emp_test where job='''||'CLERK'||'''','OUT_PATH','emp_test_clerk.csv');

结语：如果导出的数据有查询条件且比较复杂，执行语句容易报错。建议将查询的数据创建临时表，导出临时表数据即可。

【完毕】

oracle 大数据

文章转载自敏而好好学，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle利用存储过程导出大数据

评论