SQL*Loader是加载数据的主流方法
传统路径：SQLLDR会利用SQL插入的方式加载数据
直径路径：直接格式化数据块
从一个平面文件读取数据，并将其直接写至格式化的数据库块，而绕开整个SQL引擎，同时还能避免redo、undo的生成。要在一个没有任何数据的数据库中充分加载数据，最快的方法就是采用并行直接路径加载
外部表：允许访问操作系统文件，就好像它们是数据库表一样
数据卸载技术
平面文件卸载(flat file unload)
数据泵卸载(data pump unload)
实验1
文件路径
[oracle@node1 mysqlldr]$ pwd
/home/oracle/mysqlldr
[oracle@node1 mysqlldr]$ ll
total 16
-rw-r--r-- 1 oracle oinstall 76 Jul 22 09:53 afiedt.buf
-rw-r--r-- 1 oracle oinstall 173 Jul 22 09:56 demo1.ctl
-rw-r--r-- 1 oracle oinstall 1611 Jul 22 09:57 demo1.log
-rw-r--r-- 1 oracle oinstall 111 Jul 22 09:47 dept.sql
配置文件demo1.ctl
[oracle@node1 mysqlldr]$ 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@node1 mysqlldr]$
创建表脚本
[oracle@node1 mysqlldr]$ cat dept.sql
create table dept
(deptno number(2) constraint dept_pk primary key,
dname varchar2(14),
loc varchar2(13)
)
/
执行命令
sqlldr userid=alex/alex control=/home/oracle/mysqlldr/demo1.ctl
实验2
配置文件中第四条记录的最后一个字段有很多的字符
[oracle@node1 mysqlldr]$ cat demo2.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 text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more
清空之前插入的数据
ALEX@devdb1 >truncate table dept;
执行命令
[oracle@node1 mysqlldr]$ sqlldr userid=alex/alex control=/home/oracle/mysqlldr/demo2.ctl
在demo2.log中出现了exceeds maximum length 最大长度不够
[oracle@node1 mysqlldr]$ cat demo2.log
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO FIRST * , CHARACTER
DNAME NEXT * , CHARACTER
LOC NEXT * , CHARACTER
Record 4: Rejected - Error on table DEPT, column LOC.
Field in data file exceeds maximum length
Table DEPT:
3 Rows successfully loaded.
1 Row not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
数据只成功加载3条，最后一条没有成功
ALEX@devdb1 >select * from dept;
DEPTNO DNAME LOC
---------- ------------------------------------------ ---------------------------------------
10 Sales Virginia
20 Accounting Virginia
30 Consulting Virginia
demo2.bad文件中记录了加载失败的数据
[oracle@node1 mysqlldr]$ cat demo2.bad
40,Finance,Virginia text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more
修改控制文件demo2.ctl，将char默认的255扩展到1000
[oracle@node1 mysqlldr]$ cat demo2.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO,DNAME ,LOC char(1000))
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more
更改表dept的字段loc为varchar2（1000）
ALEX@devdb1 >alter table dept modify loc varchar2(1000);
Table altered.
ALEX@devdb1 >desc dept;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(1000)
清空表后重新加载
truncate table dept;
$ sqlldr userid=alex/alex control=/home/oracle/mysqlldr/demo2.ctl
验证结果插入成功
select * from dept;
DEPTNO DNAME
---------- ------------------------------------------
LOC
--------------------------------------------------------------------------------
10 Sales
Virginia
20 Accounting
Virginia
30 Consulting
Virginia
DEPTNO DNAME
---------- ------------------------------------------
LOC
--------------------------------------------------------------------------------
40 Finance
Virginia text more text more text more text more text more text more text more t
ext more text more text more text more text more text more text more text more t
ext moretext more text more text more text more text more text more text more te
xt more text more text more text more text more text more text more text more te
xt more text more text more text more text moretext more text more text more tex
t more text more text more text more text moretext more text more text more text
more text m
ore text more text more text more text more text more text more text
more text more text more text more text more text more text more text more text
DEPTNO DNAME
---------- ------------------------------------------
LOC
--------------------------------------------------------------------------------
more text more text more text more text more text more text more text more text
moretext more text more text more text more
实验3
配置文件中加入选项“ ”区域中代表一个完整的字段，可忽略其中的,号。
如果字段中含有”，需要用”“来替代
[oracle@node1 mysqlldr]$ cat demo3.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO,DNAME,LOC)
BEGINDATA
10,Sales,"Virginia,USA"
20,Accounting,"Va,""USA"""
30,Consulting,Virginia
40,Finance,Virginia
ALEX@devdb1 >select * from dept;
DEPTNO DNAME LOC
---------- ---------- --------------------
10 Sales Virginia,USA <===字段中包含分隔符 ,
20 Accounting Va,”USA” <===字段中包含”
30 Consulting Virginia
40 Finance Virginia
实验4 使用FILLER参数来过滤掉不需要的列
[oracle@node1 mysqlldr]$ cp demo3.ctl demo4.ctl
[oracle@node1 mysqlldr]$ vi demo4.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO,DNAME,LOC,dummy1 filler)
BEGINDATA
10,Sales,"Virginia,USA",haha
20,Accounting,"Va,""USA""",haha
30,Consulting,Virginia,haha
40,Finance,Virginia,haha
truncate table dept;
sqlldr userid=alex/alex control=/home/oracle/mysqlldr/demo4.ctl
[oracle@node1 mysqlldr]$ cat demo4.log
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO FIRST * , O(") CHARACTER
DNAME NEXT * , O(") CHARACTER
LOC NEXT * , O(") CHARACTER
DUMMY1 NEXT * , O(") CHARACTER
(FILLER FIELD)
[oracle@node1 mysqlldr]$ sqlplus alex/alex
ALEX@devdb1 >select * from dept;
DEPTNO DNAME LOC
---------- --------------- ---------------
10 Sales Virginia,USA
20 Accounting Va,"USA"
30 Consulting Virginia
40 Finance Virginia
实验5 日期类型的加载
ALEX@devdb1 >alter table dept add last_updated date;
Table altered.
ALEX@devdb1 >desc dept;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(1000)
LAST_UPDATED DATE
[oracle@node1 mysqlldr]$ cat demo5.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO,DNAME,LOC,LAST_UPDATED date 'yyyy-mm-dd')
BEGINDATA
10,Sales,Virginia,2014-05-01
20,Accounting,"Va,""USA""",2014-06-03
30,Consulting,Virginia,2014-07-22
40,Finance,Virginia,2014-07-23
[oracle@node1 mysqlldr]$ sqlldr userid=alex/alex control=/home/oracle/mysqlldr/demo5.ctl
col dname for a15
col loc for a15
select * from dept;
DEPTNO DNAME LOC LAST_UPDATED
---------- --------------- --------------- -------------------
10 Sales Virginia 2014/05/01 00:00:00
20 Accounting Va,"USA" 2014/06/03 00:00:00
30 Consulting Virginia 2014/07/22 00:00:00
40 Finance Virginia 2014/07/23 00:00:00
实验6 函数加载
[oracle@node1 mysqlldr]$ cat demo6.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc),
LAST_UPDATED date 'yyyy-mm-dd')
BEGINDATA
10,Sales,Virginia,1998-09-01
20,Accounting,Virginia,2002-08-09
30,Consulting,Virginia,2008-08-08
40,Finance,Virginia,2014-06-03
[oracle@node1 mysqlldr]$ sqlldr userid=alex/alex control=demo6.ctl
ALEX@devdb1 >select * from dept;
DEPTNO DNAME LOC LAST_UPDATED
---------- --------------- --------------- -------------------
10 SALES VIRGINIA 1998/09/01 00:00:00
20 ACCOUNTING VIRGINIA 2002/08/09 00:00:00
30 CONSULTING VIRGINIA 2008/08/08 00:00:00
40 FINANCE VIRGINIA 2014/06/03 00:00:00本文转自ICT时空 dbasdk博客，原文链接： SQL*Loader 笔记 （一） 热身练习，如需转载请自行联系原博主。
