posts - 186,comments - 176,trackbacks - 0

Oracle 的SQL*LOADER可以将外部数据加载到数据库表中。下面是SQL*LOADER的基本特点:

1.1  功能
1)能装入不同数据类型文件及多个数据文件的数据
2)可装入固定格式,自由定界以及可度长格式的数据
3)可以装入二进制,压缩十进制数据
4)一次可对多个表装入数据
5)连接多个物理记录装到一个记录中
6)对一单记录分解再装入到表中
7)可以用 数对制定列生成唯一的KEY
8)可对磁盘或 磁带数据文件装入制表中
9)提供装入错误报告
10)可以将文件中的整型字符串,自动转成压缩十进制并装入列表中。

1.2  控制文件
控制文件是用一种语言写的文本文件,这个文本文件能被SQL*LOADER识别。
SQL*LOADER根据控制文件可以找到需要加载的数据。
并且分析和解释这些数据。
控制文件由三个部分组成:

全局选件,行,跳过的记录数等;
INFILE子句指定的输入数据;
数据特性说明。

1.3  输入文件
对于 SQL*Loader, 除控制文件外就是输入数据。SQL*Loader可从一个或多个指定的文件中读出数据。如果 数据是在控制文件中指定,就要在控制文件中写成 INFILE * 格式。当数据固定的格式(长度一样)时且是在文件中得到时,要用INFILE <datafile_name> "fix n"

load data
infile 'example.dat' "fix 11"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1 char(5),
col2 char(7))
example.dat:
001, cd, 0002,fghi,
00003,lmn,
1, "pqrs",
0005,uvwx,

当数据是可变格式(长度不一样)时且是在文件中得到时,要用INFILE <datafile_name> "var n"。如:

load data
infile 'example.dat' "var 3"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1 char(5),
col2 char(7))
example.dat:
009hello,cd,010world,im,
012my,name is,

1.4  坏文件
坏文件包含那些被SQL*Loader拒绝的记录。被拒绝的记录可能是不符合要求的记录。
坏文件的名字由 SQL*Loader命令的BADFILE 参数来给定。

1.5  日志文件及日志信息
当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} integr ]
[ {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)要加载的数据文件:

1. INFILE 和INDDN是同义词,它们后面都是要加载的数据文件。如果用 * 则表示数据就在控制文件内。在INFILE 后可以跟几个文件。
2. STRAM 表示一次读一个字节的数据。新行代表新物理记录(逻辑记录可由几个物理记录组成)。
3. RECORD 使用宿主操作系统文件及记录管理系统。如果数据在控制文件中则使用这种方法。
4. FIXED length 要读的记录长度为length字节,
5. VARIABLE 被读的记录中前两个字节包含的长度,length 记录可能的长度。缺伤为8k字节。
6. BADFILE和BADDN同义。Oracle 不能加载数据到数据库的那些记录。
7. DISCARDFILE和DISCARDDN是同义词。记录没有通过的数据。
8. DISCARDS和DISCARDMAX是同义词。Integer 为最大放弃的文件个数。

2)加载的方法:

1.APPEND 给表添加行。
2.INSERT 给空表增加行(如果表中有记录则退出)。
3.REPLACE 先清空表在加载数据。
4.RECLEN 用于两种情况,1)SQLLDR不能自动计算记录长度,2)或用户想看坏文件的完整记录时。对于后一种,Oracle只能按常规把坏记录部分写到错误的地方。如果看整条记录,则可以将整条记录写到坏文件中。

3)指定最大的记录长度:

1. CONCATENATE 允许用户设定一个整数,表示要组合逻辑记录的数目。

4)建立逻辑记录:

1.THIS 检查当前记录条件,如果为真则连接下一个记录。
2.NEXT 检查下一个记录条件。如果为真,则连接下一个记录到当前记录来。
3.Start: end 表示要检查在THIS或NEXT字串是否存在继续串的列,以确定是否进行连接。如:continueif next(1-3)='WAG' 或continueif next(1-3)=X'0d03if'

5)指定要加载的表:


