| 订阅 | 在线投稿
分享
 
 
当前位置: 王朝网络 >> oracle >> 详细讲解Oracle SQL*Loader的使用方法
 

详细讲解Oracle SQL*Loader的使用方法

2008-08-19 06:50:54 编辑來源:互联网 繁體版 评论
 
 
  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 的性能:

  1) 一个简单而容易忽略的问题是,没有对导入的表使用任何索引和/或约束(主键)。如果这样做,甚至在使用ROWS=参数时,会很明显降低数据库导入性能。

  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的性能:

  1) 一个简单而容易忽略的问题是,没有对导入的表使用任何索引和/或约束(主键)。如果这样做,甚至在使用ROWS=参数时,会很明显降低数据库导入性能。

  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
 
 
 
 
上一篇《Oracle Developer 2000中的一些实用语句》
下一篇《Oracle数据库PL/SQL过程调试的输出方法》
 
 
 
 
 
 
 
 
 
日版宠物情人插曲《Winding Road》歌词

日版宠物情人2017的插曲,很带节奏感,日语的,女生唱的。 最后听见是在第8集的时候女主手割伤了,然后男主用嘴帮她吸了一下,插曲就出来了。 歌手:Def...

兄弟共妻,我成了他们夜里的美食

老钟家的两个儿子很特别,就是跟其他的人不太一样,魔一般的执着。兄弟俩都到了要结婚的年龄了,不管自家老爹怎么磨破嘴皮子,兄弟俩说不娶就不娶,老父母为兄弟两操碎了心...

如何磨出破洞牛仔裤?牛仔裤怎么剪破洞?

把牛仔裤磨出有线的破洞 1、具体工具就是磨脚石,下面垫一个硬物,然后用磨脚石一直磨一直磨,到把那块磨薄了,用手撕开就好了。出来的洞啊很自然的。需要猫须的话调几...

我就是扫描下图得到了敬业福和爱国福

先来看下敬业福和爱国福 今年春节,支付宝再次推出了“五福红包”活动,表示要“把欠大家的敬业福都还给大家”。 今天该活动正式启动,和去年一样,需要收集“五福”...

冰箱异味产生的原因和臭味去除的方法

有时候我们打开冰箱就会闻到一股异味,冰箱里的这种异味是因为一些物质发出的气味的混合体,闻起来让人恶心。 产生这些异味的主要原因有以下几点。 1、很多人有这种习...

 
 
 
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 的性能: 1) 一个简单而容易忽略的问题是,没有对导入的表使用任何索引和/或约束(主键)。如果这样做,甚至在使用ROWS=参数时,会很明显降低数据库导入性能。 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 的性能: 1) 一个简单而容易忽略的问题是,没有对导入的表使用任何索引和/或约束(主键)。如果这样做,甚至在使用ROWS=参数时,会很明显降低数据库导入性能。 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
󰈣󰈤
 
 
 
  免责声明:本文仅代表作者个人观点,与王朝网络无关。王朝网络登载此文出于传递更多信息之目的,并不意味着赞同其观点或证实其描述,其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
我们约会吧
漂亮白皙的模特
好纯好美的女生
靓丽的网络美女
痞子的甘南日记
疑是银河落九天
雪域坝上四——纯美色
冬日恋歌——西城杨柳弄轻柔
 
>>返回首页<<
 为你推荐
 
 
 转载本文
 UBB代码 HTML代码
复制到剪贴板...
 
 热帖排行
 
 
 
 
©2005- 王朝网络 版权所有