[Oracle] SQL*Loader 詳細使用教程（3）

集微筆記 2014-02-13

展開全文

控制文件是SQL*Loader里最重要的文件，它是一個文本文件，用來定義數(shù)據(jù)文件的位置、數(shù)據(jù)的格式、以及配置數(shù)據(jù)加載過程的行為，在sqlldr中以control參數(shù)指定控制文件。

在控制文件里配置命令行參數(shù) （OPTIONS）

在上一篇中我們講過命令行參數(shù)可以配置在三個地方，其中一個就是可以使用控制文件的OPTIONS字句（這也是最常用的方式），如下所示：

OPTIONS (DIRECT=true, SILENT=(ERRORS, FEEDBACK) )

更多的命令行參數(shù)，請見上一篇。

配置數(shù)據(jù)文件（INFILE）

使用INFILE可以配置一個或多個數(shù)據(jù)文件，其語法如下：

如果數(shù)據(jù)包含在控制文件本身，則用*表示，如：

INFILE  *

在控制文件的末尾，以BEGINDATA開頭，后面接數(shù)據(jù)：

BEGINDATA 
data

如果你有多個數(shù)據(jù)文件怎么辦？很簡單，只要多個INFILE即可，而且還可以為每個INFILE指定各自的錯誤文件和丟棄文件等，如下所示：

INFILE  mydat1.dat  BADFILE  mydat1.bad  DISCARDFILE mydat1.dis 
INFILE  mydat2.dat 
INFILE  mydat3.dat  DISCARDFILE  mydat3.dis 
INFILE  mydat4.dat  DISCARDMAX  10 0

字符集

如果數(shù)據(jù)文件的字符集和數(shù)據(jù)庫的字符集不一樣，SQL*Loader會自動把數(shù)據(jù)文件的字符集轉(zhuǎn)換成數(shù)據(jù)庫的字符集，當(dāng)然前提條件是數(shù)據(jù)庫的字符集是數(shù)據(jù)文件的字符集的超集。

數(shù)據(jù)庫的字符集可以通過以下SQL語句查詢：

[sql] view plain copy print ?

select * from nls_database_parameters;

數(shù)據(jù)文件的字符集可以通過控制文件中的CHARACTERSET參數(shù)配置，其語法如下：

CHARACTERSET char_set_name

如果沒有設(shè)置CHARACTERSET參數(shù)，數(shù)據(jù)文件的字符集由操作系統(tǒng)的NLS_LANG設(shè)置。
受到字符集影響的數(shù)據(jù)類型有：CHAR,VARCHAR,numeric EXTERNAL, datetime, interval

還有一種字符集要特別注意，就是控制文件本身的字符集（只能由NLS_LANG設(shè)置），如果控制文件的字符集和數(shù)據(jù)文件的不一樣，會先轉(zhuǎn)換成數(shù)據(jù)文件的字符集，但這樣很容易出錯（特別是分隔符），因此，實際使用中為了方便，一般把NLS_LANG，CHARACTERSET（如果有的話）設(shè)成和數(shù)據(jù)庫字符集一樣。

數(shù)據(jù)加載方式（INSERT, APPEND, REPLACE, TRUNCATE）

默認情況下，SQL*Loader是以INSERT方式加載數(shù)據(jù)，目標(biāo)表必須是空表，否則會報錯；

如果目標(biāo)表為非空表，則必須指定如下的三種方式之一：

1. APPEND：添加數(shù)據(jù)至源數(shù)據(jù)的后面

2. REPLACE：在添加數(shù)據(jù)之前，先執(zhí)行DELETE FROM TABLE把表清空，注意：這里的REPLACE是表級別的替代，而不是行級別

3. TRUNCATE：在添加數(shù)據(jù)之前，先執(zhí)行TRUNCATE TABLE table_name REUSE STORAGE

從上面我們可以知道，SQL*Loader不支持對已存在的行更新，如果你想更新已存在的行，則必須先把數(shù)據(jù)加載至一個空的中間表，然后在原表和中間表進行關(guān)聯(lián)update

如果你想把記錄插入到多個表中，可以使用多個INTO TABLE，舉個例子：

1 50   Manufacturing       — DEPT record 
2 1119 Smith      50       — EMP record 
2 1120 Snyder     50 
1 60   Shipping 
2 1121 Stevens    60

數(shù)據(jù)文件如上所示，根據(jù)第一列的值插入到不同的表中：

INTO TABLE dept 
   WHEN recid = 1 
   (recid  FILLER POSITION(1:1)  INTEGER EXTERNAL,
    deptno POSITION(3:4)  INTEGER EXTERNAL, 
    dname  POSITION(8:21) CHAR) 
INTO TABLE emp 
   WHEN recid <> 1 
   (recid  FILLER POSITION(1:1)   INTEGER EXTERNAL,
    empno  POSITION(3:6)   INTEGER EXTERNAL, 
    ename  POSITION(8:17)  CHAR, 
    deptno POSITION(19:20) INTEGER EXTERNAL)

數(shù)據(jù)過濾(WHEN)

我們可以用WHEN字句篩選需要的記錄，不符合WHEN條件的記錄將被丟棄，WEHN的語法如下：

下面是一個例子：

WHEN (deptno = '10') AND (job = 'SALES')

設(shè)置字段分隔符（WHEN）

如果所有的字段分隔符都一樣，可以設(shè)置一個默認的分隔符，其語法如下：

下面是一個例子：

FIELDS TERMINATED BY WHITESPACE

無法匹配的字段用NULL填充（TRAILING NULLCOLS）

如果數(shù)據(jù)文件中記錄的列少于目標(biāo)表的列，最好用NULL代替，否則會報錯，如下所示：

INTO TABLE dept 
    TRAILING NULLCOLS 
( deptno CHAR TERMINATED BY " ", 
  dname  CHAR TERMINATED BY WHITESPACE, 
  loc    CHAR TERMINATED BY WHITESPACE 
)

記錄格式如下：