本文示範快速sqlldr導入、UTL_FILE導出Oracle表資料執行個體。
本文示範快速sqlldr導入、UTL_FILE導出Oracle表資料執行個體
表結構如下，示範資料約112萬，可自行準備。
1 create table MemberPointDemo
2 (
3 MEMBERID NUMBER(20) not null ,--會員ID
4 PointType VARCHAR2(20) not null,--積分類型
5 Points VARCHAR2(20),--積分
6 SDate VARCHAR2(20) not null,--積分記賬日期
7 Notes VARCHAR2(60)--備注
8
9 );
10 -- Create/Recreate indexes
11 create index IDX_MEMBERID on MemberPointDemo (MEMBERID);
12 alter table MemberPointDemo
13 add constraint PK_MEMBER_OUT primary key (MEMBERID, PointType, SDate ); 1、快速導入sqlldr
#### *******************最快導入***********************************************/
su - oracle
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
--裝載源表資料
sqlldr dbusrdms/******@DBDMS control=/home/oracle/memberpoint.ctl log=/home/oracle/2017_12_06_162045.log memberpoint.ctl檔案内容：
1 load data
2 infile '/home/oracle/2017_12_06_162045.CSV'
3 into table dbusrdms.MemberPointDemo append
4 fields terminated by ','
5 (MEMBERID, PointType, Points, SDate, Notes) csv檔案格式：
MEMBERID,PointType,Points,SDate,Notes
9281776,1,219,2015-01-01,2015增加
8758055,1,356.00,2015-01-01,2015增加 以上導入112萬記錄大約在50秒内完成。
2、快速導出UTL_FILE.FOPEN
1 /* ******************************* 導出為csv檔案最快******************/
2 DECLARE
3 VSFILE UTL_FILE.FILE_TYPE; --定義用于接收檔案句柄的類型
4 V_CNT NUMBER; --統計每個檔案加載行數
5 V_Date Varchar(20);
6 --字段清單
7 MEMBERID NUMBER(20);
8 PointType VARCHAR2(20);
9 Points VARCHAR2(20);
10 SDate VARCHAR2(20);
11 Notes VARCHAR2(60);
12
13
14 BEGIN
15 --DBMS_OUTPUT.ENABLE(1000000); -->避免報錯ORA-20000: ORU-10027: BUFFER OVERFLOW, LIMIT OF 10000 BYTES
16
17 --檔案命名規則..把表資料時間當做檔案命名...
18 V_Date := TO_CHAR(sysdate, 'YYYY_MM_dd_hh24MiSS');
19 --開始打開檔案，EXP_DIR為對應的目錄
20 VSFILE := UTL_FILE.FOPEN('EXP_DIR', V_Date || '.CSV', 'W');
21
22 --檔案字段标頭列印
23 UTL_FILE.PUT_LINE(VSFILE,'MEMBERID,PointType,Points,SDate,Notes');
24 --UTL_FILE.PUT_LINE(VSFILE, '會員ID,積分類型,積分,記賬日期,備注');
25
26 --每個檔案加載行數[每次進入循環都指派為0].排除标頭部分
27 V_CNT := 0;
28 --将FOR循環查詢的内容
29 FOR SQL_ IN (SELECT MEMBERID, PointType, Points, SDate, Notes FROM MemberPointDemo) LOOP
30 --字段清單
31 MEMBERID := SQL_.MEMBERID;
32 PointType := SQL_.PointType;
33 Points := SQL_.Points;
34 SDate := SQL_.SDate;
35 Notes := SQL_.Notes;
36
37
38 ----UTL_FILE.PUT_LINE 若需要EXCEL格式，需要每字段用逗号隔開,,WINDOWS EXCEL工具打開預設就是EXCEL格式
39 UTL_FILE.PUT_LINE(VSFILE, MEMBERID || ',' || PointType || ',' || Points || ',' || SDate || ',' || Notes );
40 --下面語句可以輸出帶引号的格式
41 --UTL_FILE.PUT_LINE(VSFILE, '"'||MEMBERID || '","' || PointType || '","' || Points || '","' || SDate || '","' || Notes || '"');
42
43 --計數器，每一條資料都循環+1
44 V_CNT := V_CNT + 1;
45 END LOOP;
46
47 --列印每個檔案 LOAD ROWS
48 DBMS_OUTPUT.PUT_LINE(V_Date || '.CSV檔案LOAD ROWS：' || V_CNT);
49
50 --放在LOOP 後，否則報錯 ORA-29282: 檔案 ID 無效/ORA-06512: 在 "SYS.UTL_FILE", LINE 878
51 --若不寫如下 強制輸出緩沖/關閉句柄，可能存在導出資料少于查詢條目
52 UTL_FILE.FFLUSH(VSFILE);
53 UTL_FILE.FCLOSE(VSFILE);
54
55 END; 以上，導出為指定目錄下的CSV檔案，112萬記錄，大約14秒。
邀月注：本文版權由邀月和部落格園共同所有，轉載請注明出處。
助人等于自助! [email protected]