1.INTO TABLE 要加的表名。
2.WHEN 和select WHERE类似。用来检查记录的情况,如:when(3-5)='SSM' and (22)='*"

6)介绍并括起记录中的字段:

1. FIELDS  给出记录中字段的分隔符,FIELDS格式为:

FIELDS [TERMIALED [BY] {WHITESPACE | [X] 'charcter'} ]
[ [ OPTIONALLY] ENCLOSE [BY] [X]'charcter' ]

TERMINATED 读完前一个字段即开始读下一个字段直到结束。
WHITESPACE 是指结束符是空格的意思。包括空格、Tab、换行符、换页符及回车符。如果是要判断单字符,可以用单引号括起,如X'1B'等。
OPTIONALLY ENCLOSED 表示数据应由特殊字符括起来。也可以括在TERMINATED字符内。使用OPTIONALLY要同时用TERMINLATED。
ENCLOSED 指两个分界符内的数据。如果同时用 ENCLOSED和TERMINAED ,则它们的顺序决定计算的顺序。

7)定义列:

column   是表列名。列的取值可以是:
BECHUM   表示逻辑记录数。第一个记录为1,第2个记录为2。
CONSTANT  表示赋予常数。
SEQUENCE        表示序列可以从任意序号开始,格式为:
SEQUENCE       ( { integer | MAX |COUNT} [,increment]
POSITION  给出列在逻辑记录中的位置。可以是绝对的,或相对前一列的值。
格式为:
POSITION ( {start[end] | * [+integer] } )
Start 开始位置
* 表示前字段之后立刻开始。
+ 从前列开始向后条的位置数。

8)定义数据类型:
可以定义14种数据类型:
CHAR 
DATE
DECIMAL EXTERNAL
DECIMAL
DOUBLE
FLOAT
FLOAT EXTERNAL
GRAPHIC EXTERNAL
INTEGER
INTEGER EXTERNAL
SMALLINT
VARCHAR
VARGRAPHIC

1.字符类型数据
CHAR  [ (length)] [delimiter]
length缺省为 1.

