使用SQL*Loader高速批量数据加载工具！

最新推荐文章于 2021-10-30 11:35:11 发布

事后诸葛亮

最新推荐文章于 2021-10-30 11:35:11 发布

阅读量1.3k

点赞数

分类专栏：数据加载&卸载文章标签： sql oracle linux table insert character

本文链接：https://blog.csdn.net/zq9017197/article/details/6844939

版权

数据加载&卸载专栏收录该内容

11 篇文章 0 订阅

订阅专栏

1.控制文件中包含要加载的数据

首先创建一张测试表

SQL> show user
USER 为 "ING"
SQL> create table dept
  2  (deptno number(10) constraint dept_pk primary key,
  3   dname varchar2(20),
  4   loc varchar2(20));

表已创建。

然后创建一个控制文件（其中包含被加载的数据）

[oracle@linux sqlldr]$ pwd
/u01/sqlldr
[oracle@linux sqlldr]$ cat demo1.ctl 
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO,DNAME,LOC)
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia

然后在命令行执行加载

[oracle@linux sqlldr]$ pwd
/u01/sqlldr
[oracle@linux sqlldr]$ sqlldr userid=ing/ing control=demo1.ctl log=demo1.log

SQL*Loader: Release 10.2.0.4.0 - Production on 星期二 10月 4 18:39:54 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

达到提交点 - 逻辑记录计数 4

查看dept表

SQL> select * from dept;

    DEPTNO DNAME                LOC
---------- -------------------- --------------------
        10 Sales                Virginia
        20 Accounting           Virginia
        30 Consulting           Virginia
        40 Finance              Virginia

查看demo1.log日志文件

[oracle@linux sqlldr]$ pwd
/u01/sqlldr
[oracle@linux sqlldr]$ cat demo1.log 

SQL*Loader: Release 10.2.0.4.0 - Production on 星期二 10月 4 18:39:54 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

控制文件:      demo1.ctl
数据文件:      demo1.ctl
  错误文件:    demo1.bad
  废弃文件:    未作指定
 
(可废弃所有记录)

要加载的数: ALL
要跳过的数: 0
允许的错误: 50
绑定数组: 64 行, 最大 256000 字节
继续:    未作指定
所用路径:       常规

表 DEPT,已加载从每个逻辑记录
插入选项对此表 INSERT 生效

   列名                        位置      长度  中止 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *   ,       CHARACTER            
DNAME                                NEXT     *   ,       CHARACTER            
LOC                                  NEXT     *   ,       CHARACTER            


表 DEPT:
  4 行 加载成功。
  由于数据错误, 0 行 没有加载。
  由于所有 WHEN 子句失败, 0 行 没有加载。
  由于所有字段都为空的, 0 行 没有加载。


为绑定数组分配的空间:                 49536 字节 (64 行)
读取   缓冲区字节数: 1048576

跳过的逻辑记录总数:          0
读取的逻辑记录总数:             4
拒绝的逻辑记录总数:          0
废弃的逻辑记录总数:        0

从 星期二 10月 04 18:39:54 2011 开始运行
在 星期二 10月 04 18:39:54 2011 处运行结束

经过时间为: 00: 00: 00.68
CPU 时间为: 00: 00: 00.07

2.控制文件和数据文件分开

继续使用前面的dept表，首先创建一个控制文件和一个数据文件

[oracle@linux sqlldr]$ pwd
/u01/sqlldr
[oracle@linux sqlldr]$ cat demo2.ctl 
load data
infile demo2.data
append into table dept
fields terminated by ','
(deptno,dname,loc)
[oracle@linux sqlldr]$ cat demo2.data 
50,Sales,Virginia
60,Accounting,Virginia
70,Consulting,Virginia
80,Finance,Virginia

然后在命令行执行加载

[oracle@linux sqlldr]$ sqlldr userid=ing/ing control=demo2.ctl 

SQL*Loader: Release 10.2.0.4.0 - Production on 星期二 10月 4 18:47:23 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

达到提交点 - 逻辑记录计数 4

最后查看dept表

SQL> select * from dept;

    DEPTNO DNAME                LOC
