[Oracle] SQL*Loader详细使用教程（3）- 控制文件

控制文件是SQL*Loader里最重要的文件，它定义数据文件的位置、数据的格式、以及配置数据加载过程的行为，本节介绍控制文件的配置参数。

在控制文件里配置命令行参数（OPTIONS）

可以使用OPTIONS在控制文件里配置命令行参数，如下所示：

OPTIONS (BINDSIZE=100000, SILENT=(ERRORS, FEEDBACK) )

配置数据文件（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

如果数据包含在控制文件里，可以用*表示，如：

INFILE *

并用BEGINDATA来标识哪些是数据：

BEGINDATA

data

配置坏文件（BADFILE）

以下三种情况将导致记录进入到坏文件：

1. INSERT时发生Oracle错误

2. 数据加载时格式有误

3. 违反约束

配置丢弃文件（DISCARDFILE）

被丢弃的记录是指不符合WHEN定义的记录，你还可以利用DISCARDMAX配置允许丢弃的最大数，如果超过该数目，将停止加载

配置表名（INTO TABLE）及加载方式（INSERT, APPEND, REPLACE, TRUNCATE）

默认情况下，目标表必须是空表（即INSERT方式），否则会报错，如果目标表为非空表，则必须指定如下三种方式的一种：

1. APPEND：添加数据至源数据的后面

2. REPLACE：在添加数据之前，先执行DELETE FROM TABLE把表清空，注意：这里的REPLACE是表级别的替代，而不是行级别

3. TRUNCATE：在添加数据之前，先执行TRUNCATE TABLEtable_nameREUSE STORAGE

如果你想把记录插入到多个表中，可以使用多个INTO TABLE，如：

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

我们可以根据第一列的值的不同分别插入两个不同的表：

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)

筛选记录（WHEN）

用WHEN字句筛选需要的记录，不符合WHEN条件的将被丢弃，如：

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

告诉SQL*Loader如果记录的列少于目标表的列，则用NULL代替，如：

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

记录格式如下：

10 Accounting

对于如上的这条记录，loc列的值为NULL。

数据字段配置

字段属性的设置包括：位置、数据类型、条件、分隔符等。

数据字段的偏移量（POSITION）

指示从哪个字符开始，到哪个字符结束，如：

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

上面的*是值上一个的末尾+偏移量1，也就是从27。

解析字段（FILLER）

解析字段并不真正导入到表中，而是作为逻辑判断的条件。

数据类型

默认的数据类型为CHAR，INTERGER表示二进制数据，INTERGER EXTERNAL表示字符数据

INTERGET(n), SMALLINT, FLOAT, DOUBLE, BYTEINT, ZONED, DECIMAL, VARCHAR, CHAR, Datetime and Interval

数据分隔符

有两种分隔符：

1. TERMINATED BY

2. 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）

下面是一个控制文件的例子：

[plain] 
OPTIONS (DIRECT=true,SKIP_INDEX_MAINTENANCE=true,PARALLEL=true)  
LOAD DATA  
INFILE 'nor.dat'  
BADFILE 'nor.bad'  
DISCARDFILE 'nor.dsc'  
INTO TABLE p95169.DISEASE_EXPERT_RELATION  
APPEND  
WHEN len='3'  
FIELDS TERMINATED BY WHITESPACE  
(  
  len FILLER POSITION(1) CHAR,  
  DISEASEEXPERTUUID EXPRESSION "SYS_GUID()",  
  EXPERTUUID CHAR,  
  DISEASEUUID CHAR,  
  DISEASESORTCODE EXPRESSION "NULL",  
  DISEASENAME CHAR,  
  CREATEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')",  
  MODIFIEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')"  
)  
INTO TABLE p95169.DISEASE_EXPERT_RELATION  
APPEND  
WHEN len='2'  
FIELDS TERMINATED BY WHITESPACE  
(  
  len FILLER POSITION(1) CHAR,  
  DISEASEEXPERTUUID EXPRESSION "SYS_GUID()",  
  EXPERTUUID CHAR,  
  DISEASEUUID EXPRESSION "NULL",  
  DISEASESORTCODE EXPRESSION "NULL",  
  DISEASENAME CHAR,  
  CREATEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')",  
  MODIFIEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')"  
)  
INTO TABLE p95169.DISEASE_EXPERT_RELATION  
APPEND  
WHEN len='1'  
FIELDS TERMINATED BY WHITESPACE  
(  
  len FILLER POSITION(1) CHAR,  
  DISEASEEXPERTUUID EXPRESSION "SYS_GUID()",  
  EXPERTUUID CHAR,  
  DISEASEUUID EXPRESSION "NULL",  
  DISEASESORTCODE EXPRESSION "NULL",  
  DISEASENAME EXPRESSION "NULL",  
  CREATEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')",  
  MODIFIEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')"  
)