昨天开发人员要求根据其提供的sql语句,将查询到的3百多万条数据导出为excel文件,以提供给相关人员进行线下统计分析。
我首先想到的是通过plsql工具的“导出查询结果”–>"csv文件"这种方法。使用这种方式导出几千上万条数据还行,对于导出几百万条数据就比较吃力了,如果网络稳定还好,如果网络不稳定导致连接中断,所有努力就白费了。
因此我采用了oracle自带的UTL_FILE包来实现将大批量数据导出为csv。
一、准备工作
在操作UTL_FILE工具包之前,需要先创建或使用一个已经存在的目录(directory)。如果是以普通用户来操作,还需要给普通用户赋予相应的权限。
--如果是普通用户,则需要赋予权限:
grant execute on utl_file to cqiwen;
--查询和创建目录
select * from dba_directories
create directory exp_dir as '/recover';
grant read,write on directory exp_dir to cqiwen;
二、导出大批量数据为txt文件
--通过UTL_FILE包导出数据至txt文件中
set serveroutput on
declare
VSFILE UTL_FILE.FILE_TYPE; --定义用于接收文件句柄的类型
V_CNT NUMBER; --统计每个文件加载行数或作为序号使用
begin
VSFILE:=UTL_FILE.FOPEN('EXP_DIR','test3.txt','w');
V_CNT := 1;
-- UTL_FILE.PUT_LINE(VSFILE,'序号, 流水id, 学校名称, 付款方式,...'); --可以为要导出的文件指定标题栏,最好与后面导出的列相对应
for x in (select * from cqiwen.tmp_20211220_1)
loop
UTL_FILE.PUT_LINE(VSFILE,V_CNT||','||x.payorderid||','||x.cname||','||x.pname||','||x.paytype||','||x.tcost||','||x.odsrc||','||x.devcode);
--以上是将要导出的字段列出来,并写到指定好的文件中
V_CNT := V_CNT + 1;
end loop;
DBMS_OUTPUT.PUT_LINE('Finished! ALL LOAD ROWS:' || V_CNT);
UTL_FILE.FFLUSH(VSFILE);
UTL_FILE.FCLOSE(VSFILE);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,2000));
end;
/
三、对导出的文件进行拆分
--对导出的文件进行拆分,以方便后续保存为csv时能够不超过最大行数上限
--参数最后一位指定为null时表示截止最后一行
BEGIN
utl_file.fcopy('EXP_DIR', 'test3.txt', 'EXP_DIR', 'sp_test1.txt',1,1024000);
END;
/
BEGIN
utl_file.fcopy('EXP_DIR', 'test3.txt', 'EXP_DIR', 'sp_test2.txt',1024001,null);
END;
/
四、转储为csv文件,然后即可通过excel工具打开
此时得到的txt文件并不能通过直接修改为csv后缀而转换为excel表格,因为utl_file包生成的文件是unix-dos格式的,在excel中并不能正确识别中文。
因此,先在UltraEdit中打开txt文件,然后UE中复制全文并粘贴到一个新文件中,将新文件另存为XX.csv即可。
五、补充
在本文发出后,热心的网友反馈还可以使用第三方工具sqluldr2来实现大量数据导出为csv文件。为了使本文更全面,因此我也补充一下关于sqluldr2的介绍。
1)这个软件声明中写的是对非商业用途免费,商业用途收费,类似于oracle软件。
License: Free for non-commercial useage, else 100 USD per server.
2)针对含中文的大量数据导出为csv文件的常用命令:
./sqluldr2 user=cqiwen/cqiwen sql=./query.sql head=yes charset=ZHS16GBK batch=yes file=./test_%B.csv
最后修改时间:2021-12-23 09:16:51
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。