oracle导出大批量数据为csv或txt文件 -m6米乐安卓版下载

昨天开发人员要求根据其提供的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