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

时间：2023-03-08 22:23:37

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

 

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

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

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

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

 

配置数据文件（INFILE）

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

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

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

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语句查询：
  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的语法如下：

[Oracle] SQL*Loader 详细使用教程（3）- 控制文件
下面是一个例子：
WHEN (deptno = '10') AND (job = 'SALES')

 

设置字段分隔符（WHEN）

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

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

下面是一个例子：

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。

控制文件示例

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

  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
踩

相关文章