目录
SQL*Loader对不同文件及格式的处理方法
2.1 Excel文件
一般的Excel文件最大行数不超过65536行，说明数据处理量并不大，处理Excel的方式是将其另存为CSV格式文件,然后即可按照正常方式导入即可。
2.2要加载的文件不是以逗号分隔
有两种方式可以参考：
1)修改数据文件，将分隔符替换为逗号。
2)修改控制文件，将FIELDS TERMINATED BY的值修改为实际的分隔符。
2.3要加载的数据中包含分隔符
例如,要向scott.tb_loader表插入数据提供的数据格式如下：
SMITH,CLEAK,3904
ALLEN,"SALER,M",2891
WARD,"SALER,""S""",3128
KING,PRESIDENT,2523
修改控制文件,注意下列示例代码中的粗体字符,OPTIONALLY ENCLOSED BY参数指明定界符为双引号(CSV格式文件默认定界符就是双引号,你可以根据实际情况修改OPTIONALLY的参数值),如下所示：
--控制文件
[oracle@wjq SQL*Loader]$ vim wjq_test2.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test2.dat'
TRUNCATE INTO TABLE tb_loader
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(ENAME,JOB,SAL)
--数据文件
[oracle@wjq SQL*Loader]$ vim wjq_test2.dat
SMITH,CLEAK,3904
ALLEN,"SALER,M",2891
WARD,"SALER,""S""",3128
KING,PRESIDENT,2523sqlldr
运行如上代码，并查询结果如下：
[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test2.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 14:56:40 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
--查询结果
SCOTT@seiang11g>select * from tb_loader;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
SMITH CLEAK 3904
ALLEN SALER,M 2891
WARD SALER,"S" 3128
KING PRESIDENT 2523
2.4数据文件没有分隔符
如下的数据文件专业叫做定长字符串，sqlldr中处理定长字符串也轻而易举。针对此例，我们将控制文件修改如下：
--控制文件
[oracle@wjq SQL*Loader]$ vim wjq_test3.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test3.dat'
TRUNCATE INTO TABLE tb_loader
(
ENAME position(1:5),
JOB position(10:18),
SAL position(23:26)
)
--数据文件
[oracle@wjq SQL*Loader]$ vim wjq_test3.dat
SMITH CLEAK 3904
ALLEN SALESMAN 2891
WARD SALESMAN 3128
KING PRESIDENT 252
position关键字用来指定列的开始和结束位置，如JOB position(10:18)是指从第10个字符开始截止到第18个字符作为ENAME列的列值。position的写法也很灵活，要实现上述功能还可以换成下列几种形式：
①position(*+2:18):直接指定数值的方式叫作绝对偏移量，如果使用*号，专业名词叫相对偏移量，表示上一个字段从哪里结束，这次就从哪里开始，相对偏移量也 可以再做运算，比如Position(*+2:15)就表示从上次结束的位置+2的地方开始。
②position(*) char(9):这种相对偏移量+类型和长度的优势在于，你只需要为第一列 指定开始位置，其他列只需要指定列长度就可以了，实际使用中比较省事。
sqlldr运行如上代码，并查询结果如下：
--sqlldr命令
[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test3.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:04:13 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
--查询结果
SCOTT@seiang11g>select * from tb_loader;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
SMITH CLEAK 3904
ALLEN SALESMAN 2891
WARD SALESMAN 3128
KING PRESIDENT 252
2.5数据文件中的列比要导入的表中的列少
在前面几个例子中，数文件中的列比表中的列要少的环境中演示的，这说明列少不怕，关键是看控制文件中的配置。但是如果缺少的列必须赋值又怎么办呢？只需稍改下控制文件即可，直接指定COMM列，并赋初始值0(这里仍然引用ldr_case3.dat中的数据）：
--控制文件
[oracle@wjq SQL*Loader]$ vim wjq_test4.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test3.dat'
TRUNCATE INTO TABLE tb_loader
(
ENAME position(1:5),
JOB position(10:18),
SAL position(23:26),
COMM "0"
)
--sqlldr命令
[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test4.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:08:50 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
--查看结果
SCOTT@seiang11g>select * from tb_loader;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
SMITH CLEAK 3904 0
ALLEN SALESMAN 2891 0
WARD SALESMAN 3128 0
KING PRESIDENT 252 0
COMM的值也可以根据其他列的值而定，修改控制文件如下
--控制文件
[oracle@wjq SQL*Loader]$ vim wjq_test5.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test3.dat'
TRUNCATE INTO TABLE tb_loader
(
ENAME position(1:5),
JOB position(10:18),
SAL position(23:26),
COMM "substr(:SAL,1,1)"
)
sqlldr执行上述代码,结果如下，很明显发现COMM的值是根据SAL的值的第1位数字获得
--sqlldr命令
[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test5.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:12:00 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
--执行结果
SCOTT@seiang11g>select * from tb_loader;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
SMITH CLEAK 3904 3
ALLEN SALESMAN 2891 2
WARD SALESMAN 3128 3
KING PRESIDENT 252 2
这里COMM列的值根据SAL列值而定，我们通过一个SQL中的函数substr取SAL值的第一列，赋予COMM列，当然这只是一个示例，DBA可以根据实际需求进行适当的 修改，通过SQL中的函数可以实现很多很有意思的转换，也许能够为你省下很大力气，而且如果现有函数无法实现，甚至可以通过PL/SQL编写自定义的函数，然后在sqlldr的 控制文件中调用，调用方式与系统自带函数方式完全相同，这样就可以根据需求对要加载 的列做审灵活的处理。
2.6数据文件中的列比要导入的表中列多
如果数据文件中的列比要导入的表中的列少，处理的时候可能麻烦些，多了反倒更简单，针对不同情况，一般有以下两种处理方式：
方式一：修改数据文件，将多余的数据删除，不过以这种方式处理，小数据量时还可行，一旦数据文件较大，几百甚至上千兆，修改数据文件耗时耗力。
方式二：使用sqlldr中控制文件FILLER来排除不需要的列
1）演示数据文件如下
--数据文件
[oracle@wjq SQL*Loader]$ vim wjq_test6.dat
SMITH 7369 CLERK 1020 20
ALLEN 7499 SALESMAN 1930 30
WARD 7521 SALESMAN 1580 30
JONES 7566 MANAGER 3195 20
MARTIN 7654 SALESMAN 1580 30
BLAKE 7698 MANAGER 3180 30
CLARK 7782 MANAGER 2172 10
SCOTT 7788 ANALYST 3220 20
KING 7839 PRESIDENT 4722 10
TURNER 7844 SALESMAN 1830 30
ADAMS 7876 CLERK 1320 20
JAMES 7900 CLERK 1280 30
FORD 7902 ANALYST 3220 20
MILLER 7934 CLERK 1022 10
此时我们的需求希望我们导入第1、3、4列而跳过2、5列，创建控制文件如下
--控制文件
[oracle@wjq SQL*Loader]$ vim wjq_test6.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test6.dat'
TRUNCATE INTO TABLE tb_loader
(
ENAME position(1:6),
COL1 FILLER position(10:13),
JOB position(17:25),
SAL position(28:31)
)
sqlldr的控制文件中对列定义时支持FILLER关键字，可以用来指定过滤列，在上述控制文件中，我们就使用该关键字来过滤列，相当于第10到第13列之间的数据不导入。
事实上由于此处为定长字串，我们在控制文件中指定的position参数，己经限定了读取的内容，你甚至可以删除控制文件中TCOL FILLER position (10:13)那行。
执行sqlldr命令：
[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test6.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:24:36 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 14
--查询结果
SCOTT@seiang11g>select * from tb_loader;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
SMITH CLERK 1020
ALLEN SALESMAN 1930
WARD SALESMAN 1580
JONES MANAGER 3195
MARTIN SALESMAN 1580
BLAKE MANAGER 3180
CLARK MANAGER 2172
SCOTT ANALYST 3220
KING PRESIDENT 4722
TURNER SALESMAN 1830
ADAMS CLERK 1320
JAMES CLERK 1280
FORD ANALYST 3220
MILLER CLERK 1022
2）如果数据文件中字符串不是定长格式，而是通过分隔符来处理的，那控制文件中就需要注意，如数据文件如下：
--数据文件
[oracle@wjq SQL*Loader]$ vim wjq_test7.dat
SMITH,7369,CLERK,1020,20
ALLEN,7499,SALESMAN,1930,30
WARD,7521,SALESMAN,1580,30
JONES,7566,MANAGER,3195,20
MARTIN,7654,SALESMAN,1580,30
BLAKE,7698,MANAGER,3180,30
CLARK,7782,MANAGER,2172,10
SCOTT,7788,ANALYST,3220,20
KING,7839,PRESIDENT,4722,10
TURNER,7844,SALESMAN,1830,30
ADAMS,7876,CLERK,1320,20
JAMES,7900,CLERK,1280,30
FORD,7902,ANALYST,3220,20
MILLER,7934,CLERK,1022,10
此时创建控制文件时，控制文件中就必须制定FILLER，不然列中的值可能不对应，创建控制文件如下
--控制文件
[oracle@wjq SQL*Loader]$ vim wjq_test7.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test7.dat'
TRUNCATE INTO TABLE tb_loader
FIELDS TERMINATED BY ","
(
ENAME,COL1 FILLER,JOB,SAL
)
执行sqlldr命令，并查看结果
--sqlldr命令
[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test7.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:32:48 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 14
--查看结果
SCOTT@seiang11g>select * from tb_loader;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
SMITH CLERK 1020
ALLEN SALESMAN 1930
WARD SALESMAN 1580
JONES MANAGER 3195
MARTIN SALESMAN 1580
BLAKE MANAGER 3180
CLARK MANAGER 2172
SCOTT ANALYST 3220
KING PRESIDENT 4722
TURNER SALESMAN 1830
ADAMS CLERK 1320
JAMES CLERK 1280
FORD ANALYST 3220
MILLER CLERK 1022