sqlldr的学习与总结

######################################      sqlldr数据加载工具的学习总结   




sqlldr是oracle7以来一直比较流行的数据加载工具，使用方便，灵活，在windows界面下是SQLLDR，linux/unix 平台大小写敏感，是sqlldr


有关sqlldr的五个文件：
1.控制文件：顾名思义该文件负责控制加载数据的关键信息，其中重要参数如下：
  LOAD DATA 控制文件一般都是以此开头，用来表示加载数据，默认的不用加任何参数，参数主要有UNRECOVER和RECOVER用来控制所加载数据是否可以恢复，也可以为CONTINUE_LOAD表示继续加载
  INFILE 表示要加载的数据文件的位置，若为*表示该数据文件写在该控制文件内部
  INTO TABLE 表示要加载数据的目标表，加载方式有以下几种：
INSERT INTO 向表中插入数据，该方式表必须为空表
APPEND INTO 向表中追加数据
REPLACE INTO 替换表中的数据，等于将表的数据delete后再向表中添加数据
TRUNCATE INTO 以truncate的方式将表中的数据清空后加载数据
  FIELDS TERMINATED BY "," 指定加载数据的分隔值，此处认为是，分割
  （NAME,SEX,AGE）要加入的表中的列名
  BEGINDATA 表示以下是要加载的数据，INFILE为*时参数才用得到
2.数据文件
  数据文件为要加载的数据存储文件，格式不统一
3.日志文件
  默认情况下加载数据完成后会在控制文件同目录下生成一个扩展名为.log的日志文件，用来记录数据加载情况
4.错误文件
  在sqlldr加载数据时遇到加载错误的数据，会把加载错误的数据写入同名下扩展名为.bad的错误文件中。该参数可以自行修改
5.废弃文件
  如果有需求，可以显示的制定废弃数据（不需要加载的数据）写入到一个扩展名为.dsc的废弃文件中，该文件需要特殊制定，默认情况下不会产生




一个简单的sqlldr的控制文件：
[oracle@orcl2 myscript]$ pwd
/u01/myscript


在/u01/myscript下做sqlldr的练习，写入sqlldr1.ctl如下内容


LOAD DATA  


INFILE *   //为*说明我要加载的数据就在该控制文件内
INTO TABLE BONUS //将数据加载到bouns表中
FIELDS TERMINATED BY "," //分割符用的是，
(ENAME,JOB,SAL) //制定要加载到表中的列名


BEGINDATA //以下部分为要加载的数据
TOM,EMP,100
LALY,EMP,110
TOKE,EMP,120
JONE,EMP,130
JECL,MGR,140
JECK,HR,150
KULI,MGR,160


以scott用户创建bonus表结构
SQL> conn scott/oracle
Connected.
SQL> create table bonus(ENAME varchar(20),JOB varchar(15),SAL number);


Table created.


开始加载数据：
[oracle@orcl2 myscript]$ sqlldr scott/oracle control=sqlldr1.ctl


SQL*Loader: Release 10.2.0.1.0 - Production on Tue Nov 19 10:21:58 2013


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


Commit point reached - logical record count 8


数据加载完毕，查看通目录下的文件：
[oracle@orcl2 myscript]$ ls
sqlldr1.ctl  sqlldr1.log
发现多出了一个.log文件，没有.bad文件和.dsc文件说明此次加载数据无任何的错误


在scott用户下查看表中的数据信息
SQL> select * from bonus;


ENAME                JOB                    SAL
-------------------- --------------- ----------
TOM                  EMP                    100
LALY                 EMP                    110
TOKE                 EMP                    120
JONE                 EMP                    130
JECL                 MGR                    140
JECK                 HR                     150
KULI                 MGR                    160


7 rows selected.
发现7条数据已完全加载如表中




从excel表中向数据库导入数据：


拿到一个excel表格，首先复制一份表格出来，以下操作均在备份表格中，以防原数据损坏


得到复制excel表格后，将其列头和无关数据删除后，点击文件->另存为->保存类型选择csv(次格式的表格可以用记事本打开，数据间用的是逗号分隔符)，以记事本打开刚保存的csv的表格即得到你要的数据，此处我所得到的测试数据如下：
USER1,EMP,100
USER2,EMP,101
USER3,EMP,102
USER4,EMP,103
USER5,EMP,104
USER6,EMP,105
USER7,MGR,106
USER8,MGR,107
USER9,HR,108


