将本地数据根据规则导入到数据库表的几种方法

最新推荐文章于 2022-08-16 10:26:24 发布

zhangzhongjie

最新推荐文章于 2022-08-16 10:26:24 发布

阅读量521

点赞数 1

分类专栏： ORACLE 文章标签：数据库操作系统

ORACLE 专栏收录该内容

26 篇文章 0 订阅

订阅专栏

SQL*Loader

SQL*Loader是Oracle数据库导入外部数据的一个系统工具，在Unix下，SQL Loader一般由命令和控制文件组成，命令定义执行的模式，控制文件定义数据映射和可选逻辑。SQL Loader 的特点是能装入不同数据类型文件及多个数据文件以及固定格式，自由定界以及可度长格式的数据，控制文件灵活，速度快（ORACLE自称可以达到每小时处理100GB数据的能力）等。

Sqlldr命令

首先通过查看SQL Loader的帮助信息来了解下sqlldr的使用方法（注：如下图所示，输入sqlldr后回车即可查看其帮助信息）：

Ctl 控制文件

控制文件是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} integer ]

[ {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. 对数据文件的控制

a) INFILE 和INDDN是同义词，它们后面都是要加载的数据文件。如果用 * 则表示数据就在控制文件内。

b) STRAM 表示一次读一个字节的数据。新行代表新物理记录（逻辑记录可由几个物理记录组成）。

c) RECORD 使用宿主操作系统文件及记录管理系统。如果数据在控制文件中则使用这种方法。

d) FIXED length 要读的记录长度为length字节。

e) VARIABLE 被读的记录中前两个字节包含的长度，length 记录可能的长度。缺省为8k字节。

f) DISCARDFILE和DISCARDDN是同义词。记录没有通过的数据。DISCARDS和DISCARDMAX是同义词。Integer 为最大放弃的文件个数。

2. 对加载方法的控制

a) INSERT。缺省方法，该选项要求数据载入前表为空，否则SQL Loader会退出

b) APPEND。在原有记录基础上追加数据，不影响原有数据，原有数据为空也可以。

c) REPLACE。这种方式先删除表的数据，然后载入新纪录，值得注意的是，这种方法将触发表上的触发器

d) TRUNCATE。这种方法在载入前，会不带记录点回滚点的删除数据，不会触发触发器，是不可恢复的。使用这种方法必须保证表没有证完整性约束，且执行用户具有权限。

3. 对导入逻辑的控制

a) THIS 检查当前记录条件，如果为真则连接下一个记录。

b) NEXT 检查下一个记录条件。如果为真，则连接下一个记录到当前记录来。

c) Start: end 表示要检查在THIS或NEXT字串是否存在继续串的列，以确定是否进行连接。如：continueif next(1-3)='WAG' 或continueif next(1-3)=X'0d03if'

4. 对字段识别读取的控制

a) TERMINATED 读完前一个字段即开始读下一个字段直到结束。

b) WHITESPACE 是指结束符是空格的意思。包括空格、Tab、换行符、换页符及回车符。如果是要判断但字符，可以用单引号括起，如X'1B'等。

c) OPTIONALLY ENCLOSED 表示数据应由特殊字符括起来。也可以括在TERMINATED字符内。使用OPTIONALLY要同时用TERMINLATED。

d) ENCLOSED 指两个分界符内的数据。如果同时用 ENCLOSED和TERMINAED ，则它们的顺序决定计算的顺序。

5. 对列定义的控制

column 是表列名。列的取值可以是：

a) BECHUM 表示逻辑记录数。第一个记录为1,第2个记录为2。

b) CONSTANT 表示赋予常数。

c) SEQUENCE表示序列可以从任意序号开始，格式为：SEQUENCE { integer | MAX |COUNT} [,increment]

d) POSITION 给出列在逻辑记录中的位置。可以是绝对的，或相对前一列的值。格式为：

e) POSITION ( {start[end] | * [+integer] } )

f) Start 开始位置

* 表示前字段之后立刻开始。

+ 从前列开始向后条的位置数。

例子：

OPTIONS(SKIP=0)
LOAD DATA
REPLACE
INTO TABLE CUX_TEST_TABLE
FIELDS TERMINATED BY X'09'
TRAILING NULLCOLS(
supp_id                "CUX_TEST_SEQ_S.NEXTVAL",
supp_company_code     CHAR  "TRIM(TO_CHAR(:supp_company_code))",
supp_date              DATE  'YYYYMMDD',
supp_amount           INTEGER   EXTERNAL,
supp_process_status     CONSTANT  "1", 
supp_creation_date      "to_date(SYSDATE)"
)