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

控制文件是SQL*Loader里最重要的文件,它是一个文本文件,用来定义数据文件的位置、数据的格式、以及配置数据加载过程的行为,在sqlldr中以control参数指定控制文件。

 

在控制文件里配置命令行参数 (OPTIONS)

在上一篇中我们讲过命令行参数可以配置在三个地方,其中一个就是可以使用控制文件的OPTIONS字句(这也是最常用的方式),如下所示:

 

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

更多的命令行参数,请见上一篇。

 

配置数据文件(INFILE)

 

使用INFILE可以配置一个或多个数据文件,其语法如下:

如果数据包含在控制文件本身,则用*表示,如:

 

INFILE  *
在控制文件的末尾,以BEGINDATA开头,后面接数据:

 

 

BEGINDATA 
data

如果你有多个数据文件怎么办?很简单,只要多个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

 

字符集

如果数据文件的字符集和数据库的字符集不一样,SQL*Loader会自动把数据文件的字符集转换成数据库的字符集,当然前提条件是数据库的字符集是数据文件的字符集的超集。

数据库的字符集可以通过以下SQL语句查询:
[sql] view plain copy
 
 print?
  1. select * from nls_database_parameters;  
数据文件的字符集可以通过控制文件中的CHARACTERSET参数配置,其语法如下:
CHARACTERSET char_set_name 
如果没有设置CHARACTERSET参数,数据文件的字符集由操作系统的NLS_LANG设置。
受到字符集影响的数据类型有:CHAR,VARCHAR,numeric EXTERNAL, datetime, interval

还有一种字符集要特别注意,就是控制文件本身的字符集(只能由NLS_LANG设置),如果控制文件的字符集和数据文件的不一样,会先转换成数据文件的字符集,但这样很容易出错(特别是分隔符),因此,实际使用中为了方便,一般把NLS_LANG,CHARACTERSET(如果有的话)设成和数据库字符集一样。

 

数据加载方式(INSERT, APPEND, REPLACE, TRUNCATE)

默认情况下,SQL*Loader是以INSERT方式加载数据,目标表必须是空表,否则会报错;

如果目标表为非空表,则必须指定如下的三种方式之一:

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

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

3. TRUNCATE:在添加数据之前,先执行TRUNCATE TABLE table_name REUSE STORAGE 

从上面我们可以知道,SQL*Loader不支持对已存在的行更新,如果你想更新已存在的行,则必须先把数据加载至一个空的中间表,然后在原表和中间表进行关联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 
数据文件如上所示,根据第一列的值插入到不同的表中:
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条件的记录将被丢弃,WEHN的语法如下:

下面是一个例子:
WHEN (deptno = '10') AND (job = 'SALES') 

 

 

 

设置字段分隔符(WHEN)

 

如果所有的字段分隔符都一样,可以设置一个默认的分隔符,其语法如下:


下面是一个例子:

 

FIELDS TERMINATED BY WHITESPACE
 

 

 

无法匹配的字段用NULL填充(TRAILING NULLCOLS)

 

如果数据文件中记录的列少于目标表的列,最好用NULL代替,否则会报错,如下所示:

 

INTO TABLE dept 
    TRAILING NULLCOLS 
( deptno CHAR TERMINATED BY " ", 
  dname  CHAR TERMINATED BY WHITESPACE, 
  loc    CHAR TERMINATED BY WHITESPACE 
) 
记录格式如下:

 

10 Accounting 
对于如上的这条记录,loc列的值为NULL。

 

控制文件示例

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

 

[plain] view plain copy
 
 print?
  1. OPTIONS (DIRECT=true,SKIP_INDEX_MAINTENANCE=true,PARALLEL=true)  
  2. LOAD DATA  
  3. INFILE 'nor.dat'  
  4. BADFILE 'nor.bad'  
  5. DISCARDFILE 'nor.dsc'  
  6. INTO TABLE p95169.DISEASE_EXPERT_RELATION  
  7. APPEND  
  8. WHEN len='3'  
  9. FIELDS TERMINATED BY WHITESPACE  
  10. (  
  11.   len FILLER POSITION(1) CHAR,  
  12.   DISEASEEXPERTUUID EXPRESSION "SYS_GUID()",  
  13.   EXPERTUUID CHAR,  
  14.   DISEASEUUID CHAR,  
  15.   DISEASESORTCODE EXPRESSION "NULL",  
  16.   DISEASENAME CHAR,  
  17.   CREATEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')",  
  18.   MODIFIEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')"  
  19. )  
  20. INTO TABLE p95169.DISEASE_EXPERT_RELATION  
  21. APPEND  
  22. WHEN len='2'  
  23. FIELDS TERMINATED BY WHITESPACE  
  24. (  
  25.   len FILLER POSITION(1) CHAR,  
  26.   DISEASEEXPERTUUID EXPRESSION "SYS_GUID()",  
  27.   EXPERTUUID CHAR,  
  28.   DISEASEUUID EXPRESSION "NULL",  
  29.   DISEASESORTCODE EXPRESSION "NULL",  
  30.   DISEASENAME CHAR,  
  31.   CREATEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')",  
  32.   MODIFIEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')"  
  33. )  
  34. INTO TABLE p95169.DISEASE_EXPERT_RELATION  
  35. APPEND  
  36. WHEN len='1'  
  37. FIELDS TERMINATED BY WHITESPACE  
  38. (  
  39.   len FILLER POSITION(1) CHAR,  
  40.   DISEASEEXPERTUUID EXPRESSION "SYS_GUID()",  
  41.   EXPERTUUID CHAR,  
  42.   DISEASEUUID EXPRESSION "NULL",  
  43.   DISEASESORTCODE EXPRESSION "NULL",  
  44.   DISEASENAME EXPRESSION "NULL",  
  45.   CREATEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')",  
  46.   MODIFIEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')"  
  47. )  

 

 

 

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

 

 

 

 
6
posted @ 2016-07-30 10:30  zfswff  阅读(10450)  评论(0编辑  收藏  举报