暂无图片
暂无图片
17
暂无图片
暂无图片
暂无图片

oracle导出大批量数据为csv或txt文件

原创 cqiwen 2021-12-21
6644

昨天开发人员要求根据其提供的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进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论