当前位置: 首页 > 图文教程 > 数据库 > DB2 > Oracle数据库导入外部数据如何实现

DB2
在Linux虚拟机上安装 DB2 9
DB2 9励精图强 Viper 2续写传奇
DB2返回SQLCODE -818 错误
DB2未来版“Viper 2” ：为IT敏捷加速
解析：DB2 无限活动日志策略的实用技巧
SQL Server和MySQL的安全性分析
DB2数据库归档日志的管理方案
实例解析IBM DB2的数据复制、迁移方法
备份恢复DB2数据库步骤
DB2 9部署定制的安全性插件
DB2 Web 服务提供者的安全性
DB2无限活动日志策略从介绍到实用技巧
实现对 DB2 UDB 的低层访问控制
DB2数据库安全涉及的问题
关于DB2 数据库授权的研究
DB2 数据库身份验证基础
监控 DB2 活动之捕获事件监控数据
使用重定向恢复克隆 DB2 数据库
监控 DB2 活动之捕获快照数据
DB2 数据库特权授予的方法和技巧

No. « 2 3 4 5 »

技术文章搜索

关键字

DB2 中的 Oracle数据库导入外部数据如何实现

出处:互联网   整理: 软晨网（RuanChen.com）   发布: 2009-09-30   浏览: 727 ::
收藏到网摘: n/a

SQL*Loader是Oracle数据库导入外部数据的一个工具.它和DB2的Load工具相似,但有更多的选择，它支持变化的加载模式，可选的加载及多表加载.

　　如何使用 SQL*Loader 工具

　　我们可以用Oracle的sqlldr工具来导入数据。例如:

　　sqlldr scott/tiger control=loader.ctl

　　控制文件(loader.ctl) 将加载一个外部数据文件(含分隔符). loader.ctl如下:


　　load data
　　infile 'c:\data\mydata.csv'
　　into table emp
　　fields terminated by "," optionally enclosed by '"'
　　( empno, empname, sal, deptno )

　　mydata.csv 如下:


　　10001,"Scott Tiger", 1000, 40
　　10002,"Frank Naude", 500, 20

　　下面是一个指定记录长度的示例控制文件。"*" 代表数据文件与此文件同名，即在后面使用BEGINDATA段来标识数据。


　　load data
　　infile *
　　replace
　　into table departments
　　( dept position (02:05) char(4),
　　deptname position (08:27) char(20)
　　)
　　begindata
　　COSC COMPUTER SCIENCE
　　ENGL ENGLISH LITERATURE
　　MATH MATHEMATICS
　　POLY POLITICAL SCIENCE

　　Unloader这样的工具

　　Oracle 没有提供将数据导出到一个文件的工具。但是，我们可以用SQL*Plus的select 及 format 数据来输出到一个文件：


　　set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
　　spool oradata.txt
　　select col1 || ',' || col2 || ',' || col3
　　from tab1
　　where col2 = 'XYZ';
　　spool off

另外，也可以使用使用 UTL_FILE PL/SQL 包处理:


　　rem Remember to update initSID.ora, utl_file_dir='c:\oradata' parameter
　　declare
　　fp utl_file.file_type;
　　begin
　　fp := utl_file.fopen('c:\oradata','tab1.txt','w');
　　utl_file.putf(fp, '%s, %s\n', 'TextField', 55);
　　utl_file.fclose(fp);
　　end;
　　/

　　当然你也可以使用第三方工具，如SQLWays ,TOAD for Quest等。

　　加载可变长度或指定长度的记录

　　如：


　　LOAD DATA
　　INFILE *
　　INTO TABLE load_delimited_data
　　FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
　　TRAILING NULLCOLS
　　( data1,
　　data2
　　)
　　BEGINDATA
　　11111,AAAAAAAAAA
　　22222,"A,B,C,D,"

　　下面是导入固定位置(固定长度)数据示例：


