SQL*Loader的使用总结(二)

本文主要是介绍SQL*Loader的使用总结(二)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

SQL*Loader对不同文件及格式的处理方法

1.Excel文件

一般的Excel文件最大行数不超过65536行，说明数据处理量并不大，处理Excel的方式是将其另存为CSV格式文件,然后即可按照正常方式导入即可。

2.要加载的文件不是以逗号分隔
有两种方式可以参考：
1)修改数据文件，将分隔符替换为逗号。
2)修改控制文件，将FIELDS TERMINATED BY的值修改为实际的分隔符。

3.要加载的数据中包含分隔符
例如,要向scott.bonus表插入数据提供的数据格式如下：
SMITH,CLEAK,3904
ALLEN,"SALER,M",2891
WARD,"SALER,""S""",3128
KING,PRESIDENT,2523
修改控制文件,注意下列示例代码中的粗体字符,OPTIONALLY ENCLOSED BY参数指明定界符为双引号(CSV格式文件默认定界符就是双引号,你可以根据实际情况修改OPTIONALLY的参数值),如下所示：

--控制文件
[oracle@cancer ~]$ cat ldr_case2.ctl 
LOAD DATA
INFILE ldr_case2.dat
TRUNCATE INTO TABLE BONUS
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(ENAME,JOB,SAL)--数据文件
[oracle@cancer ~]$ cat ldr_case2.dat
SMITH,CLEAK,3904
ALLEN,"SALER,M",2891
WARD,"SALER,""S""",3128
KING,PRESIDENT,2523

sqlldr运行如上代码，并查询结果如下：

--sqlldr命令
[oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case2.ctl 
Commit point reached - logical record count 4--查询结果
SQL> select * from bonus;
ENAME	   JOB		    SAL       COMM
---------- --------- ---------- ----------
SMITH	   CLEAK	   3904
ALLEN	   SALER,M	   2891
WARD	   SALER,"S"   3128
KING	   PRESIDENT   2523

4.数据文件没有分隔符

如下的数据文件专业叫做定长字符串，sqlldr中处理定长字符串也轻而易举。针对此例，我们将控制文件修改如下：

--控制文件
[oracle@cancer ~]$ cat ldr_case3.ctl
LOAD DATA
INFILE ldr_case3.dat
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:5),
JOB position(10:18),
SAL position(23:26)
)--数据文件
[oracle@cancer ~]$ cat ldr_case3.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@cancer ~]$ sqlldr scott/tiger control=ldr_case3.ctl
Commit point reached - logical record count 4--查询结果
SQL> select * from bonus;
ENAME	   JOB		    SAL       COMM
---------- --------- ---------- ----------
SMITH	   CLEAK	   3904
ALLEN	   SALESMAN	   2891
WARD	   SALESMAN	   3128
KING	   PRESIDENT   2523

5.数据文件中的列比要导入的表中的列少

在前面几个例子中，数文件中的列比表中的列要少的环境中演示的，这说明列少不怕，关键是看控制文件中的配置。但是如果缺少的列必须赋值又怎么办呢？只需稍改下控制文件即可，直接指定COMM列，并赋初始值0(这里仍然引用ldr_case3.dat中的数据）：

--控制文件
[oracle@cancer ~]$ cat ldr_case4.ctl 
LOAD DATA
INFILE ldr_case3.dat
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:5),
JOB position(10:18),
SAL position(23:26),
COMM "0"
)

查看演示结果

--sqlldr命令
[oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case4.ctl
Commit point reached - logical record count 4--查看结果
SQL> select * from bonus;
ENAME	   JOB		    SAL       COMM
---------- --------- ---------- ----------
SMITH	   CLEAK	   3904 	 0
ALLEN	   SALESMAN	   2891 	 0
WARD	   SALESMAN	   3128 	 0
KING	   PRESIDENT   2523 	 0

COMM的值也可以根据其他列的值而定，修改控制文件如下

--控制文件
[oracle@cancer ~]$ cat ldr_case5.ctl 
LOAD DATA
INFILE ldr_case3.dat
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:5),
JOB position(10:18),
SAL position(23:26),
COMM "substr(:SAL,1,1)"
)

