Oracle 的SQL*LOADER可以将外部数据加载到数据库表中。
sqlldr userid = USERNAME / PASSWORD @ IP/数据库名 control = D:/data.ctl bad = D:/ bad.bad log =D:/log.log
1.1 SQL*LOADER的基本特点:
- 1)能装入不同数据类型文件及多个数据文件的数据
- 2)可装入固定格式,自由定界以及可度长格式的数据
- 3)可以装入二进制,压缩十进制数据
- 4)一次可对多个表装入数据
- 5)连接多个物理记录装到一个记录中
- 6)对一单记录分解再装入到表中
- 7)可以用 数对制定列生成唯一的KEY
- 8)可对磁盘或 磁带数据文件装入制表中
- 9)提供装入错误报告
- 10)可以将文件中的整型字符串,自动转成压缩十进制并装入列表中。
- 11)可以用filler关键字,不装载某列:field2 FILLER,
- 12)可以用 "SKIP n" 关键字来指定导入时可以跳过多少行数据
- 13)可以将多行当着一行记录
1.2控制文件(*.ctl)
控制文件是用一种语言写的文本文件,这个文本文件能被SQL*LOADER识别。SQL*LOADER根据控制文件可以找到需要加载的数据。并且分析和解释这些数据。控制文件由三个部分组成:
- 全局选件,行,跳过的记录数等;
- INFILE子句指定的输入数据;
- 数据特性说明。
1.3输入文件
对于 SQL*Loader, 除控制文件外就是输入数据。SQL*Loader可从一个或多个指定的文件中读出数据。如果数据是在控制文件中指定,就要在控制文件中写成 INFILE * 格式。当数据固定的格式(长度一样)时且是在文件中得到时,要用INFILE "fix n"
load data
infile 'example.dat' "fix 11"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1 char(5),
col2 char(7))
example.dat:
001, cd, 0002,fghi,
00003,lmn,
1, "pqrs",
0005,uvwx,
当数据是可变格式(长度不一样)时且是在文件中得到时,要用INFILE "var n"。如:
load data
infile 'example.dat' "var 3"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1 char(5),
col2 char(7))
example.dat:
009hello,cd,010world,im,
012my,name is,
1.4坏文件
坏文件包含那些被SQL*Loader拒绝的记录。被拒绝的记录可能是不符合要求的记录。
坏文件的名字由 SQL*Loader命令的BADFILE 参数来给定。
1.5日志文件及日志信息
当SQL*Loader 开始执行后,它就自动建立 日志文件。日志文件包含有加载的总结,加载中的错误信息等。
控制文件语法
控制文件的格式如下:
OPTIONS ( { [SKIP=integer] [ LOAD = integer ]
[ERRORS = integer] [ROWS=integer]
[BINDSIZE=integer] [SILENT=(ALL|FEEDBACK|ERROR|DISCARD) ] )
LOAD[DATA]
[ { INFILE | INDDN } {file | * }
[STREAM | RECORD | FIXED length [BLOCKSIZE size]|
VARIABLE [length] ]
[ { BADFILE | BADDN } file ]
{DISCARDS | DISCARDMAX} integr ]
[ {INDDN | INFILE} . . . ]
[ APPEND | REPLACE | INSERT ]
[RECLENT integer]
[ { CONCATENATE integer |
CONTINUEIF { [THIS | NEXT] (start[: end])LAST }
Operator { 'string' | X 'hex' } } ]
INTO TABLE [user.]table
[APPEND | REPLACE|INSERT]
[WHEN condition [AND condition]...]
[FIELDS [delimiter] ]
(
column {
RECNUM | CONSTANT value |
SEQUENCE ( { integer | MAX |COUNT} [, increment] ) |
[POSITION ( { start [end] | * [ + integer] }
) ]
datatype
[TERMINATED [ BY ] {WHITESPACE| [X] 'character' } ]
[ [OPTIONALLY] ENCLOSE[BY] [X]'charcter']
[NULLIF condition ]
[DEFAULTIF condotion]
}
[ ,...]
)
[INTO TABLE...]
[BEGINDATA]
1)要加载的数据文件:
- INFILE 和INDDN是同义词,它们后面都是要加载的数据文件。如果用 * 则表示数据就在控制文件内。在INFILE 后可以跟几个文件。
- STRAM 表示一次读一个字节的数据。新行代表新物理记录(逻辑记录可由几个物理记录组成)。
- RECORD 使用宿主操作系统文件及记录管理系统。如果数据在控制文件中则使用这种方法。
- FIXED length 要读的记录长度为length字节,
- VARIABLE 被读的记录中前两个字节包含的长度,length 记录可能的长度。缺伤为8k字节。
- BADFILE和BADDN同义。Oracle 不能加载数据到数据库的那些记录。
- DISCARDFILE和DISCARDDN是同义词。记录没有通过的数据。
- DISCARDS和DISCARDMAX是同义词。Integer 为最大放弃的文件个数。
2)加载的方法:
- APPEND 给表添加行。
- INSERT 给空表增加行(如果表中有记录则退出)。
- REPLACE 先清空表再加载数据。
- RECLEN 用于两种情况,1)SQLLDR不能自动计算记录长度,2)或用户想看坏文件的完整记录时。对于后一种,Oracle只能按常规把坏记录部分写到错误的地方。如果看整条记录,则可以将整条记录写到坏文件中。
3)指定最大的记录长度:
- CONCATENATE 允许用户设定一个整数,表示要组合逻辑记录的数目。
4)建立逻辑记录:
- THIS 检查当前记录条件,如果为真则连接下一个记录。
- NEXT 检查下一个记录条件。如果为真,则连接下一个记录到当前记录来。
- Start: end 表示要检查在THIS或NEXT字串是否存在继续串的列,以确定是否进行连接。如:continueif next(1-3)='WAG' 或continueif next(1-3)=X'0d03if'
5)指定要加载的表:
- INTO TABLE 要加的表名。
- WHEN 和select WHERE类似。用来检查记录的情况,如:when(3-5)='SSM' and (22)='*"
6)介绍并括起记录中的字段:
- FIELDS给出记录中字段的分隔符,FIELDS格式为:
- FIELDS [TERMIALED [BY] {WHITESPACE | [X] 'charcter'} ]
- [ [ OPTIONALLY] ENCLOSE [BY] [X]'charcter' ]
- TERMINATED 读完前一个字段即开始读下一个字段直到介绍。
- WHITESPACE 是指结束符是空格的意思。包括空格、Tab、换行符、换页符及回车符。如果是要判断但字符,可以用单引号括起,如X'1B'等。
- OPTIONALLY ENCLOSED 表示数据应由特殊字符括起来。也可以括在TERMINATED字符内。使用OPTIONALLY要同时用TERMINLATED。
- ENCLOSED 指两个分界符内的数据。如果同时用 ENCLOSED和TERMINAED ,则它们的顺序决定计算的顺序。
7)定义列:
column 是表列名。列的取值可以是:
- BECHUM 表示逻辑记录数。第一个记录为1,第2个记录为2。
- CONSTANT 表示赋予常数。
- SEQUENCE 表示序列可以从任意序号开始,格式为:
- SEQUENCE ( { integer | MAX |COUNT} [,increment]
- POSITION 给出列在逻辑记录中的位置。可以是绝对的,或相对前一列的值。格式为:POSITION ( {start[end] | * [+integer] } )
- Start 开始位置
- * 表示前字段之后立刻开始。
- + 从前列开始向后条的位置数。
8)定义数据类型:
可以定义14种数据类型:
- CHAR
- DATE
- DECIMAL EXTERNAL
- DECIMAL
- DOUBLE
- FLOAT
- FLOAT EXTERNAL
- GRAPHIC EXTERNAL
- INTEGER
- INTEGER EXTERNAL
- SMALLINT
- VARCHAR
- VARGRAPHIC
1.字符类型数据
CHAR[ (length)] [delimiter]
length缺省为 1.
2.日期类型数据
DATE [ ( length)]['date_format' [delimiter]
使用to_date函数来限制。
3.字符格式中的十进制
DECIMAL EXTERNAL [(length)] [delimiter]
用于常规格式的十进制数(不是二进制=> 一个位等于一个bit)。
4.压缩十进制格式数据
DECIMAL (digtial [,divcision])
5.双精度符点二进制
DOUBLE
6.普通符点二进制
FLOAT
7.字符格式符点数
FLOAT EXTERNAL [ (length) ] [delimiter]
8.双字节字符串数据
GRAPHIC [ (legth)]
9.双字节字符串数据
GRAPHIC EXTERNAL[ (legth)]
10.常规全字二进制整数
INTEGER
11.字符格式整数
INTEGER EXTERNAL
12.常规全字二进制数据
SMALLINT
13.可变长度字符串
VARCHAR
14.可变双字节字符串数据
VARGRAPHIC
2.2写控制文件CTL
- 各数据文件的文件名;
- 各数据文件格式;
- 各数据文件里各数据记录字段的属性;
- 接受数据的ORACLE表列的属性;
- 数据定义;
- 其它
数据文件的要求