---------- -------------------- --------------------
        10 Sales                Virginia
        20 Accounting           Virginia
        30 Consulting           Virginia
        40 Finance              Virginia
        50 Sales                Virginia
        60 Accounting           Virginia
        70 Consulting           Virginia
        80 Finance              Virginia

已选择8行。

3.sql*loader简要说明

注意：下面代码左边加括号的数并不是控制文件的一部分，只是为了方便显示。

（1）LOAD DATA
（2）INFILE *
（3）INTO TABLE DEPT
（4）FIELDS TERMINATED BY ','
（5）(DEPTNO,DNAME,LOC)
（6）BEGINDATA
（7）10,Sales,Virginia
（8）20,Accounting,Virginia
（9）30,Consulting,Virginia
（10）40,Finance,Virginia
（1）：这会告诉sqlldr要做什么，上面的例子指示要加载数据。
（2）：*是指要加载所有的数据（例子1），也可以直接指定数据文件的名字（例子2）。
（3）：这会告诉sqlldr要加载到哪个表中。完整语法：[insert | append | replace | truncate] into table dept insert - 默认就是insert，后面的dept表必须的空的；append - 是追加，后面的dept表可以不为空；replace - 是先delete然后在insert；truncate - 是先truncate再insert。
（4）：是告诉sqlldr以逗号分隔值。
（5）：是告诉sqlldr数据要加载到对应的列中。
（6）：是告诉sqlldr要加载的数据开始了。
（7）~（10）：是要被加载的具体的数据。

4.常见的一些问题

如何加载特殊字符（引号）

[oracle@linux sqlldr]$ pwd
/u01/sqlldr
[oracle@linux sqlldr]$ cat demo3.ctl 
LOAD DATA
INFILE *
REPLACE INTO TABLE DEPT
FIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '"'
(DEPTNO,DNAME,LOC)
BEGINDATA
10,Sales,"Virginia,USA"
20,Accounting,"VS,""USA"""
30,Consulting,Virginia
40,Finance,Virginia
[oracle@linux sqlldr]$ sqlldr ing/ing control=demo3.ctl 

SQL*Loader: Release 10.2.0.4.0 - Production on 星期二 10月 4 19:36:52 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

达到提交点 - 逻辑记录计数 4

查询结果

SQL> select * from dept;

    DEPTNO DNAME                LOC
---------- -------------------- --------------------
        10 Sales                Virginia,USA
        20 Accounting           VS,"USA"
        30 Consulting           Virginia
        40 Finance              Virginia

说明：部门10中的Virginia,USA：这是因为数据是"Virginia,USA"。输入数据字段必须包括在括号里才能保留数据中的逗号。

VS,"USA"：这是因为输入数据是"VS,""USA"""。对于括号引起的字符串，两次“记为一次出现。

如何加载固定格式数据

[oracle@linux sqlldr]$ pwd
/u01/sqlldr
[oracle@linux sqlldr]$ cat demo4.ctl 
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
(DEPTNO POSITION(1:2),
 DNAME POSITION(3:12),
 LOC POSITION(13:20))
BEGINDATA
10Sales     Virginia
20AccountingVirginia
30ConsultingVirginia
40Finance   Virginia
[oracle@linux sqlldr]$ sqlldr ing/ing control=demo4.ctl 

SQL*Loader: Release 10.2.0.4.0 - Production on 星期二 10月 4 19:45:56 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

达到提交点 - 逻辑记录计数 4

查看

SQL> select * from dept;

    DEPTNO DNAME                LOC
---------- -------------------- --------------------
        10 Sales                Virginia
        20 Accounting           Virginia
        30 Consulting           Virginia
        40 Finance              Virginia

说明：上面控制文件中的(DEPTNO POSITION(1:2), DNAME POSITION(3:12), LOC POSITION(13:20))还可以改为(DEPTNO POSITION(1:2), DNAME POSITION(*:12), LOC POSITION(*:20))效果是一样的。前者是绝对位置，后者是相对位置。

还可以这样写：(DEPTNO POSITION(1:2), DNAME POSITION(*+2:12), LOC POSITION(*+2:20))，只不过这里的*+2对于后面的数据就不太使用了，这里只是给出一个用法展示。