　　LOAD DATA
　　INFILE *
　　INTO TABLE load_positional_data
　　( data1 POSITION(1:5),
　　data2 POSITION(6:15)
　　)
　　BEGINDATA
　　11111AAAAAAAAAA
　　22222BBBBBBBBBB

　　跳过数据行：

　　可以用 "SKIP n" 关键字来指定导入时可以跳过多少行数据。如：

 


　　LOAD DATA
　　INFILE *
　　INTO TABLE load_positional_data
　　SKIP 5
　　( data1 POSITION(1:5),
　　data2 POSITION(6:15)
　　)
　　BEGINDATA
　　11111AAAAAAAAAA
　　22222BBBBBBBBBB

　　导入数据时修改数据：

　　在导入数据到数据库时，可以修改数据。注意，这仅适合于常规导入，并不适合 direct导入方式.如：


　　LOAD DATA
　　INFILE *
　　INTO TABLE modified_data
　　( rec_no "my_db_sequence.nextval",
　　region CONSTANT '31',
　　time_loaded "to_char(SYSDATE, 'HH24:MI')",
　　data1 POSITION(1:5) ":data1/100",
　　data2 POSITION(6:15) "upper(:data2)",
　　data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"
　　)
　　BEGINDATA
　　11111AAAAAAAAAA991201
　　22222BBBBBBBBBB990112
　　LOAD DATA
　　INFILE 'mail_orders.txt'
　　BADFILE 'bad_orders.txt'
　　APPEND
　　INTO TABLE mailing_list
　　FIELDS TERMINATED BY ","
　　( addr,
　　city,
　　state,
　　zipcode,
　　mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
　　mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
　　mailing_state
　　)

　如:


　　LOAD DATA
　　INFILE *
　　REPLACE
　　INTO TABLE emp
　　WHEN empno != ' '
　　( empno POSITION(1:4) INTEGER EXTERNAL,
　　ename POSITION(6:15) CHAR,
　　deptno POSITION(17:18) CHAR,
　　mgr POSITION(20:23) INTEGER EXTERNAL
　　)
　　INTO TABLE proj
　　WHEN projno != ' '
　　( projno POSITION(25:27) INTEGER EXTERNAL,
　　empno POSITION(1:4) INTEGER EXTERNAL
　　)

　　导入选定的记录：

　　如下例： (01) 代表第一个字符, (30:37) 代表30到37之间的字符:


　　LOAD DATA
　　INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
　　APPEND
　　INTO TABLE my_selective_table
　　WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'
　　(
　　region CONSTANT '31',
　　service_key POSITION(01:11) INTEGER EXTERNAL,
　　call_b_no POSITION(12:29) CHAR
　　)

　　导入时跳过某些字段：

　　可用 POSTION(x:y) 来分隔数据. 在Oracle8i中可以通过指定 FILLER 字段实现。FILLER 字段用来跳过、忽略导入数据文件中的字段.如：


　　LOAD DATA
　　TRUNCATE INTO TABLE T1
　　FIELDS TERMINATED BY ','
　　( field1,
　　field2 FILLER,
　　field3
　　)

　导入多行记录：

　　可以使用下面两个选项之一来实现将多行数据导入为一个记录:


　　CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.
　　CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.

　　SQL*Loader 数据的提交：

　　一般情况下是在导入数据文件数据后提交的。

　　也可以通过指定 ROWS= 参数来指定每次提交记录数。

　　提高 SQL*Loader 的性能：

　　2) 可以添加 DIRECT=TRUE来提高导入数据的性能。当然，在很多情况下，不能使用此参数。

　　3) 通过指定 UNRECOVERABLE选项，可以关闭数据库的日志。这个选项只能和 direct 一起使用。

　　4) 可以同时运行多个导入任务.

　　常规导入与direct导入方式的区别：

　　常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE)，而直接将数据导入到数据文件中。

　　导入数据时修改数据：