2.日期类型数据
DATE [ ( length)]['date_format' [delimiter]
使用to_date函数来限制。

3.字符格式中的十进制
DECIMAL EXTERNAL [(length)] [delimiter]
用于常规格式的十进制数(不是二进制=> 一个位等于一个bit)。

4.压缩十进制格式数据
DECIMAL (digtial [,precision])

5.双精度符点二进制
DOUBLE

6.普通符点二进制
FLOAT

7.字符格式符点数
FLOAT EXTERNAL [ (length) ] [delimiter]

8.双字节字符串数据
GRAPHIC [ (legth)]

9.双字节字符串数据
GRAPHIC EXTERNAL[ (legth)]

10.常规全字二进制整数
INTEGER

11.字符格式整数
INTEGER EXTERNAL

12.常规全字二进制数据
SMALLINT

13.可变长度字符串
VARCHAR

14.可变双字节字符串数据
VARGRAPHIC


[二]写控制文件CTL

1.1  数据文件的要求: 数据类型的指定
      CHAR    字符型
      INTEGER  EXTERNAL   整型
      DECIMAL  EXTERNAL   浮点型

1.  各数据文件的文件名;
2.  各数据文件格式;
3.  各数据文件里各数据记录字段的属性;
4.  接受数据的ORACLE表列的属性;
5.  数据定义;
6.  其它


2.1  数据文件的内容

可以在OS下的一个文件;或跟在控制文件下的具体数据。数据文件可以是:
1、 二进制与字符格式:LOADER可以把二进制文件读(当成字符读)列表中
2、 固定格式:记录中的数据、数据类型、 数据长度固定。
3、 可变格式:每个记录至少有一个可变长数据字段,一个记录可以是一个连续的字符串。数据段的分界(如姓名、年龄)如用“,”作字段的 分 ;用,"’作数据括号等
4、 LOADER可以使用多个连续字段的物理记录组成一个逻辑记录,记录文件运行情况文件:包括以下内容:
1、 运行日期:软件版本号
2、 全部输入,输出文件名;对命令行的展示信息,补充信息,
3、 对每个装入信息报告:如表名,装入情况;对初始装入, 加截入或更新装入的选择情况,栏信息
4、 数据错误报告:错误码;放弃记录报告
5、 每个装X报告:装入行;装入行数,可能跳过行数;可能拒绝行数;可能放弃行数等
6、 统计概要:使用空间(包大小,长度);读入记录数,装入记录数,跳过记录数;拒绝记录数,放弃记录数;运行时间等。


[三]实际使用 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等。
  
  (1)加载可变长度或指定长度的记录
  
  如:
  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,"
  
  (2)下面是导入固定位置(固定长度)数据示例:
  LOAD DATA
  INFILE *
  INTO TABLE load_positional_data
  ( data1 POSITION(1:5),
  data2 POSITION(6:15)
  )
  BEGINDATA
  11111AAAAAAAAAA
  22222BBBBBBBBBB
  
  (3)跳过数据行:
  
  可以用 "SKIP n" 关键字来指定导入时可以跳过多少行数据。如:
  LOAD DATA
  INFILE *
  INTO TABLE load_positional_data
  SKIP 5
  ( data1 POSITION(1:5),
  data2 POSITION(6:15)
  )
  BEGINDATA
  11111AAAAAAAAAA
  22222BBBBBBBBBB
  
  (4)导入数据时修改数据:
  
  在导入数据到数据库时,可以修改数据。注意,这仅适合于常规导入,并不适合 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
  )
  
  (5)将数据导入多个表:
  
  如:
  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
  )
  
  (6)导入选定的记录:
  
  如下例: (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
  )
  
  (7)导入时跳过某些字段:
  
  可用 POSTION(x:y) 来分隔数据. 在Oracle8i中可以通过指定 FILLER 字段实现。FILLER 字段用来跳过、忽略导入数据文件中的字段.如:
  LOAD DATA
  TRUNCATE INTO TABLE T1
  FIELDS TERMINATED BY ','
  ( field1,
  field2 FILLER,
  field3
  )
  
  (8)导入多行记录:
  
  可以使用下面两个选项之一来实现将多行数据导入为一个记录:
  
  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.

        (9)去掉导入记录的前后空格:
  create_customer_file2()
{
        echo "LOAD DATA
INFILE '$SQLLDR_DIR/$SQLLDR_DATA_DIR/$1'
INTO TABLE zxdbm_ismp.service
APPEND
FIELDS TERMINATED BY \"|\"
TRAILING NULLCOLS
(
serviceid \"trim(:serviceid)\",                     
SPID \"trim(:SPID)\",
AccessNo \"trim(:AccessNo)\",                           
ServiceType \"trim(:ServiceType)\" 
)
" >$SQLLDR_DIR/$SQLLDR_CONTROL_DIR/$1.ctl      
}
trim函数可以去掉文件每列的一前和一后的所有空格,当本列为空时,则导入也为空,当本列为一个或多个空格时,导入也为空。前提是表的该字段允许为NULL。


  

[四]SQL*Loader 数据的提交:
  
  1)一般情况下是在导入数据文件数据后提交的。
  
  2)也可以通过指定 ROWS= 参数来指定每次提交记录数。
  
  提高 SQL*Loader 的性能:
  
  1) 一个简单而容易忽略的问题是,没有对导入的表使用任何索引和/或约束(主键)。如果这样做,甚至在使用ROWS=参数时,会很明显降低数据库导入性能。
  
  2) 可以添加DIRECT=TRUE来提高导入数据的性能。当然,在很多情况下,不能使用此参数。
  
  3) 通过指定UNRECOVERABLE选项,可以关闭数据库的日志。这个选项只能和direct一起使用。
  
  4) 可以同时运行多个导入任务.
  
  常规导入与direct导入方式的区别:
  
  常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻(DIRECT=TRUE),而直接将数据导入到数据文件中。

 


          转:http://hi.baidu.com/ggcong/blog/item/81986e4a610eb02308f7ef02.html

posted on 2008-05-08 10:41 cheng 阅读(3320) 评论(0)  编辑  收藏 所属分类: Oracle

只有注册用户登录后才能发表评论。


网站导航: