SQL*Loader對不同檔案及格式的處理方法
1.多個資料檔案，匯入同一張表
通常對於邏輯比較複雜的系統可能存在這種情況，因為匯出的資料來源於多個系統，因此可能提供給DBA的也是多個資料檔案。這種情況並不一定需要執行多次載入，只需要在控制檔案中做適當配置即可。不過有一點非常重要，提供的資料檔案中的資料存放格式必須完全相同。

建立演示表Manager表

--建立Manager表
CREATE TABLE MANAGER
(
MGRNO     NUMBER,
MNAME     VARCHAR2(30),
JOB       VARCHAR2(300),
REMARK    VARCHAR2(4000)
);
Table created.

有多個資料檔案，分別如下：

--資料檔案1
[oracle@cancer ~]$ cat ldr_case8_1.dat
10,SMITH,SALES MANAGER
11,ALLEN.W,TECH MANAGER
16,BLAKE,HR MANAGER

--資料檔案2
[oracle@cancer ~]$ cat ldr_case8_2.dat
12,WARD,SERVICE MANAGER
13,TURNER,SELLS DIRECTOR
15,JAMES,HR DIRECTOR

--資料檔案3
[oracle@cancer ~]$ cat ldr_case8_3.dat
17,MILLER,PRESIDENT

建立控制檔案，制定多個INFILE引數即可，控制檔案如下：

--控制檔案
[oracle@cancer ~]$ cat ldr_case8.ctl
LOAD DATA
INFILE ldr_case8_1.dat
INFILE ldr_case8_2.dat
INFILE ldr_case8_3.dat
TRUNCATE INTO TABLE MANAGER
FIELDS TERMINATED BY ","
(MGRNO,MNAME,JOB)

執行sqlldr命令，並檢視結果

