昨天開發(fā)人員要求根據(jù)其提供的sql語句，將查詢到的3百多萬條數(shù)據(jù)導(dǎo)出為excel文件，以提供給相關(guān)人員進(jìn)行線下統(tǒng)計(jì)分析。

我首先想到的是通過plsql工具的“導(dǎo)出查詢結(jié)果”–>"csv文件"這種方法。使用這種方式導(dǎo)出幾千上萬條數(shù)據(jù)還行，對(duì)于導(dǎo)出幾百萬條數(shù)據(jù)就比較吃力了，如果網(wǎng)絡(luò)穩(wěn)定還好，如果網(wǎng)絡(luò)不穩(wěn)定導(dǎo)致連接中斷，所有努力就白費(fèi)了。

因此我采用了oracle自帶的UTL_FILE包來實(shí)現(xiàn)將大批量數(shù)據(jù)導(dǎo)出為csv。

一、準(zhǔn)備工作

在操作UTL_FILE工具包之前，需要先創(chuàng)建或使用一個(gè)已經(jīng)存在的目錄(directory)。如果是以普通用戶來操作，還需要給普通用戶賦予相應(yīng)的權(quán)限。

--如果是普通用戶，則需要賦予權(quán)限：
grant execute on utl_file to cqiwen;

--查詢和創(chuàng)建目錄
select * from dba_directories
create directory exp_dir as '/recover';
grant read,write on directory exp_dir to cqiwen;

二、導(dǎo)出大批量數(shù)據(jù)為txt文件

--通過UTL_FILE包導(dǎo)出數(shù)據(jù)至txt文件中
set serveroutput on
declare
  VSFILE UTL_FILE.FILE_TYPE; --定義用于接收文件句柄的類型
  V_CNT NUMBER; --統(tǒng)計(jì)每個(gè)文件加載行數(shù)或作為序號(hào)使用
begin
    VSFILE:=UTL_FILE.FOPEN('EXP_DIR','test3.txt','w');
    V_CNT := 1;
    -- UTL_FILE.PUT_LINE(VSFILE,'序號(hào), 流水id, 學(xué)校名稱, 付款方式,...');  --可以為要導(dǎo)出的文件指定標(biāo)題欄,最好與后面導(dǎo)出的列相對(duì)應(yīng)
    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);
        --以上是將要導(dǎo)出的字段列出來，并寫到指定好的文件中
        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;
/

三、對(duì)導(dǎo)出的文件進(jìn)行拆分

--對(duì)導(dǎo)出的文件進(jìn)行拆分，以方便后續(xù)保存為csv時(shí)能夠不超過最大行數(shù)上限
--參數(shù)最后一位指定為null時(shí)表示截止最后一行
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;
/

四、轉(zhuǎn)儲(chǔ)為csv文件，然后即可通過excel工具打開

此時(shí)得到的txt文件并不能通過直接修改為csv后綴而轉(zhuǎn)換為excel表格，因?yàn)閡tl_file包生成的文件是unix-dos格式的，在excel中并不能正確識(shí)別中文。

因此，先在UltraEdit中打開txt文件，然后UE中復(fù)制全文并粘貼到一個(gè)新文件中，將新文件另存為XX.csv即可。

五、當(dāng)導(dǎo)出的列中有clob大字段時(shí)進(jìn)行切割

DECLARE
  clob_data   clob;
  content_txt varchar2(4000) := '';
  data_len    number := 0;
  offset_1    number := 1;
  dest_dir    VARCHAR2(256) := 'EXP_DIR';   --需先在oracle中創(chuàng)建此目錄并授予用戶讀寫權(quán)限
  dest_file   VARCHAR2(256) := 'GET_PRO_DDL.txt';
  dest_handle UTL_FILE.file_type;
  cursor cur1 is
    select rownum rnm, a.*
      from (select owner,
                   object_name,
                   object_type,
                   dbms_metadata.get_ddl(object_type => OBJECT_TYPE,
                                         name        => object_name,
                                         schema      => owner) ddl
              from dba_objects
             where owner not in ('SYS',
                                 'SYSTEM',
                                 'PUBLIC',
                                 )
               AND OBJECT_TYPE in ('PROCEDURE', 'PACKAGE', 'FUNCTION')
               AND OWNER NOT IN
                   (SELECT USERNAME
                      FROM DBA_USERS
                     WHERE ACCOUNT_STATUS <> 'OPEN')
             ORDER BY owner, object_type, object_name) a;
  v_c cur1%rowtype;
BEGIN
  dbms_output.enable(1000000);
  dest_file := 'GET_PRO_DDL_' || to_char(sysdate, 'yyyymmdd') || '.txt';
  open cur1;
  loop
    fetch cur1
      into v_c;
    exit when cur1%notfound;
    clob_data := v_c.rnm || ',' || v_c.owner || ',' || v_c.object_name || ',' ||v_c.ddl || CHR(13);
    select length(clob_data) into data_len from dual;
    offset_1 := 1;
    loop
      exit when data_len <= 0;
      content_txt := substr(clob_data, offset_1, 3000);  --按3000個(gè)字符進(jìn)行切割
      dbms_output.put_line(v_c.rnm || ',' || v_c.object_name || ',' ||data_len);  --輸出切割列表
      dest_handle := UTL_FILE.fopen(dest_dir, dest_file, 'a', 32767);
      UTL_FILE.put_line(dest_handle, content_txt, true);
      utl_file.fflush(dest_handle);
      UTL_FILE.fclose(dest_handle);
      --dbms_output.put_line(content_txt);
      data_len := data_len - 3000;
      offset_1 := offset_1 + 3000;
    end loop;
  end loop;
  close cur1;
  IF UTL_FILE.is_open(dest_handle) THEN
    UTL_FILE.fclose(dest_handle);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    RAISE;
END;

六、補(bǔ)充

在本文發(fā)出后，熱心的網(wǎng)友反饋還可以使用第三方工具sqluldr2來實(shí)現(xiàn)大量數(shù)據(jù)導(dǎo)出為csv文件。為了使本文更全面，因此我也補(bǔ)充一下關(guān)于sqluldr2的介紹。

1）這個(gè)軟件聲明中寫的是對(duì)非商業(yè)用途免費(fèi)，商業(yè)用途收費(fèi)，類似于oracle軟件。
License: Free for non-commercial useage, else 100 USD per server.
2）針對(duì)含中文的大量數(shù)據(jù)導(dǎo)出為csv文件的常用命令：

./sqluldr2 user=cqiwen/cqiwen sql=./query.sql head=yes charset=ZHS16GBK batch=yes file=./test_%B.csv 

3. 這個(gè)工具已經(jīng)很早就沒有人維護(hù)和更新了，由于代碼不開源，其中的bug也就無法繼續(xù)修復(fù)了。目前已知的問題有：

• 3.1.當(dāng)表中有除英文和中文外的其它國(guó)家文字時(shí)，導(dǎo)出必定會(huì)有亂碼；
• 3.2.當(dāng)表中有clob列時(shí)，如果該列儲(chǔ)存的文本超過4000字符，則導(dǎo)出時(shí)會(huì)報(bào)錯(cuò)。且無法在導(dǎo)出時(shí)進(jìn)行切割，除非在原表中先切割為多列再導(dǎo)出；
• 3.3.當(dāng)導(dǎo)出的列中有date類型或number類型時(shí)，可能導(dǎo)出為csv文件后會(huì)出現(xiàn)問題，包括但不限于數(shù)字精度變化等。