昨天開發(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
- 這個(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ù)字精度變化等。




