[Oracle] SQL*Loader 詳細使用教程（4）- 欄位列表

標籤：

在上一篇中我們介紹了SQL*Loader中最重要的檔案——控制檔案，而本篇要介紹控制檔案中最重要的部分——欄位列表，欄位列表的作用是把資料檔案中的記錄和資料庫中表的列對應起來，下面是欄位列表的一個例子，本篇我們將一一講解它們的意思。

 

...1  (hiredate  SYSDATE,2     deptno  POSITION(1:2)  INTEGER EXTERNAL(2)              NULLIF deptno=BLANKS,3       job   POSITION(7:14)  CHAR  TERMINATED BY WHITESPACE              NULLIF job=BLANKS  "UPPER(:job)",       mgr    POSITION(28:31) INTEGER EXTERNAL               TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,       ename  POSITION(34:41) CHAR               TERMINATED BY WHITESPACE  "UPPER(:ename)",       empno  POSITION(45) INTEGER EXTERNAL               TERMINATED BY WHITESPACE,       sal    POSITION(51) CHAR  TERMINATED BY WHITESPACE              "TO_NUMBER(:sal,‘$99,999.99‘)",4      comm   INTEGER EXTERNAL  ENCLOSED BY ‘(‘ AND ‘%‘              ":comm * 100"    )

 

指定列和欄位的對應關係

 

我們知道SQL*Loader的工作就是把資料檔案裡的記錄載入到資料庫表中，因此一定要有資料檔案的記錄欄位和資料庫表的列的對應關係，因此在控制檔案的欄位列表裡，我們首先得配置這種關係。這裡要注意的是，並不需要表的所有列都出現在欄位列表中，沒有出現的列，SQL*Loader會自動用NULL填充。

但有一種欄位比較特殊，叫解析欄位（以FILTER標識），它不跟表的列匹配，它的主要作用是給WHEN語句提供條件判斷的依據，如下所示：

 

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) 

 

指定位置（POSITION）

我們知道記錄在資料檔案中是以位元組儲存的，如果記錄的每個欄位大小是已知的，那麼我們可以使用POSITION字句指定欄位在記錄中的位元組位置，其文法如下：

下面是一些例子：

 

ename  POSITION (1:20)  CHAR empno  POSITION (22-26) INTEGER EXTERNAL allow  POSITION (*+2)   INTEGER EXTERNAL TERMINATED BY "/"

上例中，1~20位元組對應ename列，22~26位元組對應empno列，*表示從上一個欄位的後一個位元組開始（即27），所以*+2=29，也就是說從第29個位元組開始，直到遇到分隔字元‘/‘為止的所有位元組，都屬於allow列。

 

資料類型

SQL*Loader根據控制檔案裡定義的資料類型讀取資料檔案的欄位，然後把它發給資料庫表裡對應的列，這裡需要注意的是：控制檔案定義的資料類型並不需要和資料庫裡對應的表列一樣，因為SQL*Loader會自動轉換，包括字元集的轉換，當然，你必須得保證它們之間是可以轉換的，否則會報錯。

控制檔案的資料類型分為兩種，分別是可移植的和不可移植的，所謂可移植的資料類型就是和具體平台無關，而不可移植的正好相反。

不可移植的資料類型有：integer(n), smallint, float, double, byteint, zoned,decimal,vargraphic, varchar, varraw, long varraw, 

通常情況下，我都採用可移植的資料類型，所以下面我們重點介紹可移植的資料類型：

CHAR

最常用也是預設的資料類型，其文法如下：

length表示CHAR的最大長度，如果不指定則為256，這裡一定要跟資料庫的CHAR區分開，SQL*Loader的CHAR是個變長的資料類型，有點類似於資料庫的varchar。

Datatime

 

• DATE

• TIME

• TIME WITH TIME ZONE

• TIMESTAMP

• TIMESTAMP WITH TIME ZONE

• TIMESTAMP WITH LOCAL TIME ZONE

Interval

 

 

• INTERVAL YEAR TO MONTH

• INTERVAL DAY TO SECOND

Numeric EXTERNAL

 

以字元形式表示數值型的資料類型，包括(INTEGER EXTERNAL, FLOAT EXTERNAL, DECIMAL EXTERNAL, and ZONED EXTERNAL)，他的特性和CHAR很像，在實際使用中，一般都採用它來代替不可移植的數值型資料類型。

 

分隔字元

CHAR, datetime, interval, numeric EXTERNAL欄位可以使用分隔字元來標識，分隔字元的文法如下：

Terminated by 和 Enclosed by 可以單獨使用，也可以配合使用，以下是一些例子：

 

TERMINATED BY ‘,‘                      a data string, ENCLOSED BY ‘"‘                        "a data string" TERMINATED BY ‘,‘ ENCLOSED BY ‘"‘      "a data string", ENCLOSED BY ‘(‘ AND ‘)‘                (a data string) 

 

 

欄位條件設定（WHEN, NULLIF, DEFAULTIF）

NULLIF：如果符合條件則設為NULL，下面是一個例子：

 

ull_fieldname ... NULLIF column_name=BLANKS 

 

BLANKS參數表示空白的意思（不包括tab），上面的例子表示如果欄位為BLANKS，則欄位為NULL。

 

SQL*Loader產生資料

有時候我們可能希望有些資料在載入資料的過程中自動產生，SQL*Loader提供了一些參數用於產生資料。

CONSTANT

設定列的值為常量，文法如下：

 

column_name CONSTANT  value

運算式

 

設定列的值為運算式的值，文法如下：

 

column_name EXPRESSION "SQL string"

當前日期

 

設定列的值為當前日期，文法如下：

 

column_name SYSDATE

序列

 

設定列的值為唯一序列數字，文法如下：

例子：

 

[plain] view plain copy  print?

1. id SEQUENCE(1,1)  

這裡要注意的是：無法使用Oracle資料庫裡的sequence，這點真的很不方便。

[Oracle] SQL*Loader 詳細使用教程（4）- 欄位列表