--執行sqlldr命令
[oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case8.ctl
Commit point reached - logical record count 3
Commit point reached - logical record count 6
Commit point reached - logical record count 7

--檢視結果
SQL> select * from manager;
MGRNO      MNAME      JOB              REMARK
---------- ---------- ---------------- ----------
10         SMITH      SALES MANAGER
11         ALLEN.W    TECH MANAGER
16         BLAKE      HR MANAGER
12         WARD       SERVICE MANAGER
13         TURNER     SELLS DIRECTOR
15         JAMES      HR DIRECTOR
17         MILLER     PRESIDENT

2.同一個資料檔案，匯入不同表
控制檔案提供了多種邏輯判斷方式，只要能把邏輯清晰地描述出來，SQL*Loader就能按照指定的邏輯執行載入。
資料檔案如下：

--資料檔案
[oracle@cancer ~]$ cat ldr_case9.dat
BON SMITH CLEAK       3904
BON ALLEN SALER,M     2891
BON WARD SALER,"S"   3128
BON KING PRESIDENT   2523
MGR 10 SMITH    SALES MANAGER
MGR 11 ALLEN.W TECH MANAGER
MGR 16 BLAKE    HR MANAGER
TMP SMITH 7369 CLERK    1020 20
TMP ALLEN 7499 SALESMAN 1930 30
TMP WARD 7521 SALESMAN 1580 30
TMP JONES 7566 MANAGER 3195 20

需求是將MGR開頭的記錄匯入到Manager表，以BON開頭的記錄匯入到BONUS表，其他記錄存放到廢棄檔案中，建立控制檔案如下:

--控制檔案
[oracle@cancer ~]$ cat ldr_case9.ctl
LOAD DATA
INFILE ldr_case9.dat
DISCARDFILE ldr_case9.dsc
TRUNCATE
INTO TABLE BONUS
WHEN TAB='BON'
(
TAB FILLER POSITION(1:3),
ENAME POSITION(6:10),
JOB POSITION(*+1:20),
SAL POSITION(*+3:27)
)
INTO TABLE MANAGER
WHEN TAB='MGR'
(
TAB FILLER POSITION(1:3),
MGRNO POSITION(6:7),
MNAME POSITION(9:15),
JOB POSITION(*+2:30)
)

雖然這個控制檔案看起來比之前的都要複雜，但只有一個新語法，即關鍵字，我們這裡透過WHEN來實現判斷，很容易理解。同時，指定了DISCARDFILE引數，以生成不滿足載入條件的廢棄檔案，如果你有心，不妨等執行完sqlldr命令後査看ldr_case9.dsc檔案和ldr_case9.log檔案。
另外注意，控制檔案中WHEN邏輯判斷不支援OR關鍵字，因此如果你的判斷條件有多個，則只能透過AND連線，而不能直接使用OR。
執行sqlldr命令，並檢視結果

--執行sqlldr命令
[oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case9.ctl
Commit point reached - logical record count 12

--檢視結果
SQL> select * from bonus;
ENAME      JOB   SAL        COMM
---------- --------- ---------- ----------
SMITH CLEAK     3904
ALLEN SALER,M   2891
WARD       SALER,"S" 3128
KING       PRESIDENT 2523

SQL> select * from manager;
MGRNO   MNAME      JOB        REMARK
------- ---------- -------------------- ----------
10      SMITH      SALES MANAGER
11      ALLEN.W    TECH MANAGER
16      BLAKE      HR MANAGER

這裡貼一下log日誌和廢棄日誌

--log日誌
[oracle@cancer ~]$ cat ldr_case9.log

Control File:   ldr_case9.ctl
Data File:      ldr_case9.dat
Bad File:     ldr_case9.bad
Discard File: ldr_case9.dsc
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table BONUS, loaded when TAB = 0X424f4e(character 'BON')
Insert option in effect for this table: TRUNCATE

Column Name                  Position   Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
TAB                                   1:3     3           CHARACTER
(FILLER FIELD)
ENAME                                6:10     5           CHARACTER
JOB                             NEXT+1:20    19           CHARACTER
SAL                             NEXT+3:27    24           CHARACTER

Table MANAGER, loaded when TAB = 0X4d4752(character 'MGR')
Insert option in effect for this table: TRUNCATE

Column Name                  Position   Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
TAB                                   1:3     3           CHARACTER
(FILLER FIELD)
MGRNO                                 6:7     2           CHARACTER
MNAME                                9:15     7           CHARACTER
JOB                             NEXT+2:30    28           CHARACTER

Record 8: Discarded - failed all WHEN clauses.
Record 9: Discarded - failed all WHEN clauses.
Record 10: Discarded - failed all WHEN clauses.
Record 11: Discarded - failed all WHEN clauses.
Record 12: Discarded - failed all WHEN clauses.

Table BONUS:
4 Rows successfully loaded.
0 Rows not loaded due to data errors.
8 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Table MANAGER:
3 Rows successfully loaded.
0 Rows not loaded due to data errors.
9 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Space allocated for bind array:                   7168 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:            12
Total logical records rejected:         0
Total logical records discarded:        5

Run began on Wed Jan 27 14:20:36 2016
Run ended on Wed Jan 27 14:20:36 2016

Elapsed time was:     00:00:00.13
CPU time was:         00:00:00.03

--廢棄日誌
[oracle@cancer ~]$ cat ldr_case9.dsc
TMP SMITH 7369 CLERK 1020 20
TMP ALLEN 7499 SALESMAN 1930 30
TMP WARD 7521 SALESMAN 1580 30
TMP JONES 7566 MANAGER 3195 20

3.資料檔案前N行不想匯入
假如某天你接到一項資料載入需求，使用者提供了一份100萬行的資料檔案，告訴你只導後50萬行，恭喜，你接到了一個正常的需求！
實現的方式較多，比如修改資料檔案，只保留後50萬行（Windows下藉助EditPlus 這類文字工具可以輕鬆實現，Linux/UNIX下透過TAIL等命令也可以輕易實現），如果你人很懶，不想修改檔案，那正合sqlldr胃口，人家早早地就提供好了SKIP引數專用於滿足此類需求。
資料檔案如下：

--資料檔案
[oracle@cancer ~]$ cat ldr_case10.dat
SQL> select ename,mgr,job,sal from emp;
ENAME        MGR JOB             SAL
---------- ----- --------- ---------
SMITH       7902 CLERK         1020
ALLEN       7698 SALESMAN      1930
WARD        7698 SALESMAN      1580
JONES       7839 MANAGER       3195
MARTIN      7698 SALESMAN      1580
BLAKE       7839 MANAGER       3180
CLARK       7839 MANAGER       2172
SCOTT       7566 ANALYST       3220
KING             PRESIDENT     4722
TURNER      7698 SALESMAN      1830
ADAMS       7788 CLERK         1320
JAMES       7698 CLERK         1280
FORD        7566 ANALYST       3220
MILLER      7782 CLERK         1022

我們只對該資料檔案只從第4行開始匯入，即前3行不進行匯入，建立控制檔案如下：

--控制檔案
[oracle@cancer ~]$ cat ldr_case10.ctl
LOAD DATA
INFILE ldr_case10.dat
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:6),
XCOL FILLER position(13:16),
JOB position(18:26),
SAL position(32:35)
)

