几个SQLLDR的典型案例

    今天打算玩玩SQLLDR这个小工具，于是按照三思兄的涂抹一书中几个比较典型的案例进行操作，为了以后方便查找于是记录如下。
    1.将excel文件导入
1.1首先将execl文件另存为csv文件(该文件中各字段以","分割);
1.2配置control文件，control文件ldr_case1.txt内容如下:
load data
infile "d:\test.csv"
truncate into table bonus
fields terminated by ","
(ename,job,sal)
1.3最后执行sqlldr scott/tiger control=ldr_case1.txt即可

    2.加载的数据中包含分隔符
2.1通过optinally enclosed选项来屏蔽分隔符;
2.2配置control文件，control文件ldr_case1.txt内容如下:
load data
infile "d:\test.txt
truncate into table bonus
fields terminated by "," optionally enclosed by '"'
(ename,job,sal)
2.3数据文件test.txt内容如下:
smith,"cleak,",3905
allen,"sales,""man""",2892
2.4执行sqlldr scott/tiger control=ldr_case1.txt即可;
2.5最后通过select显示执行的结果:
SQL> select * from bonus;                                                      
ENAME      JOB                  SAL       COMM
---------- ------------------------------ ---------- ----------                
smith     cleak,                        3905           
allen      sales,"man"                2892

    3.数据文件中的列比要导入的表中列多
3.1通过tcol filler选项来屏蔽多余的列;
3.2配置control文件，control文件ldr_case1.txt内容如下:
load data
infile "d:\test.txt"
truncate into table bonus
fields terminated by "," optionally enclosed by '"'
(ename,tcol filler,sal)
3.3执行sqlldr scott/tiger control=ldr_case1.txt即可;
3.4最后通过select显示执行的结果;
SQL> select * from bonus;                                                      
ENAME      JOB                  SAL       COMM
---------- ------------------------------ ---------- ----------                
smith                                      3905           
allen                                       2892

    4.明确指定只加载nn~mm行
4.1只导入数据文件中的第2~4行,通过skip和load选项实现该需求;
4.2配置控制文件，control文件ldr_case1.txt内容如下:
load data
infile "d:\test.txt"
truncate into table bonus
fields terminated by "," optionally enclosed by '"'
(ename,job,sal)
4.3数据文件test.txt内容如下:
smith,"cleak,",3905
allen,"sales,""man""",2892
king,worker,1000
jane,manager,3000
micile,president,5000
4.4执行sqlldr scott/tiger control=ldr_case1.txt skip=1 load=3即可;
4.5执行select验证结果;
SQL> select * from bonus;
ENAME      JOB                    SAL       COMM
---------- ------------------------------ ---------- ----------
allen     sales,"man"                 2892                                     
king      worker                         1000                 
jane      manager                      3000

    5.导入某些字段无值的数据
5.1通过trailing nullcols选项屏蔽掉某些列无值的情况:
5.2配置控制文件，control文件ldr_case1.txt内容如下:
load data
infile "d:\test.txt"
truncate into table bonus
fields terminated by ","  trailing nullcols
(ename,job,sal)
5.3数据文件test.txt的内容如下:
smith,cleak,3905
king,worker,1000
jane,manager,
5.4执行sqlldr scott/tiger control=ldr_case1.txt
5.5执行select验证结果
SQL> select * from bonus;                                                      
ENAME      JOB                    SAL       COMM                
---------- ------------------------------ ---------- ----------
smith      cleak                        3905                           
king       worker                       1000
jane       manager     

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20801486/viewspace-718125/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20801486/viewspace-718125/