SQL*Loader對不同檔案及格式的處理方法
1.Excel檔案
一般的Excel檔案最大行數不超過65536行，說明資料處理量並不大，處理Excel的方式是將其另存為CSV格式檔案,然後即可按照正常方式匯入即可。

2.要載入的檔案不是以逗號分隔
有兩種方式可以參考：
1)修改資料檔案，將分隔符替換為逗號。
2)修改控制檔案，將FIELDS TERMINATED BY的值修改為實際的分隔符。

3.要載入的資料中包含分隔符
例如,要向scott.bonus表插入資料提供的資料格式如下：
SMITH,CLEAK,3904
ALLEN,"SALER,M",2891
WARD,"SALER,""S""",3128
KING,PRESIDENT,2523
修改控制檔案,注意下列示例程式碼中的粗體字元,OPTIONALLY ENCLOSED BY引數指明定界符為雙引號(CSV格式檔案預設定界符就是雙引號,你可以根據實際情況修改OPTIONALLY的引數值),如下所示：

--控制檔案
[oracle@cancer ~]$ cat ldr_case2.ctl
LOAD DATA
INFILE ldr_case2.dat
TRUNCATE INTO TABLE BONUS
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(ENAME,JOB,SAL)

--資料檔案
[oracle@cancer ~]$ cat ldr_case2.dat
SMITH,CLEAK,3904
ALLEN,"SALER,M",2891
WARD,"SALER,""S""",3128
KING,PRESIDENT,2523

sqlldr執行如上程式碼，並查詢結果如下：

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

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

4.資料檔案沒有分隔符
如下的資料檔案專業叫做定長字串，sqlldr中處理定長字串也輕而易舉。針對此例，我們將控制檔案修改如下：

--控制檔案
[oracle@cancer ~]$ cat ldr_case3.ctl
LOAD DATA
INFILE ldr_case3.dat
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:5),
JOB position(10:18),
SAL position(23:26)
)

--資料檔案
[oracle@cancer ~]$ cat ldr_case3.dat
SMITH    CLEAK        3904
ALLEN    SALESMAN     2891
WARD     SALESMAN     3128
KING     PRESIDENT    252

position關鍵字用來指定列的開始和結束位置，如JOB position(10:18)是指從第10個字元開始截止到第18個字元作為ENAME列的列值。position的寫法也很靈活，要實現上述功能還可以換成下列幾種形式：
①position(*+2:18):直接指定數值的方式叫作絕對偏移量，如果使用*號，專業名詞叫相對偏移量，表示上一個欄位從哪裡結束，這次就從哪裡開始，相對偏移量也可以再做運算，比如Position(*+2:15)就表示從上次結束的位置+2的地方開始。
②position(*) char(9):這種相對偏移量+型別和長度的優勢在於，你只需要為第一列指定開始位置，其他列只需要指定列長度就可以了，實際使用中比較省事。

sqlldr執行如上程式碼，並查詢結果如下：

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

--查詢結果
SQL> select * from bonus;
ENAME      JOB      SAL       COMM
---------- --------- ---------- ----------
SMITH CLEAK     3904
ALLEN SALESMAN 2891
WARD    SALESMAN 3128
KING    PRESIDENT 2523

5.資料檔案中的列比要匯入的表中的列少
在前面幾個例子中，數檔案中的列比表中的列要少的環境中演示的，這說明列少不怕，關鍵是看控制檔案中的配置。但是如果缺少的列必須賦值又怎麼辦呢？只需稍改下控制檔案即可，直接指定COMM列，並賦初始值0(這裡仍然引用ldr_case3.dat中的資料）：

--控制檔案
[oracle@cancer ~]$ cat ldr_case4.ctl
LOAD DATA
INFILE ldr_case3.dat
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:5),
JOB position(10:18),
SAL position(23:26),
COMM "0"
)

檢視演示結果

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

--檢視結果
SQL> select * from bonus;
ENAME   JOB      SAL       COMM
------- --------- ---------- ----------
SMITH   CLEAK     3904 0
ALLEN   SALESMAN 2891 0
WARD    SALESMAN 3128 0
KING PRESIDENT 2523 0

COMM的值也可以根據其他列的值而定，修改控制檔案如下

--控制檔案
[oracle@cancer ~]$ cat ldr_case5.ctl
LOAD DATA
INFILE ldr_case3.dat
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:5),
JOB position(10:18),
SAL position(23:26),
COMM "substr(:SAL,1,1)"
)

sqlldr執行上述程式碼,結果如下，很明顯發現COMM的值是根據SAL的值的第1位數字獲得

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

