[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

序列

设置列的值为唯一序列数字，语法如下：

例子：

id SEQUENCE(1,1)

这里要注意的是：无法使用Oracle数据库里的sequence，这点真的很不方便。

[Oracle] SQL*Loader 详细使用教程（1）- 总览
[Oracle] SQL*Loader 详细使用教程（2）- 命令行参数
[Oracle] SQL*Loader 详细使用教程（3）- 控制文件

[Oracle] SQL*Loader 详细使用教程（4）- 字段列表