Oracle SQL*Loader使用案例（二）

SQL*Loader對不同文件及格式的處理方法

2.1 Excel文件

一般的Excel文件最大行數(shù)不超過65536行，說明數(shù)據(jù)處理量并不大，處理Excel的方式是將其另存為CSV格式文件,然后即可按照正常方式導(dǎo)入即可。

2.2要加載的文件不是以逗號分隔

有兩種方式可以參考：
1)修改數(shù)據(jù)文件，將分隔符替換為逗號。
2)修改控制文件，將FIELDS TERMINATED BY的值修改為實際的分隔符。

2.3要加載的數(shù)據(jù)中包含分隔符

例如,要向scott.tb_loader表插入數(shù)據(jù)提供的數(shù)據(jù)格式如下：
SMITH,CLEAK,3904
ALLEN,"SALER,M",2891
WARD,"SALER,""S""",3128
KING,PRESIDENT,2523
    修改控制文件,注意下列示例代碼中的粗體字符,OPTIONALLY ENCLOSED BY參數(shù)指明定界符為雙引號(CSV格式文件默認(rèn)定界符就是雙引號,你可以根據(jù)實際情況修改OPTIONALLY的參數(shù)值),如下所示：

--控制文件  

[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)

--數(shù)據(jù)文件  

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

運行如上代碼，并查詢結(jié)果如下：

[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

--查詢結(jié)果  

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數(shù)據(jù)文件沒有分隔符

如下的數(shù)據(jù)文件專業(yè)叫做定長字符串，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)
)

--數(shù)據(jù)文件  

[oracle@wjq SQL*Loader]$ vim wjq_test3.dat
SMITH    CLEAK        3904
ALLEN    SALESMAN     2891
WARD     SALESMAN     3128
KING     PRESIDENT    252

position關(guān)鍵字用來指定列的開始和結(jié)束位置，如JOB position(10:18)是指從第10個字符開始截止到第18個字符作為ENAME列的列值。position的寫法也很靈活，要實現(xiàn)上述功能還可以換成下列幾種形式：

①position(*+2:18):直接指定數(shù)值的方式叫作絕對偏移量，如果使用*號，專業(yè)名詞叫相對偏移量，表示上一個字段從哪里結(jié)束，這次就從哪里開始，相對偏移量也 可以再做運算，比如Position(*+2:15)就表示從上次結(jié)束的位置+2的地方開始。

②position(*) char(9):這種相對偏移量+類型和長度的優(yōu)勢在于，你只需要為第一列 指定開始位置，其他列只需要指定列長度就可以了，實際使用中比較省事。

sqlldr運行如上代碼，并查詢結(jié)果如下：

--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

--查詢結(jié)果  

SCOTT@seiang11g>select * from tb_loader;

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

2.5數(shù)據(jù)文件中的列比要導(dǎo)入的表中的列少

在前面幾個例子中，數(shù)文件中的列比表中的列要少的環(huán)境中演示的，這說明列少不怕，關(guān)鍵是看控制文件中的配置。但是如果缺少的列必須賦值又怎么辦呢？只需稍改下控制文件即可，直接指定COMM列，并賦初始值0(這里仍然引用ldr_case3.dat中的數(shù)據(jù)）：

--控制文件  

[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

--查看結(jié)果  

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的值也可以根據(jù)其他列的值而定，修改控制文件如下

--控制文件  

[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執(zhí)行上述代碼,結(jié)果如下，很明顯發(fā)現(xiàn)COMM的值是根據(jù)SAL的值的第1位數(shù)字獲得

--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

--執(zhí)行結(jié)果  

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列的值根據(jù)SAL列值而定，我們通過一個SQL中的函數(shù)substr取SAL值的第一列，賦予COMM列，當(dāng)然這只是一個示例，DBA可以根據(jù)實際需求進(jìn)行適當(dāng)?shù)?修改，通過SQL中的函數(shù)可以實現(xiàn)很多很有意思的轉(zhuǎn)換，也許能夠為你省下很大力氣，而且如果現(xiàn)有函數(shù)無法實現(xiàn)，甚至可以通過PL/SQL編寫自定義的函數(shù)，然后在sqlldr的 控制文件中調(diào)用，調(diào)用方式與系統(tǒng)自帶函數(shù)方式完全相同，這樣就可以根據(jù)需求對要加載 的列做審靈活的處理。

2.6數(shù)據(jù)文件中的列比要導(dǎo)入的表中列多

如果數(shù)據(jù)文件中的列比要導(dǎo)入的表中的列少，處理的時候可能麻煩些，多了反倒更簡單，針對不同情況，一般有以下兩種處理方式：
方式一：修改數(shù)據(jù)文件，將多余的數(shù)據(jù)刪除，不過以這種方式處理，小數(shù)據(jù)量時還可行，一旦數(shù)據(jù)文件較大，幾百甚至上千兆，修改數(shù)據(jù)文件耗時耗力。

方式二：使用sqlldr中控制文件FILLER來排除不需要的列
1）演示數(shù)據(jù)文件如下

--數(shù)據(jù)文件  

[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

此時我們的需求希望我們導(dǎo)入第1、3、4列而跳過2、5列，創(chuàng)建控制文件如下

--控制文件  

[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關(guān)鍵字，可以用來指定過濾列，在上述控制文件中，我們就使用該關(guān)鍵字來過濾列，相當(dāng)于第10到第13列之間的數(shù)據(jù)不導(dǎo)入。
事實上由于此處為定長字串，我們在控制文件中指定的position參數(shù)，己經(jīng)限定了讀取的內(nèi)容，你甚至可以刪除控制文件中TCOL FILLER position (10:13)那行。

執(zhí)行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

--查詢結(jié)果  

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）如果數(shù)據(jù)文件中字符串不是定長格式，而是通過分隔符來處理的，那控制文件中就需要注意，如數(shù)據(jù)文件如下：

--數(shù)據(jù)文件  

[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

此時創(chuàng)建控制文件時，控制文件中就必須制定FILLER，不然列中的值可能不對應(yīng)，創(chuàng)建控制文件如下

--控制文件  

[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
) 

執(zhí)行sqlldr命令，并查看結(jié)果

--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

--查看結(jié)果  

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