[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会自动把数据文件的字符集转换成数据库的字符集,当然前提条件是数据库的字符集是数据文件的字符集的超集。
- select * from nls_database_parameters;
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。
控制文件示例
下面是一个控制文件的例子:
- 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')"
- )
[Oracle] SQL*Loader 详细使用教程(4)- 字段列表
- 顶
- 6
- 踩