Oracle sqlldr使用总结

Oracle sqlldr使用总结

先看个简单的，源文件(test.csv)内容如下：

"张三","上海市黄浦区","38"
"李四","广东省深圳市罗湖区","29"
…………
…………

上面文本文件test.csv里的内容，除了使用逗号分割以外，还使用了双引号包围。

创建控制文件test.ctl如下：

load data
infile 'test.csv'
insert into table victor.test
fields terminated by ','
optionally enclosed by '"'
(
c1,
c2,
c3
)

注意：要把文本文件test.csv的内容导入到表victor.test，但是要忽略掉文本里的双引号""，
则必须使用optionally enclosed by '"'，否则双引号也会跟着导入进去。
另外，insert into table victor.test这一行中的insert可以替换为append,replace,truncate等，
说明如下：
a) insert，为缺省方式，在数据装载开始时要求表为空
b) append，在表中追加新记录
c) replace，删除旧记录，替换成新装载的记录
d) truncate，同上

导入命令：
sqlldr victor/victor control=test.ctl

如果导入之后查询表的数据发现是乱码，则请检查环境变量是否设置为zhs16gbk，如下：
export NLS_LANG=american_america.zhs16gbk
然后重新导入就不会有乱码了。

上面很容易明白，现在搞个复杂一点的。
源文件(test.csv)内容如下：

"  Jackson ","   95   ","19890805"," NewYork"
" Michael ","   92   ","19900120","  Bei Jing  "
" Emily ","   100   ","19891110"
"  Victor  ","   89   ","","  Shenzhen     "
" Unkown "
"","  92 ",""," GuangZhou "
"","",""," Shanghai "
…………
…………
…………
此文本有上千万行记录，有些记录是空值，有些内容前后有空格！
文本里的空值如实导入，但空格需要去除。

准备导入到下面这个表：
SQL> desc test
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 C1                                                 CHAR(10)
 C2                                                 NUMBER
 C3                                                 DATE
 C4                                                 VARCHAR2(15)

控制文件是test.ctl，内容如下：

options(
errors='500' ,
rows='100000' ,
bindsize='40960000' ,
readsize='40960000' ,
streamsize='40960000',
parallel='no',
direct='yes'
)

unrecoverable
load data
infile "test.csv" badfile "test.bad"

into table test
truncate
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(
c1 char(10) "trim(:c1)",
c2 "trim(:c2)",
c3 date 'yyyymmdd',
c4 char(15) "trim(:c4)"
)

对这个控制文件的几个参数的说明：
errors -- 允许的错误的数目         (默认 50) 如果不指定，达到错误上限自动停止，不会完全导入
rows -- 每次提交的记录数(默认: 常规路径 64, 所有直接路径)
bindsize -- 每次提交记录的缓冲区的大小 (以字节计) (默认 256000)
readsize -- 读取缓冲区的大小               (默认 1048576)
streamsize -- 直接路径流缓冲区的大小 (以字节计) (默认 256000)
parallel -- 执行并行加载                    (默认 FALSE)
direct -- 使用直接路径                     (默认 FALSE)
trailing   nullcols --  表的字段没有对应的值时允许为空

执行sqlldr装载文本：
sqlldr victor/victor control=test.ctl

查询表test进行结果验证：
SQL> select * from test where rownum<9;

C1                 C2        C3         C4
---------- -------------- ---------------- ---------------
Jackson            95 1989-08-05 NewYork
Michael             92 1990-01-20 Bei Jing
Emily               100 1989-11-10
Victor                89                       Shenzhen
Unkown
                          92                       GuangZhou
                                                      Shanghai


SQL> select * from test where c1='Jackson';

C1                 C2 C3         C4
---------- ---------- ---------- ---------------
Jackson            95 1989-08-05 NewYork

SQL> select * from test where c4='Bei Jing';

C1                 C2 C3         C4
---------- ---------- ---------- ---------------
Michael            92 1990-01-20 Bei Jing

SQL> select * from test where c4='Shanghai';

C1                 C2 C3         C4
---------- ---------- ---------- ---------------
                                 Shanghai
以上显示导入正常！