SQL*Loader的使用總結(二)

梓沐發表於2016-02-15
SQL
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的引數值),如下所示：
  1. --控制檔案
    [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執行如上程式碼，並查詢結果如下：
  1. --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中處理定長字串也輕而易舉。針對此例，我們將控制檔案修改如下：
  1. --控制檔案
    [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執行如上程式碼，並查詢結果如下：
  1. --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中的資料）：
  1. --控制檔案
    [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"
    )
檢視演示結果
  1. --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的值也可以根據其他列的值而定，修改控制檔案如下
  1. --控制檔案
    [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位數字獲得
  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）演示資料檔案如下
  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列，建立控制檔案如下
  1. --控制檔案
    [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命令：
  1. --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）如果資料檔案中字串不是定長格式，而是透過分隔符來處理的，那控制檔案中就需要注意，如資料檔案如下：
  1. --資料檔案
    [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，不然列中的值可能不對應，建立控制檔案如下
  1. --控制檔案
    [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命令，並檢視結果
  1. --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親自測試透過，並對一些小問題進行了處理


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29812844/viewspace-1988869/，如需轉載，請註明出處，否則將追究法律責任。

相關文章