　　在导入数据到数据库时，可以修改数据。注意，这仅适合于常规导入，并不适合 direct导入方式.如：


　　LOAD DATA
　　INFILE *
　　INTO TABLE modified_data
　　( rec_no "my_db_sequence.nextval",
　　region CONSTANT '31',
　　time_loaded "to_char(SYSDATE, 'HH24:MI')",
　　data1 POSITION(1:5) ":data1/100",
　　data2 POSITION(6:15) "upper(:data2)",
　　data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"
　　)
　　BEGINDATA
　　11111AAAAAAAAAA991201
　　22222BBBBBBBBBB990112
　　LOAD DATA
　　INFILE 'mail_orders.txt'
　　BADFILE 'bad_orders.txt'
　　APPEND
　　INTO TABLE mailing_list
　　FIELDS TERMINATED BY ","
　　( addr,
　　city,
　　state,
　　zipcode,
　　mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
　　mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
　　mailing_state
　　)

如:


　　LOAD DATA
　　INFILE *
　　REPLACE
　　INTO TABLE emp
　　WHEN empno != ' '
　　( empno POSITION(1:4) INTEGER EXTERNAL,
　　ename POSITION(6:15) CHAR,
　　deptno POSITION(17:18) CHAR,
　　mgr POSITION(20:23) INTEGER EXTERNAL
　　)
　　INTO TABLE proj
　　WHEN projno != ' '
　　( projno POSITION(25:27) INTEGER EXTERNAL,
　　empno POSITION(1:4) INTEGER EXTERNAL
　　)

　　导入选定的记录：

　　如下例： (01) 代表第一个字符, (30:37) 代表30到37之间的字符:


　　LOAD DATA
　　INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
　　APPEND
　　INTO TABLE my_selective_table
　　WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'
　　(
　　region CONSTANT '31',
　　service_key POSITION(01:11) INTEGER EXTERNAL,
　　call_b_no POSITION(12:29) CHAR
　　)

　　导入时跳过某些字段：

　　可用 POSTION(x:y) 来分隔数据. 在Oracle8i中可以通过指定 FILLER　字段实现。FILLER 字段用来跳过、忽略导入数据文件中的字段.如：


　　LOAD DATA
　　TRUNCATE INTO TABLE T1
　　FIELDS TERMINATED BY ','
　　( field1,
　　field2 FILLER,
　　field3
　　)

　导入多行记录：

　　可以使用下面两个选项之一来实现将多行数据导入为一个记录:


　　CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.
　　CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.

　　SQL*Loader 数据的提交：

　　一般情况下是在导入数据文件数据后提交的。

　　也可以通过指定 ROWS= 参数来指定每次提交记录数。

　　提高 SQL*Loader　的性能：

　　2) 可以添加 DIRECT=TRUE来提高导入数据的性能。当然，在很多情况下，不能使用此参数。

　　3) 通过指定　UNRECOVERABLE选项，可以关闭数据库的日志。这个选项只能和 direct 一起使用。

　　4) 可以同时运行多个导入任务.

　　常规导入与direct导入方式的区别：

　　常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE)，而直接将数据导入到数据文件中。

　　sqlldr使用例子说明

　　先把Excel另存为.csv格式文件，如test.csv，再编写一个insert.ctl

　　用sqlldr进行导入!

　　insert.ctl内容如下：

　　load data　　　　　　　　　　 --1、控制文件标识

　　infile 'test.csv'　　　　　　 --2、要输入的数据文件名为test.csv

　　append into table table_name　　　　 --3、向表table_name中追加记录

　　fields terminated by ','　　 --4、字段终止于','，是一个逗号

　　(field1,

　　field2,

　　field3,

　　...

　　fieldn)-----定义列对应顺序

　　注意括号中field排列顺序要与csv文件中相对应

　　然后就可以执行如下命令：


　　sqlldr user/password control=insert.ctl