practical sqlldr of oracle

1、sqlldr_in.bat
 

sqlldr control=REPORT_DATA.ctl log=REPORT_DATA.log rows=10000 readsize=20000000 bindsize=20000000

说明：这只是一个批处理，再要增加数据的调度，再下一行添加即可

2、控制文件 (REPORT_DATA.ctl)

 

OPTIONS(skip=1,rows=10000) --此处的参数选项为跳过第一行(有时候数据文件第一行是列名，不是我们想要装载的数据，所以要跳过)，每10000行提交一次(一般应为10000左右，提高效率);当然这些参数也可以写在调sqlldr的批处理当中
LOAD DATA
INFILE 'D:\sqlldr_windows\ReportData1.dat'
INFILE 'D:\sqlldr_windows\ReportData2.dat' --此处可以写多个数据文件，但OPTIONS设置的值对全体有效，如在此参数文件脚本中，第一个文件的列名可以不取，第二个列名则过滤不了，遇到这种情况，可以考虑用两个sqlldr调度，第二个控制文件用append的处理方式
TRUNCATE
INTO TABLE REPORT_DATA
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
REPORTNO      "NVL(:REPORTNO,'0')",
ROWNO         "NVL(:ROWNO,' ')",
ROWNAME       "SUBSTR(:ROWNAME,1,3)||'我是中国人'", --此处说明：在sqlldr的参数文件里，可以使用oracle函数及连字符等
ROWSUBJECT    "NVL(:ROWSUBJECT,'0')",
DISPLAYORDER "NVL(:DISPLAYORDER,'0')",
ROWDIMTYPE    "NVL(:ROWDIMTYPE,'0')",
ROWATTRIBUTE "NVL(:ROWATTRIBUTE,'0')",
COL1VALUE     "NVL(:COL1VALUE,'0')",
COL2VALUE     "NVL(:COL2VALUE,'0')",
COL3VALUE     "NVL(:COL3VALUE,'0')",
COL4VALUE     "NVL(:COL4VALUE,'0')",
STANDARDVALUE ":STANDARDVALUE||'2341'" --可以根据需要，灵活使用函数,包括自己在数据库里写的函数，都可以用在这里
)   --如果数据文件里没有那一列值，而要导入一个默认值，可以在控制文件中再加一行，如：GBICC         "NVL(:GBICC,'吉林银行')" －－＞则可实现，数据文件中没有值的默认值导入

3、数据文件示例(ReportDate1.dat/ReportDate2.dat):
 

REPORTNO|ROWNO|ROWNAME|ROWSUBJECT|DISPLAYORDER|ROWDIMTYPE|ROWATTRIBUTE|COL1VALUE|COL2VALUE|COL3VALUE|COL4VALUE|STANDARDVALUE
20090819001081|0083|实收资本（或股本）|301|0083|||5000000.0|5000000.0|||
20090819001081|0084|资本公积|302|0084|||1226274.73|1226274.73|||
20090819001081|0085|减：库存股|309|0085|||0.0|0.0|||
20090819001081|0086|盈余公积|303|0086|||2500000.0|2500000.0|||
20090819001081|0087|未分配利润|305|0087|||5.676863728E7|6.372684348E7|||
20090819001081|0088|少数股东权益|307|0088|||0.0|0.0|||

4、关于sqlldr:

（1）在操作类型 truncate 位置可用以下中的一值：
1) insert --为缺省方式，在数据装载开始时要求表为空
2) append --在表中追加新记录
3) replace --删除旧记录(用 delete from table 语句)，替换成新装载的记录
4) truncate --删除旧记录(用 truncate table 语句)，替换成新装载的记录
（2）参数说明:
log -- 记录导入时的日志文件，默认为 控制文件(去除扩展名).log
bad -- 坏数据文件，默认为 控制文件(去除扩展名).bad
data -- 数据文件，一般在控制文件中指定。用参数控制文件中不指定数据文件更适于自动操作
errors -- 允许的错误记录数，可以用他来控制一条记录都不能错
rows -- 多少条记录提交一次，默认为 64
skip -- 跳过的行数，比如导出的数据文件前面几行是表头或其他描述
（3）sqlldr装载数据时的两种方式
有两种使用方法：
1)只使用一个控制文件，在这个控制文件中包含数据
2)使用一个控制文件(作为模板) 和一个数据文件
一般为了利于模板和数据的分离，以及程序的不同分工会使用第二种方式，第一种方法较不常用;数据文件可以是CSV文件或者以其他分割符分隔的，数据文件可以用 PL/SQL Developer 或者 Toad 导出、sqlplus中用spool导出，也可是从其它类型数据库中以其他方式导出;另外，用 Toad 还能直接生成包含数据的控制文件。

5、sqlldr的优势所在

一是可以直接被前台应用程序调用;
二是可以从既定文件中快速大量导入数据,sqlldr速度快的原因是多条插入之后再一次提交，避免了数据库的频繁读写，数据库压力较小;
三是可以实现把多个数据文件合并导到一张表中;
四是修复、分离坏的记录,记录到.bad文件中。

感想总结：

批处理、控制文件、数据文件最好在一个目录下，这样方便调度；在unix或Linux上调shell的时候，可能会出现分隔符不一致的情况，这样可以用replace函数或编辑源数据进行相应处理。