Oracle SQL*Loader使用案例（二）

SQL*Loader對不同檔案及格式的處理方法

2.1 Excel檔案

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

2.2要載入的檔案不是以逗號分隔

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

2.3要載入的資料中包含分隔符

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

--控制檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test2.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test2.dat'
TRUNCATE INTO TABLE tb_loader
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' 
(ENAME,JOB,SAL)

--資料檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test2.dat
SMITH,CLEAK,3904
ALLEN,"SALER,M",2891
WARD,"SALER,""S""",3128
KING,PRESIDENT,2523sqlldr

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

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test2.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 14:56:40 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4

--查詢結果  

SCOTT@seiang11g>select * from tb_loader;

ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
SMITH      CLEAK           3904
ALLEN      SALER,M         2891
WARD       SALER,"S"       3128
KING       PRESIDENT       2523

2.4資料檔案沒有分隔符

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

--控制檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test3.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test3.dat'
TRUNCATE INTO TABLE tb_loader
(
 ENAME position(1:5),
 JOB position(10:18),
 SAL position(23:26)
)

--資料檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test3.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@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test3.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:04:13 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4

--查詢結果  

SCOTT@seiang11g>select * from tb_loader;

ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
SMITH      CLEAK           3904
ALLEN      SALESMAN        2891
WARD       SALESMAN        3128
KING       PRESIDENT        252

2.5資料檔案中的列比要匯入的表中的列少

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

--控制檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test4.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test3.dat'
TRUNCATE INTO TABLE tb_loader
(
 ENAME position(1:5),
 JOB position(10:18),
 SAL position(23:26),
 COMM "0"
)

--sqlldr命令  

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test4.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:08:50 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4

--檢視結果  

SCOTT@seiang11g>select * from tb_loader;

ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
SMITH      CLEAK           3904          0
ALLEN      SALESMAN        2891          0
WARD       SALESMAN        3128          0
KING       PRESIDENT        252          0

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

--控制檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test5.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test3.dat'
TRUNCATE INTO TABLE tb_loader
(
 ENAME position(1:5),
 JOB position(10:18),
 SAL position(23:26),
 COMM "substr(:SAL,1,1)"
)

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

--sqlldr命令  

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test5.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:12:00 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4

--執行結果  

SCOTT@seiang11g>select * from tb_loader;

ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
SMITH      CLEAK           3904          3
ALLEN      SALESMAN        2891          2
WARD       SALESMAN        3128          3
KING       PRESIDENT        252          2

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

2.6資料檔案中的列比要匯入的表中列多

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

方式二：使用sqlldr中控制檔案FILLER來排除不需要的列
1）演示資料檔案如下

--資料檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test6.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@wjq SQL*Loader]$ vim wjq_test6.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test6.dat'
TRUNCATE INTO TABLE tb_loader
(
 ENAME position(1:6),
 COL1 FILLER position(10:13),
 JOB position(17:25),
 SAL position(28:31)
)

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

執行sqlldr命令： 

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test6.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:24:36 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 14

--查詢結果  

SCOTT@seiang11g>select * from tb_loader;

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
JAMES      CLERK           1280
FORD       ANALYST         3220
MILLER     CLERK           1022

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

--資料檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test7.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@wjq SQL*Loader]$ vim wjq_test7.ctl
LOAD DATA 
INFILE '/u01/app/oracle/SQL*Loader/wjq_test7.dat'
TRUNCATE INTO TABLE tb_loader
FIELDS TERMINATED BY ","
( 
 ENAME,COL1 FILLER,JOB,SAL
) 

執行sqlldr命令，並檢視結果

--sqlldr命令  
[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test7.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:32:48 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 14

--檢視結果  

SCOTT@seiang11g>select * from tb_loader;

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
JAMES      CLERK           1280
FORD       ANALYST         3220
MILLER     CLERK           1022