编辑名为sqlldr2.ctl的控制文件：
LOAD DATA
INFILE sqlldr2.dat       //指定加载的数据文件路径
APPEND INTO TABLE BONUS
FIELDS TERMINATED BY ","
(ENAME,JOB,SAL)
注：若要加载数据中含有自己指定的分隔符，可以在FIELDS TERMINATED BY ","后加上OPTIONALLY ENCLOSED BY '"'指定"为定界符
图目录下编辑名为sqlldr2.dat的数据文件，加入刚才得到的逗号分隔符的数据
USER1,EMP,100
USER2,EMP,101
USER3,EMP,102
USER4,EMP,103
USER5,EMP,104
USER6,EMP,105
USER7,MGR,106
USER8,MGR,107
USER9,HR,108
开始加载数据：
[oracle@orcl2 sqlldr2]$ sqlldr scott/oracle control=sqlldr2.ctl 


SQL*Loader: Release 10.2.0.1.0 - Production on Tue Nov 19 10:45:40 2013


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


Commit point reached - logical record count 9
查看表中数据是否添加成功
SQL> select * from bonus;


ENAME                JOB                    SAL
-------------------- --------------- ----------
TOM                  EMP                    100
LALY                 EMP                    110
TOKE                 EMP                    120
JONE                 EMP                    130
JECL                 MGR                    140
JECK                 HR                     150
KULI                 MGR                    160
USER1                EMP                    100
USER2                EMP                    101
USER3                EMP                    102
USER4                EMP                    103


ENAME                JOB                    SAL
-------------------- --------------- ----------
USER5                EMP                    104
USER6                EMP                    105
USER7                MGR                    106
USER8                MGR                    107
USER9                HR                     108


16 rows selected.


加载无分隔符的数据


导出测试数据
SQL> set linesize 60
SQL> set feedback off
SQL> set heading off
SQL> spool /u01/myscript/sqlldr3/sqlldr3.dat
SQL> select * from bonus;
TOM                  EMP                    100
LALY                 EMP                    110
TOKE                 EMP                    120
JONE                 EMP                    130
JECL                 MGR                    140
JECK                 HR                     150
KULI                 MGR                    160
USER1                EMP                    100
USER2                EMP                    101
USER3                EMP                    102
USER4                EMP                    103
USER5                EMP                    104
USER6                EMP                    105
USER7                MGR                    106
USER8                MGR                    107
USER9                HR                     108
SQL> spool off
SQL> set heading on
SQL> set feedback on


查看导出结果
[oracle@orcl2 sqlldr3]$ cat sqlldr3.dat 
SQL> select * from bonus;
TOM                  EMP                    100             
LALY                 EMP                    110             
TOKE                 EMP                    120             
JONE                 EMP                    130             
JECL                 MGR                    140             
JECK                 HR                     150             
KULI                 MGR                    160             
USER1                EMP                    100             
USER2                EMP                    101             
USER3                EMP                    102             
USER4                EMP                    103             
USER5                EMP                    104             
USER6                EMP                    105             
USER7                MGR                    106             
USER8                MGR                    107             
USER9                HR                     108       
将首行的SQL语句删除 
将原表中的数据删除
SQL> truncate table bonus;


Table truncated.


SQL> select * from bonus;


no rows selected


编写控制文件sqlldr3.ctl内容如下：
LOAD DATA
INFILE sqlldr3.dat
TRUNCATE INTO TABLE BONUS
(
ENAME POSITION(1:15),     //指定截取从第一个字符开始到第十五个结束，其间的字符作为ENAME加载到数据库，下同
JOB POSITION(22:40),
SAL POSITION(44:60)
)
导入数据：
[oracle@orcl2 sqlldr3]$ sqlldr scott/oracle control=sqlldr3.ctl 


SQL*Loader: Release 10.2.0.1.0 - Production on Tue Nov 19 11:27:22 2013


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


Commit point reached - logical record count 16
[oracle@orcl2 sqlldr3]$ ls
sqlldr3.ctl  sqlldr3.dat  sqlldr3.log


查看数据导入情况


SQL> select * from bonus;


ENAME                JOB                    SAL
-------------------- --------------- ----------
TOM                  EMP                    100
LALY                 EMP                    110
TOKE                 EMP                    120
JONE                 EMP                    130
JECL                 MGR                    140
JECK                 HR                     150
KULI                 MGR                    160
USER1                EMP                    100
USER2                EMP                    101
USER3                EMP                    102
USER4                EMP                    103


ENAME                JOB                    SAL
-------------------- --------------- ----------
USER5                EMP                    104
USER6                EMP                    105
USER7                MGR                    106
USER8                MGR                    107
USER9                HR                     108


16 rows selected.




16行数据完全导入成功！


未完待续。。。

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

转载于:http://blog.itpub.net/29320885/viewspace-776950/