sqlldr常规应用 (总结篇)

load data                                          
infile *                                              ---指定加载文件   *表示数据就在控制文件后面
into table bonus                            ---指定表名
fields terminated by ","                 ---指定区域分隔符就是逗号
(ename,job,sal)                            ---指定表的列名 
begindata                                       ---仅当infile 指定*时有效

smith,cleak,3904
allen,salesman,2891
ward,salesman,3128
........

把上术文件保存成a.ctl文件，用如下命令就可以导入
sqlldr userid/pass control=a.ctl

一,要加载的文件不是以逗号作分隔
a,修改原始数据，将分隔符替换成逗号
b,修改控制文件,将fields terminated by 的值修改成实际的分隔符

二,要加载的数据中包含分隔符怎么办，如下表kkk.dat
smith,cleak,3904
allen,"salesman,"ak"",2891
ward,"salesman,M",3128
这个时候就要修改控制文件
load data                                          
infile kkk.dat                                   ---指定加载文件   *表示数据就在控制文件后面
into table bonus                            ---指定表名
fields terminated by ","   optinonally enclosed by ' " '               ---指定区域分隔符就是逗号
(ename,job,sal)                            ---指定表的列名

optinonally enclosed by ' " ' 默认就是双引号,如果是其他的,把双引号更改就可以了

三,数据文件没有分隔符怎么办,是定长字符串kkk,dat
smith   cleak           3904
allen    salesman 2891
ward    salesman 3128

修改控制文件
load data                                          
infile kkk.dat                                   
truncate table bonus                       
(
ename position(1:5),                  position(1:5)指的是从第一个字符载止到第五个字符作为ename的值,绝对偏移量
job position(7:15),
sal position(17:20)
)                     

position(*+2:15) ,相对偏移量, 表示从上一个位置结束后偏移二个开始取字符,载止到实际第15个字符
position(*)char(9) 相对偏移量+类型和长度的优势在于,你只需要为第一列指定开始位置，其他的只需要指定列长度就可以了

四，数据文件中的列比要导入的表的列少,且空列又必须赋值
如bonus中多一列comm,并赋初始值0，则可以这么写
load data                                          
infile kkk.dat                                   
truncate table bonus                       
(
ename position(1:5),                 
job position(7:15),
sal position(17:20),
comm '0'
)              
如果要输入特殊值,则可以用函数来解决
load data                                          
infile kkk.dat                                   
truncate table bonus                       
(
ename position(1:5),                
job position(7:15),
sal position(17:20),
comm "substr(:sal,1,1)"      取sal值的第一列，并赋值给comm列
)              
当然也可以用pl/sql编写自定义的函数来赋值

五，数据文件中的列比要导入的表中列多怎么办
a,将数据文件中多的列删除
b,采用过滤，或在控制文件中不录这列数据
load data                                          
infile kkk.dat                                   
truncate table bonus                       
(
ename position(1:5),                 
job position(7:15),
sal position(17:20),
tcol filler position(22:30) ,                       --tcol假设这列不录入,就过滤掉,或这行根本不出现在控制文件中
)       

如果此时数据文件是以分隔符出现的则这样写
load data                                          
infile kkk.dat                                   
truncate table bonus
fields terminated by ","                       
(ename , job ,sal, tcol filler)       

六,多个数据文件导同一张表,条件就是这些数据文件的格式要相同
load data                                          
infile kkk.dat          
infile kkk2.dat
infile kkk3.dat                         
truncate table bonus
fields terminated by ","                       
(ename , job ,sal )

七，同一个数据文件要导入不同的表
bon         smith         cleak        3904
bon         allen           saler         2891
mgr         king            tech           2543
mgr         smm     admd        3032
要把这里面的数据导到b和m表
load data
infile kkk.dat
discardfile ldr_case9.dsc
truncate 
   into table b
    when tab='bon'                                    如果此处判断关键字有多个,只能用and,不能用or
(tab filler position(1:3),
ename position(5:9),
job position(*+1:18),
sal position(*+1)
) 
into table m
when tab='mgr'
(tab filler position(1:3),
ename position(5:9),
job position(*+1:18),
sal position(*+1)
)

八,数据文件前n行不导入
sqlddr scott/scott control=ldr_case1.ctl skip=3   意思前三行不导入,从第四行工始
sqlddr scott/scott control=ldr_case1.ctl skip=3 load=6 前三行不导入，导入接下来的6行