執行sqlldr命令，並檢視結果

--執行sqlldr命令
[oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case10.ctl skip=3
Commit point reached - logical record count 14

--檢視結果
SQL> select * from bonus;
ENAME       JOB      SAL       COMM
---------- --------- ---------- ----------
SMITH      CLERK     1020
ALLEN      SALESMAN 1930
WARD       SALESMAN 1580
JONES      MANAGER   3195
MARTIN     SALESMAN 1580
BLAKE      MANAGER   3180
CLARK      MANAGER   2172
SCOTT      ANALYST   3220
KING       PRESIDENT 4722
TURNER     SALESMAN 1830
ADAMS      CLERK    1320

ENAME JOB       SAL       COMM
---------- --------- ---------- ----------
JAMES CLERK    1280
FORD       ANALYST   3220
MILLER    CLERK     1022

如果使用者要求較高，明確指定只載入第XX到第XX行的記錄，sqlldr還有一個引數叫LOAD,配置LOAD引數即可輕鬆實現。
這裡仍使用上述資料檔案，需求改為只匯入第4到9行的記錄，我們連控制檔案都不需要修改，只需要在執行sqlldr時再加上LOAD引數即可：
執行sqlldr命令(即skip=3跳過前3行，load=6，載入接下來的6行記錄)，並檢視結果

--執行sqlldr命令
[oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case10.ctl skip=3 load=6
Commit point reached - logical record count 6

--檢視結果
SQL> select * from bonus;
ENAME JOB        SAL       COMM
---------- --------- ---------- ----------
SMITH      CLERK    1020
ALLEN    SALESMAN 1930
WARD    SALESMAN 1580
JONES MANAGER   3195
MARTIN    SALESMAN 1580
BLAKE MANAGER   3180

4.載入的資料中有換行符
由於標準換行符也是sqlldr識別資料行結束的標誌符，因此要將含換行符的資料載入到表中稍複雜一點點，而且需要根據實際情況來處理，不同情況的處理方式也不一樣，但基本思路是相同的，就是要同sqlldr指明什麼時候才需要進行換行操作。
1）手工指定的換行符
在手工指定換行符的情況下，資料檔案中的換行符並不是標準的換行標誌，而是使用者自定義的一個標識字元（或多個字元組成），這種情況的處理比較簡單，如資料檔案如下：

--資料檔案
[oracle@cancer ~]$ cat ldr_case11_1.dat
10,SMITH,SALES MANAGER,This is SMITH.\nHe is a Sales Manager.
11,ALLEN.W,TECH MANAGER,This is ALLEN.W.\nHe is a Tech Manager.
16,BLAKE,HR MANAGER,This is BLAKE.\nHe is a Hr Manager.

我們可以透過控制檔案，在資料載入前處理remark列的資料，將使用者指定的字元替換為chr(10),即標準換行符，建立控制檔案如下：

--控制檔案
[oracle@cancer ~]$ cat ldr_case11_1.ctl
LOAD DATA
INFILE ldr_case11_1.dat
TRUNCATE INTO TABLE MANAGER
FIELDS TERMINATED BY ","
(
MGRNO,
MNAME,
JOB,
REMARK "REPLACE(:remark,'\\n',chr(10))"
)

這裡需要注意的是，替換時必須指定"\\n"而不只是"\n"，因為"\n"會被SQLLDR 識別成換行符並轉換成換行標誌，這樣可能導致資料載入出錯。而是預設轉義符，指定該轉義符後sqlldr就會將"\n"識別成普通字元了。
執行sqlldr命令，並檢視結果

--執行sqlldr命令
[oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case11_1.ctl
Commit point reached - logical record count 3

--檢視結果
SQL> select * from manager;
MGRNO   MNAME    JOB             REMARK
-----   ------- --------------- ----------------------------
10   SMITH    SALES MANAGER   THIS IS SMITH.
                                He is a Sales Manager.
11   ALLEN.W TECH MANAGER    THIS IS ALLEN.W
                                He is a Tech Manager.
16   BLAKE    HR MANAGER      THIS IS BLAKE.
                                He is a Hr Manager.

2）指定FIX屬性處理換行符(定長資料檔案專用)
資料檔案如下：

--資料檔案
[oracle@cancer ~]$ cat ldr_case11_2.dat
10 SMITH   SALES MANAGER THIS IS SMITH.
HE IS A SALES MANAGER.
11 ALLEN.W TECH MANAGER THIS IS ALLEN.W
HE IS A TECH MANAGER.
16 BLAKE   HR MANAGER    THIS IS BLAKE.
HE IS A HR MANAGER.

建立控制檔案如下：

--控制檔案
[oracle@cancer ~]$ cat ldr_case11_2.ctl
LOAD DATA
INFILE ldr_case11_2.dat "fix 68"
TRUNCATE INTO TABLE MANAGER
(
MGRNO POSITION(1:2),
MNAME POSITION(*+1:10),
JOB POSITION(*+1:24),
REMARK POSITION(*+1:65)
)

FIX是INFILE關鍵字的一個屬性，INFILE不僅有FIX屬性，還有VAR和STR等屬性
執行sqlldr命令，並檢視結果

--執行sqlldr命令
[oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case11_2.ctl
Commit point reached - logical record count 3

--檢視結果
SQL> select * from manager;
MGRNO   MNAME    JOB             REMARK
-----   ------- --------------- ----------------------------
10   SMITH    SALES MANAGER   THIS IS SMITH.
                                HE IS A SALES MANAGER.
11   ALLEN.W TECH MANAGER    THIS IS ALLEN.W
                                HE IS A TECH MANAGER.
16   BLAKE    HR MANAGER      THIS IS BLAKE.
                                HE IS A HR MANAGER.

這種方式其實就是在載入資料檔案之前，先透過FIX屬性指定每行的長度（這裡每行68個字元，包括換行符在內,該例子上不夠字元的用空格來代替了，注意上面資料檔案中的結尾的空格），到了指定長度就換行，不管中間有沒有換行符，因此僅能用於定長字串的資料檔案，因為只有字串定長，你才知道應該在INFILE處指定什麼值。

3）指定VAR屬性處理換行符（行頭部標識換行）
前面提到INFILE關鍵字還支援VAR屬性，語法格式為INFILE filename "var n", n的值不能超過40,否則會報錯，如果不指定n則預設值為5。
本小節就演示透過這種方式處理換行符。總的來說，這確實是相當有才的一種方式，首先透過VAR屬性在每行開頭指定一個固定長度的字串，該字串指明該行的長度，透過這種方式支援變長字串。
資料檔案如下：

--資料檔案
[oracle@cancer ~]$ cat ldr_case11_3.dat
06110,SMITH,SALES MANAGER,This is SMITH.
He is a Sales Manager.
06311,ALLEN.W,TECH MANAGER,This is ALLEN.W.
He is a Tech Manager.
05516,BLAKE,HR MANAGER,This is BLAKE.
He is a Hr Manager.

資料檔案中每行開頭的061，063，055分別表示該行取61，63，55個字元
建立控制檔案如下：

--控制檔案
[oracle@cancer ~]$ cat ldr_case11_3.ctl
LOAD DATA
INFILE ldr_case11_3.dat "var 3"
TRUNCATE INTO TABLE MANAGER
FIELDS TERMINATED BY ","
(MGRNO,MNAME,JOB,REMARK)

執行sqlldr命令，並檢視結果

--執行sqlldr命令
[oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case11_3.ctl
Commit point reached - logical record count 3

--檢視結果
SQL> select * from manager;
MGRNO MNAME     JOB             REMARK
------ --------- --------------- -----------------------------
   10 SMITH     SALES MANAGER   This is SMITH.
                                He is a Sales Manager.
   11 ALLEN.W   TECH MANAGER    This is ALLEN.W.
                                He is a Tech Manager.
   16 BLAKE     HR MANAGER      This is BLAKE.
                                He is a Hr Manager.

①這種方式可以視為第2種方式的變種，該方法不在INFILE處指定行長度，而改為在每行的行首位置標註該行長度，以支援變長型別的資料格式。
②綜合來看，這兩種方式實用價值都比較低，先不說使用者是否真有如此耐心來生成這樣格式的資料檔案，就算使用者按照這種格式提供，要知道windows、Linux/UNIX下因作業系統自身原因,對換行符識別也不同，在Windows環境下換行標誌由"回車chr(13)+換行chr(10)" 兩個位元組組成，而Linux/UNIX環境則是"換行chr(10)"一個位元組，也就是說指定了長度，作業系統變了以後，長度有可能也得跟著變，這就造成使用者提供的資料檔案通用性較差。
③有沒有一種更簡單的方式呢？事實上確實沒有太簡單的方式，在前面的內容中就提到，你需要為sqlldr指明什麼時候應該換行，因此對資料檔案進行預處理是必然的，不過相對來講，下面將要介紹的方式更易於操作，也更可行一些。

4）指定STR屬性處理換行符（行尾部標識換行）
這種方式也需要先對資料檔案做處理，在記錄換行處打上一個標記，比如"丨"（當然可以定義為其他字元，但注意不要與要匯入的資料有衝突），這樣sqlldr見到該字元就知道換行的時候到了。
由於單個字元出現在匯入資料中的機率較高，因此建議換行標誌儘可能由多個字元組成，通常習慣於定義“字元+換行符”作為新的換行標記，這裡我們也採用這種方式。
資料檔案和控制檔案如下：

--資料檔案
[oracle@cancer ~]$ cat ldr_case11_4.dat
10,SMITH,SALES MANAGER,This is SMITH.
He is a Sales Manager.|
11,ALLEN.W,TECH MANAGER,This is ALLEN.W.
He is a Tech Manager.|
16,BLAKE,HR MANAGER,This is BLAKE.
He is a Hr Manager.|

--控制檔案
[oracle@cancer ~]$ cat ldr_case11_4.ctl
LOAD DATA
INFILE ldr_case11_4.dat "str '|\n'"
TRUNCATE INTO TABLE MANAGER
FIELDS TERMINATED BY ","
(MGRNO,MNAME,JOB,REMARK)

執行sqlldr命令，並檢視結果

--執行sqlldr命令
[oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case11_4.ctl
Commit point reached - logical record count 3

--檢視結果
SQL> select * from manager;
MGRNO MNAME     JOB             REMARK
------ --------- --------------- -----------------------------
   10 SMITH     SALES MANAGER   This is SMITH.
                                He is a Sales Manager.
   11 ALLEN.W   TECH MANAGER    This is ALLEN.W.
                                He is a Tech Manager.
   16 BLAKE     HR MANAGER      This is BLAKE.
                                He is a Hr Manager.

STR屬性中支援兩種字元指定方式：
'char_string':普通字元，即標準的可見字元，不過也有些不可見字元可以透過下列反斜槓標識的方式在字串模式中指定：
\n:表示換行。
\t:表示行製表符(tab)。
\f:表示換頁。
\v:表示列製表符。
\r:表示回車。
說到這裡，又不得不再次提及Windows和Linux/UNIX對換行符識別的差異，Linux/UNIX下指定"\n"即可，Windows下需要指定"\r\n"才表示一個完整的換行符。
X'hex_string':二進位制字元。對於一些不可見字元，如像回車換行這類字元，可以將其轉換成十六進位制，然後再透過str X'hex_str'方式指定。

比如上述控制檔案中的功能如果用二進位制字元表示，形式如下:

INFILE ldr_case11_4.dat "str X'7C0A'"

要査看指定字元的十六進位制編碼，可以透過UTL_RAW.CAST_TO_RAW生成，例如:

SQL> select utl_raw.cast_to_raw('|'||chr(10)) from dual;
UTL_RAW.CAST_TO_RAW('|'||CHR(1
---------------------------------
7C0A

這種方式相當於自定義一個換行標誌，標準換行符不再擁有特殊的意義，只是作為要載入資料的一部分。較第一種方式而言最大的優勢是，資料檔案相對更容易處理，只需要在生成資料檔案時，最後一列附加一個指定字元即可，對於稍有SQL基礎的人來說，這都是小case。

本文內容參考<塗抹Oracle-三思筆記>一書，該書是基於Windows，本文引用了該書的指令碼和結論的整理在Linux親自測試透過，並對一些小問題進行了處理

SQL*Loader的使用總結(三)

相關文章