使用存储过程将Oracle数据批量导出为多个csv文件

数据库有如下表结构：

aaa ( 

        obj_id          NUMBER,  (Primary Key)

        obj_name   VARCHAR2(80),

        obj_size   NUMBER NOT NULL

        obj_time       NUMBER NOT NULL

        ms_version        NUMBER NOT NULL

)

现在有100万条记录，要求每1000条记录导出为一个csv文件，共计1000个文件，文件名称格式为output0.csv，output1.csv......output999.csv。

每个csv文件每行记录包含：$1, $2, $3, $4四部分内容。

$1 = obj_id|obj_name

$2 = obj_size *10+obj_version

$3 = obj_time in format YYYY-MM-DD HH:mi

$4 = obj_name

要求第一个文件在500ms以内产生出来，后面每个文件生成间隔时间不得大于500ms，速度均匀。

方案一：使用pl/sql存储过程分页查询，每次查询1000条记录写一个文件，实现代码如下：

[sql] view plain copy

1. create or replace procedure export_to_csv(P_DIR IN VARCHAR2)  
2. is  
3. CSV_OUTPUT UTL_FILE.FILE_TYPE;  
4. MAX_LINE NUMBER := 1000;  
5. OUT_FILE_NAME varchar2(20);  
6. OBJ_SIZE NUMBER;  
7. MIN_RECORD NUMBER;  
8. MAX_RECORD NUMBER;  
9. OBJ_DATE varchar2(100);  
10. BEGIN_TIME NUMBER;  
11. END_TIME NUMBER;  
12. begin  
13.         BEGIN_TIME := dbms_utility.get_time;--记录开始时间，注意dbms_utility.get_time获取的时间戳单位是1/100s  
14.         FOR I IN 0..999 loop  
15.                 OUT_FILE_NAME := 'output' || I ||'.csv';--拼接文件名  
16.                 MIN_RECORD := i*1000 + 1;--分页查询起始记录  
17.                 MAX_RECORD := (i+1)*1000;--分页查询中止记录  
18.                 --以写方式打开指定目录中指定文件名文件  
19.                 CSV_OUTPUT := UTL_FILE.FOPEN(P_DIR, OUT_FILE_NAME, 'W', MAX_LINE);  
20.             --隐式游标，每次查询1000条记录  
21.                 FOR cur IN (SELECT * FROM(SELECT A.*, ROWNUM rn FROM(SELECT * FROM aaa)A WHERE ROWNUM <= MAX_RECORD) WHERE rn >= MIN_RECORD) LOOP  
22.                         OBJ_SIZE := cur.OBJ_SIZE*10 + cur.MS_VERSION;  
23.             --将数值类型数据转换为日期字符串  
24.                         OBJ_DATE := TO_CHAR(TO_DATE('19700101','yyyymmdd') + cur.OBJ_TIME/86400,'yyyy-MM-dd HH24:mi');  
25.             --写文件  
26.                         UTL_FILE.PUT_LINE(CSV_OUTPUT,cur.OBJ_ID || '|' || cur.OBJ_NAME || ',' || OBJ_SIZE || ',' || OBJ_DATE || ',' || cur.OBJ_NAME);  
27.                 END LOOP;  
28.                 UTL_FILE.FCLOSE(CSV_OUTPUT);  
29.         END LOOP;  
30.         END_TIME := dbms_utility.get_time;  
31.         DBMS_output.put_line('Total time=' || (END_TIME-BEGIN_TIME)*10 || 'ms.');  
32. END;  
33. /  
34. begin  
35. export_to_csv('MYDIR');  
36. end;  
37. /  

经过测试，1000个文件总共花150s，平均每个文件150ms。

注意：执行该存储过程之前，UTL_FILE的目录必须以sysdba的用户创建，然后授权给使用的用户，代码中的MYDIR通过下面方法创建：

首先，以sysdba用户登录。

其次，创建目录，如：CREATE DIRECTORY MYDIR AS 'c:\oraload\';

最后，给用户授权，如：GRANT READ,WRITE ON DIRECTORY MYDIR TO scott;

方案二：部分页，一次性将100万条记录全部查询出来放到游标中，每1000条写一个文件，代码如下：

[sql] view plain copy

1. create or replace procedure export_to_csv(P_DIR IN VARCHAR2)  
2. is  
3. --显示游标，一次性将数据全部查询完  
4. cursor mycur is select * from aaa;  
5. --行记录  
6. myrecord aaa%rowtype;  
7. CSV_OUTPUT UTL_FILE.FILE_TYPE;  
8. MAX_LINE NUMBER := 1000;  
9. OUT_FILE_NAME varchar2(20);  
10. OBJ_SIZE NUMBER;  
11. OBJ_DATE varchar2(100);  
12. BEGIN_TIME NUMBER;  
13. END_TIME NUMBER;  
14. COUNT_NUM NUMBER;  
15. begin  
16.         BEGIN_TIME := dbms_utility.get_time;  
17.       --显式打开游标  
18.         open mycur;  
19.         FOR I IN 0..999 loop  
20.             --拼接文件名  
21.                 OUT_FILE_NAME := 'output' || I ||'.csv';  
22.                 COUNT_NUM := 0;  
23.                 --打开文件  
24.                 CSV_OUTPUT := UTL_FILE.FOPEN(P_DIR, OUT_FILE_NAME, 'W', MAX_LINE);   
25.             --每1000条写一个文件  
26.                 while COUNT_NUM < 1000 loop  
27.                   --逐条叫游标记录放入记录中  
28.                         fetch mycur into myrecord;  
29.                         OBJ_SIZE := myrecord.OBJ_SIZE*10 + myrecord.MS_VERSION;  
30.                         OBJ_DATE := TO_CHAR(TO_DATE('19700101','yyyymmdd') + myrecord.OBJ_TIME/86400,'yyyy-MM-dd HH24:mi');  
31.                         UTL_FILE.PUT_LINE(CSV_OUTPUT,myrecord.OBJ_ID || '|' || myrecord.OBJ_NAME || ',' || OBJ_SIZE || ',' || OBJ_DATE || ',' || myrecord.OBJ_NAME);  
32.                 COUNT_NUM := COUNT_NUM+1;  
33.             --取游标中下一条记录  
34.                 fetch mycur into myrecord;  
35.                 END LOOP;  
36.                 UTL_FILE.FCLOSE(CSV_OUTPUT);  
37.         END LOOP;  
38.       --关闭游标  
39.         close mycur;  
40.         END_TIME := dbms_utility.get_time;  
41.         DBMS_output.put_line('Total time=' || (END_TIME-BEGIN_TIME)*10 || 'ms.');  
42. END;  
43. /  
44. begin  
45. export_to_csv('MYDIR');  
46. end;  
47. /  

经测试发现，100万条记录总共耗时50s，平均每个文件50ms，速度大大提高。