sqlldr执行上述代码,结果如下，很明显发现COMM的值是根据SAL的值的第1位数字获得

--sqlldr命令
[oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case5.ctl 
Commit point reached - logical record count 4--执行结果
SQL> select * from bonus;
ENAME	   JOB		    SAL       COMM
---------- --------- ---------- ----------
SMITH	   CLEAK	   3904 	 3
ALLEN	   SALESMAN	   2891 	 2
WARD	   SALESMAN	   3128 	 3
KING       PRESIDENT   2523      2

这里COMM列的值根据SAL列值而定，我们通过一个SQL中的函数substr取SAL值的第一列，赋予COMM列，当然这只是一个示例，DBA可以根据实际需求进行适当的 修改，通过SQL中的函数可以实现很多很有意思的转换，也许能够为你省下很大力气，而且如果现有函数无法实现，甚至可以通过PL/SQL编写自定义的函数，然后在sqlldr的 控制文件中调用，调用方式与系统自带函数方式完全相同，这样就可以根据需求对要加载 的列做审灵活的处理。

6.数据文件中的列比要导入的表中列多

如果数据文件中的列比要导入的表中的列少，处理的时候可能麻烦些，多了反倒更简单，针对不同情况，一般有以下两种处理方式：
方式一：修改数据文件，将多余的数据删除，不过以这种方式处理，小数据量时还可行，一旦数据文件较大，几百甚至上千兆，修改数据文件耗时耗力。
方式二：使用sqlldr中控制文件FILLER来排除不需要的列
1）演示数据文件如下

--数据文件
[oracle@cancer ~]$ cat ldr_case6.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@cancer ~]$ cat ldr_case6.ctl
LOAD DATA
INFILE ldr_case6.dat
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:6),
TCOL FILLER position(10:13),
JOB position(17:25),
SAL position(28:31)
)

sqlldr的控制文件中对列定义时支持FILLER关键字，可以用来指定过滤列，在上述控制文件中，我们就使用该关键字来过滤列，相当于第10到第13列之间的数据不导入。
事实上由于此处为定长字串，我们在控制文件中指定的position参数，己经限定了读取的内容，你甚至可以删除控制文件中TCOL FILLER position (10:13)那行。
执行sqlldr命令：

--sqlldr命令
[oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case6.ctl 
Commit point reached - logical record count 14--查询结果
SQL> select * from bonus;
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	   1320ENAME	   JOB		    SAL       COMM
---------- --------- ---------- ----------
JAMES	   CLERK	   1280
FORD	   ANALYST	   3220
MILLER	   CLERK	   102214 rows selected.

2）如果数据文件中字符串不是定长格式，而是通过分隔符来处理的，那控制文件中就需要注意，如数据文件如下：

--数据文件
[oracle@cancer ~]$ cat ldr_case7.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@cancer ~]$ vim ldr_case7.ctl 
LOAD DATA
INFILE ldr_case7.dat
TRUNCATE INTO TABLE BONUS
FIELDS TERMINATED BY ","
(ENAME,XCOL FILLER,JOB,SAL)

执行sqlldr命令，并查看结果

--sqlldr命令
[oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case7.ctl 
Commit point reached - logical record count 14--查看结果
[oracle@cancer ~]$ sqlplus scott/tiger;
SQL> select * from bonus;
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	   1320ENAME	   JOB		    SAL       COMM
---------- --------- ---------- ----------
JAMES	   CLERK	   1280
FORD	   ANALYST	   3220
MILLER	   CLERK	   102214 rows selected.

本文内容参考<涂抹Oracle-三思笔记>一书，该书是基于Windows，本文引用了该书的脚本和结论的整理在Linux亲自测试通过，并对一些小问题进行了处理

这篇关于SQL*Loader的使用总结(二)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---