--執行結果
SQL> select * from bonus;
ENAME   JOB       SAL     COMM
------- --------- ------ ----------
SMITH   CLEAK     3904   3
ALLEN   SALESMAN 2891   2
WARD    SALESMAN 3128   3
KING    PRESIDENT 2523   2

這裡COMM列的值根據SAL列值而定，我們透過一個SQL中的函式substr取SAL值的第一列，賦予COMM列，當然這只是一個示例，DBA可以根據實際需求進行適當的修改，透過SQL中的函式可以實現很多很有意思的轉換，也許能夠為你省下很大力氣，而且如果現有函式無法實現，甚至可以透過PL/SQL編寫自定義的函式，然後在sqlldr的控制檔案中呼叫，呼叫方式與系統自帶函式方式完全相同，這樣就可以根據需求對要載入的列做審靈活的處理。

6.資料檔案中的列比要匯入的表中列多
如果資料檔案中的列比要匯入的表中的列少，處理的時候可能麻煩些，多了反倒更簡單，針對不同情況，一般有以下兩種處理方式：
方式一：修改資料檔案，將多餘的資料刪除，不過以這種方式處理，小資料量時還可行，一旦資料檔案較大，幾百甚至上千兆，修改資料檔案耗時耗力。
方式二：使用sqlldr中控制檔案FILLER來排除不需要的列
1）演示資料檔案如下

--資料檔案
[oracle@cancer ~]$ cat ldr_case6.dat
SMITH    7369   CLERK      1020   20
ALLEN    7499   SALESMAN   1930   30
WARD     7521   SALESMAN   1580   30
JONES    7566   MANAGER    3195   20
MARTIN   7654   SALESMAN   1580   30
BLAKE    7698   MANAGER    3180   30
CLARK    7782   MANAGER    2172   10
SCOTT    7788   ANALYST    3220   20
KING     7839   PRESIDENT 4722   10
TURNER   7844   SALESMAN   1830   30
ADAMS    7876   CLERK      1320   20
JAMES    7900   CLERK      1280   30
FORD     7902   ANALYST    3220   20
MILLER   7934   CLERK      1022   10

此時我們的需求希望我們匯入第1、3、4列而跳過2、5列，建立控制檔案如下

--控制檔案
[oracle@cancer ~]$ cat ldr_case6.ctl
LOAD DATA
INFILE ldr_case6.dat
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:6),
TCOL FILLER position(10:13),
JOB position(17:25),
SAL position(28:31)
)

sqlldr的控制檔案中對列定義時支援FILLER關鍵字，可以用來指定過濾列，在上述控制檔案中，我們就使用該關鍵字來過濾列，相當於第10到第13列之間的資料不匯入。
事實上由於此處為定長字串，我們在控制檔案中指定的position引數，己經限定了讀取的內容，你甚至可以刪除控制檔案中TCOL FILLER position (10:13)那行。
執行sqlldr命令：

--sqlldr命令
[oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case6.ctl
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

14 rows selected.

2）如果資料檔案中字串不是定長格式，而是透過分隔符來處理的，那控制檔案中就需要注意，如資料檔案如下：

--資料檔案
[oracle@cancer ~]$ cat ldr_case7.dat
SMITH,7369,CLERK,1020,20
ALLEN,7499,SALESMAN,1930,30
WARD,7521,SALESMAN,1580,30
JONES,7566,MANAGER,3195,20
MARTIN,7654,SALESMAN,1580,30
BLAKE,7698,MANAGER,3180,30
CLARK,7782,MANAGER,2172,10
SCOTT,7788,ANALYST,3220,20
KING,7839,PRESIDENT,4722,10
TURNER,7844,SALESMAN,1830,30
ADAMS,7876,CLERK,1320,20
JAMES,7900,CLERK,1280,30
FORD,7902,ANALYST,3220,20
MILLER,7934,CLERK,1022,10

此時建立控制檔案時，控制檔案中就必須制定FILLER，不然列中的值可能不對應，建立控制檔案如下

--控制檔案
[oracle@cancer ~]$ vim ldr_case7.ctl
LOAD DATA
INFILE ldr_case7.dat
TRUNCATE INTO TABLE BONUS
FIELDS TERMINATED BY ","
(ENAME,XCOL FILLER,JOB,SAL)

執行sqlldr命令，並檢視結果

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

--檢視結果
[oracle@cancer ~]$ sqlplus scott/tiger;
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

14 rows selected.

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

SQL*Loader的使用總結(二)

相關文章