九,要加载的数据中有换行符
手工指定的换行符,数据文件的换行符并不是标准的换行标志,而是用户自定义的一个标识字符或多个字符组成,
10, smith,sales amnager,this is amith,\nhe is a sales manager.
11, allen.w,tech manager,this is allen.w.\nhe is a tech manager.
16, blake,hr manager,this is blake.\nhe is a hr manager.

控制文件的写法
load data 
infile ldr_case11_1.dat
truncate into table manager
fields terminated by ","
( mgrno,mname,job,
remark "replace(:remark,'\\n',chr(10))"
)

如果数据文件是定长字符呢.
smith   sales   manager   this is smith     
he is a sales manager
allenw tech     manager this is allen w 
he is a tech manager.
blake    hr         manager this is blake      
he is a hr manager.

load data infile ldr_case11_2.dat "fix 68"     就是加载文件之前,先通过fix值属性指定每行的长度,这里每行68个字符包括换行符在内,
                                        到了指定长度就换行,不管中间有没有换行符，因此仅能用于定长字符串的数据文件,
                                        因为只有字符串定长,你才知道应该在infile处指定什么值
truncate into table manager
(
ename position(1:8),
job position(10:16),
zhiwei position(*+1:22),
remark position(*+1:65)
)

windows中换行实际上由二个字符组成,回车加换行 chr(13)+chr(10), linux/unix下只需一个字符chr(10)即可
char_string：普通字符,即标准的可见字符,
\n，表示换行, \t 表示行制表符, \f 表示换页 \v 表示列制表符 \r 表示回车
windows下用\r\n    linux/unix下用\n就可以了

行尾部换行标识例:
数据文件
10,smith,sales manager,this is smith.
he is a sales manager. |
11,allen.w,tech manager,this is allen.w.
he is a tech manager. |
控制文件
load data 
infile ldr_case1_4.dat "str ' | \r\n"
truncate into table manager
fields terminated by ','
(mgrno,maname,job,remarek)

十,要导入大字段(lob类型) 
1，数据保存在数据文件中
load data
infile ldr_case12_1.dat "str '|\r\n'"
truncate into table manager
fields terminated by "," optionally enclosed by '"',
(mgrno,mname,job,remark char(10000))
假定remark列有大量文本，就可以这么定义

2，数据文件保存在独立的文件中
SQL> create table lobtbl(
2 fileowner varchar2(30),
3 filename varchar2(200),
4 filesize number,
5 filedata clob,
6 create_date date);
数据文件如下 ldr_case12_2.dat
2009-03-17 09:43 154   junsansi   f:\oracle\script\ldr_case11_1.ctl
2009_03_17 09:44 189   junsansi   f:\oracle\script\ldr_case11_1.dat
2009_03_17 09:45 2,639 junsansi   f:\oracle\script\ldr_case11_4.log

控制文件如下
load date 
infile ldr__case12_2.dat
truncate into table lobtbl
(create_date position(1:17) date 'yyyy-mm-dd hh24:mi',
filesize position(*+1:25) "to_number(:filesize, '99,999,999')",
fileowner position(*+1:34),
filename position(*+1) char(200) "substr(:FILENAME,INSTR(:FILENAME,'\\',-1)+1)",
filedata lobfile(filename) terminated by eof)

十一,某些字段有空值:
load data
infile ldr_case13.dat
truncate into table bonus
fields terminated by "," trailing nullcols 
(ename,job,sal)

十二,大量数据的导入
sqlldr scott/scott control=ldr_object.ctl errors=10 rows=640
表示10行出错就跳出,每次加载640行.默认是64行
如果640行太大,日志信息里面有提示,这个时候就要修改bindsize参数,默认就是256k,我们将其修改为10m(
1024X1024X10=10485760)同时这一次将行提高到5000行.
这样会更快,
sqlldr control=ldr_object.ctl errors=10 rows=5000 bindsize=10485760
但还可以更快.
sqlldr scott/scott control=ldr_object.ctl direct=true;
但还可以更快
streamsize:直接路径加载默认读取全部记录, 因此不需要设置rows参数,读取到的数据处理后存入缓存区,即
streamsize参数,该参数为256k,这里加大到10mb
date_cache: 该参数指定一个转换后日期格式的缓存区,以条为单位,默认值1000条,即保存1000条转换后的日期格式,
由于我们要导入的数据中有日期格式,因此加大该参数到5000，以降低日期转换操作带来的开销.
sqlldr scott/tiger control=ldr_object.ctl direct=true streamsize=10485760 date_cache=5000