往Oracle数据库导入数据的两种方法

在升级项目中,经常需要对数据进行迁移,我这次主要操作的是将数据从Access迁移到Oracle中.如何将数据导入Oracle数据库中,我总结了两种方法,供参考.

1.SQL loader

1.1 主要特征

SQL loader是Oracle数据库自带的,通过命令行操作的数据加载工具,主要有以下特征:

在同一加载会话中从多个数据文件加载数据。
在同一加载会话期间将数据放入多个表中。
指定数据的字符集。
有选择地加载数据。
在指定的列中生成唯一的顺序键值。
生成详细的错误报告，这大大有助于故障排除。
使用常规或直接路径加载。尽管常规路径加载非常灵活，但是直接路径加载提供了卓越的加载性能。

1.2 结构概览

以下是SQL loader的概览.

input datafiles:数据文件,提供需要导入Oracle的数据
loader control file :控制文件,对数据加载的动作进行描述
log file:log文件,记录log
bad file: 坏文件,记录由于错误而被拒绝的行,这些错误可能包括错误的数据类型或参照完整性约束
discard file:丢弃文件,记录由于SQL * Loader控制文件中的语句而被过滤掉而被丢弃的行。

1.3 典型例子

1    -- This is a sample control file2    LOAD DATA3    INFILE \'sample.dat\'4    BADFILE \'sample.bad\'5    DISCARDFILE \'sample.dsc\'6    APPEND7    INTO TABLE emp8    WHEN (57) = \'.\'9    TRAILING NULLCOLS10  (hiredate SYSDATE,11    deptno POSITION(1:2)  INTEGER EXTERNAL(2)NULLIF deptno=BLANKS,12     job    POSITION(7:14)  CHAR  TERMINATED BY WHITESPACENULLIF job=BLANKS  \"UPPER(:job)\",mgr    POSITION(28:31) INTEGER EXTERNALTERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,ename  POSITION(34:41) CHARTERMINATED BY WHITESPACE  \"UPPER(:ename)\",empno  POSITION(45) INTEGER EXTERNALTERMINATED BY WHITESPACE,sal    POSITION(51) CHAR  TERMINATED BY WHITESPACE\"TO_NUMBER(:sal,\'$99,999.99\')\",13     comm   INTEGER EXTERNAL  ENCLOSED BY \'(\' AND \'%\'\":comm * 100\")

以下为说明性注释:

控制文件中写注释的方法;
LOAD DATA语句告诉SQL * Loader这是新数据加载的开始;
INFILE语句指定包含要加载的数据的数据文件的名称;
BADFILE语句指定放置拒绝记录的文件的名称;
DISCARDFILE语句指定放置废弃记录的文件的名称;
APPEND语句是将数据加载到非空表中时可以使用的选项之一,可以根据需要用REPLACE,TRUNCATE替代。使用INSERT语句是将数据加载到空表中的选项;
INTO TABLE子句允许标识表，字段和数据类型;
WHEN子句指定一个或多个字段条件;
TRAILING NULLCOLS子句告诉SQL * Loader将记录中不存在的相对定位的列视为空列;
10.SYSDATE将该列设置为当前系统日期 ;
11.POSITION指定数据字段的位置;NTEGER EXTERNAL是该字段的数据类型;NULLIF子句是可用于指定字段条件的子句之一;
12.TERMINATED BY WHITESPACE子句是可以为字段指定的分隔符之一;
13.ENCLOSED BY子句是另一个可能的字段定界符

2.PLSQL developer

2.1 text importer

通过这个工具工具可以将txt文件导入数据库,性能较好.
其中有几个我踩过的坑或心得:

点击“导入”按钮之后,出现“没有右括号”的报错.Oracle数据库会对数据进行一定的处理.我原来的数据是字符串类型的,Oracle会在SQL function中,使用to_date()函数对用于表示日期的字符串进行处理,这里会错误地多加引号,导致报错.由于我仍然要使用字符串类型的数据,所以我把SQL function清空了,数据就正常导入了.
准备的txt文件中有换行符,导致Oracle不能正确识别对应行.将TXT文件放进notepa++中,首先调整notepa++的设置,是换行符LF得以表示;然后使用转义字符进行替换.
在这个画面上,可以将txt文件做成一份SQL脚本.

2.2 import table

这个工具可以读SQL脚本导入数据,但性能较差.

以上只是提供了思路,具体使用方法,请参见互联网.