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

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)"
)
   

 

 

 

PLSQL Developer工具载入数据

 PLSQL Developer提供一些数据载入的工具，操作比较简单，可以定义数据映射模板，并且可重复使用。

Text Importer

载入数据

菜单栏à Tools à Text Importer

 

左上角的图标表示：

打开一个数据文件，可支持 txt, csv, tsv, prn文件

通过粘贴数据，可以从Excel中复制数据，点击这个按钮粘贴直接载入数据

新建一个导入

打开一个数据模板定义

打开一个文件或复制数据进入界面后，可以定义相关参数和模板映射，在第一个tab页定义参数：

 在第二个tab页定义数据映射：

 选择表的schema和表名，clear table表示是否在载入数据前清空表，然后定义数据映射，映射时需定义数据类型，和处理数据的单值函数，例如trim()。准备载入的数据可以在result preview预览。

最后点击左下角的Import就可以载入。

映射模板

相同的数据映射关系，可以保存模板，下次只要直接打开就可以重复使用映射关系和参数了。

 

ODBC Importer

 

ODBC Importer功能类似Text Importer，但是ODBC Importer可以解析二进制文件，支持dBASE file, Excel Files和MS Access Database

载入数据

 操作和Text Importer类似，在第一个tab页加载数据。

 路径：菜单栏à Tools à ODBC Importer

 选择载入类型之后，在user name和password处填的是要载入的到的数据库的用户名和密码，点击connect, 在弹出的对话框里选择相应文件，Excel file类型弹出的如下：

 选择文件后数据会加载到界面：

 第二个tab页的操作和Text Import一致，也可以保存和使用模板，并且模板在ODBC Importer和Text Importer之间是可以通用的。