如何使用函数加载数据

[oracle@linux sqlldr]$ pwd
/u01/sqlldr
[oracle@linux sqlldr]$ cat demo5.ctl 
LOAD DATA
INFILE *
REPLACE INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO,
 DNAME "UPPER(:DNAME)",
 LOC "UPPER(:LOC)")
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia
[oracle@linux sqlldr]$ sqlldr ing/ing control=demo5.ctl 

SQL*Loader: Release 10.2.0.4.0 - Production on 星期二 10月 4 19:55:25 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

达到提交点 - 逻辑记录计数 4

查看

SQL> select * from dept;

    DEPTNO DNAME                LOC
---------- -------------------- --------------------
        10 SALES                VIRGINIA
        20 ACCOUNTING           VIRGINIA
        30 CONSULTING           VIRGINIA
        40 FINANCE              VIRGINIA

说明：在sql中能做的事情都可以结合到sqlldr脚本中。

如何加载日期

首先修改一下表

SQL> alter table dept add time date;

表已更改。

SQL> desc dept
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER(10)
 DNAME                                              VARCHAR2(20)
 LOC                                                VARCHAR2(20)
 TIME                                               DATE

加载

[oracle@linux sqlldr]$ pwd
/u01/sqlldr
[oracle@linux sqlldr]$ cat demo6.ctl 
LOAD DATA
INFILE *
REPLACE INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO,
 DNAME,
 LOC,
 TIME DATE 'yyyy-mm-dd')
BEGINDATA
10,Sales,Virginia,2011-10-1
20,Accounting,Virginia,2011-10-10
30,Consulting,Virginia,2011-10-11
40,Finance,Virginia,2011-10-08
[oracle@linux sqlldr]$ sqlldr ing/ing control=demo6.ctl 

SQL*Loader: Release 10.2.0.4.0 - Production on 星期二 10月 4 20:00:25 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

达到提交点 - 逻辑记录计数 4

查询

SQL> select * from dept;

    DEPTNO DNAME                LOC                  TIME
---------- -------------------- -------------------- --------------
        10 Sales                Virginia             01-10月-11
        20 Accounting           Virginia             10-10月-11
        30 Consulting           Virginia             11-10月-11
        40 Finance              Virginia             08-10月-11

说明：后面的日期必须与前面指定的日期格式相对应起来。

如何加载NULL列数据
首先修改一下表

SQL> alter table dept add readme varchar(20);

表已更改。

SQL> desc dept
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER(10)
 DNAME                                              VARCHAR2(20)
 LOC                                                VARCHAR2(20)
 TIME                                               DATE
 README                                             VARCHAR2(20)

加载

[oracle@linux sqlldr]$ pwd
/u01/sqlldr
[oracle@linux sqlldr]$ cat demo7.ctl 
LOAD DATA
INFILE *
REPLACE INTO TABLE DEPT
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
 DNAME,
 LOC,
 TIME DATE 'yyyy-mm-dd',
 README)
BEGINDATA
10,Sales,Virginia,2011-10-1
20,Accounting,Virginia,2011-10-10
30,Consulting,Virginia,2011-10-11
40,Finance,Virginia,2011-10-08
[oracle@linux sqlldr]$ sqlldr ing/ing control=demo7.ctl 

SQL*Loader: Release 10.2.0.4.0 - Production on 星期二 10月 4 20:07:55 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

达到提交点 - 逻辑记录计数 4

查询

SQL> select * from dept;

    DEPTNO DNAME                LOC                  TIME           README
---------- -------------------- -------------------- -------------- --------------------
        10 Sales                Virginia             01-10月-11
        20 Accounting           Virginia             10-10月-11
        30 Consulting           Virginia             11-10月-11
        40 Finance              Virginia             08-10月-11

说明：可以看见并没有提供README列的数据，指定TRAILING NULLCOLS关键字，README列就会成为NULL。

事后诸葛亮

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
使用SQL*Loader高速批量数据加载工具！

1.控制文件中包含要加载的数据首先创建一张测试表SQL> show userUSER 为 "ING"SQL> create table dept 2 (deptno number(10) constraint dept_pk primary key, 3
复制链接

扫一扫