利用sql load加载txt，csv及图片到数据库
Sep152011
朋友打电话问我如何利用sql讲mysl导出的文本数据加载到oracle数据库，电话说了他也没听清楚，我给他做了几个例子：
一、加载txt文本
1.在D盘建立sqlldr的控制文件，111.ctl 内容如下
LOAD DATA INFILE 'd:111.txt' INTO TABLE SALGRADE_TEST TRUNCATE FIELDS TERMINATED BY ',' (GRADE,LOSAL,HISAL)
2.在D盘建立sqlldr的数据文件111.txt，内容如下格式
1,700,1200 2,1201,1400 3,1401,2000 4,2001,3000 5,3001,9999
3.在CMD的命令行模式执行加载操作
sqlldr scott/tiger control=d:111.ctl log=d:111.log bad=d:111.ba SQL*Loader: Release 11.2.0.1.0 - Production on 星期三 9月 14 10:58:35 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved 达到提交点 - 逻辑记录计数 4 达到提交点 - 逻辑记录计数 5
4.登录数据库验证加载结果
D:windowservermysql-5.1.51bin> sqlplus scott/tiger 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select * from SALGRADE_TEST; GRADE LOSAL HISAL ---------- ---------- ---------- 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999
5.导入成功之后，查看111.log
6.注释
LOAD DATA 控制文件标识
INFILE 'd:111.txt'为数据文件地址和名称，此处如果是INFILE *表示要加载的数据就在
这个控制文件里，也就是BEGINDATA后面跟的内容。详见7
INTO TABLE SALGRADE_TEST要往SALGRADE_TEST表里加载数据
TRUNCATE如果表里有数据，先truncate表,然后再加载，TRUNCATE还可换成其他值，比如
insert，为缺省方式，在数据装载开始时要求表为空，如果表中有数据，会报错而终止。
SQL*Loader-601: 对于 INSERT 选项, 表必须为空。表 SALGRADE_TEST 上出错
append，在表中追加新记录，如果。表上有主键和唯一性约束，新加载的数据的相关字段值不能重复
replace，删除旧记录，替换成新装载的记录 ，要求有delete权限
truncate，同上，要求有truncate权限
FIELDS TERMINATED BY ',' 数据以逗号分隔，如果是TERMINATED BY WRITESPACE表示以空白分
隔，如果不指定FIELDS TERMINATED BY ','，需要为每一列都指定分隔符，如下：
(GRADE [interger external] TERMINATED BY ',', LOSAL [interger external] TERMINATED BY ',', HISAL [interger external] TERMINATED BY ',')
例如：
LOAD DATA INFILE 'd:111.txt' TRUNCATE INTO TABLE SALGRADE_TEST (GRADE TERMINATED BY ',', LOSAL TERMINATED BY ',', HISAL TERMINATED BY ',')
(GRADE,LOSAL,HISAL) 要加载到表中的这些字段，字段顺序需要和数据文件111.txt内的数据对应，如果单独为每一列都指定了分隔符，此部分就不需要了。
7.如果数据量不大，可以讲sqlload的数据文件（111.txt）集成到sqlload的控制文件（111.ctl）内，例如：
LOAD DATA INFILE * TRUNCATE INTO TABLE SALGRADE_TEST FIELDS TERMINATED BY ',' (GRADE,LOSAL,HISAL) BEGINDATA 1,700,1200 2,1201,1400 3,1401,2000 4,2001,3000 5,3001,9999
加载
D:>sqlldr scott/tiger control=d:111.ctl SQL*Loader: Release 11.2.0.1.0 - Production on 星期三 9月 14 17:03:53 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 达到提交点 - 逻辑记录计数 4 达到提交点 - 逻辑记录计数 5
登录数据库验证加载结果
D:> sqlplus scott/tiger 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SELECT * FROM SALGRADE_TEST; GRADE LOSAL HISAL ---------- ---------- ---------- 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999
二、加载csv格式的数据
1.建立测试表
D:>sqlplus scott/tiger 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create table dept_test as select * from dept where 1=2; 表已创建。
2.建立sqlload的数据文件111.csv，内容如下
10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
3.建立sqlload的控制文件111.ctl，内容如下
LOAD DATA INFILE 'd:111.csv' TRUNCATE INTO TABLE DEPT_TEST FIELDS TERMINATED BY ',' (DEPTNO,DNAME,LOC)
4.执行加载操作
D:>sqlldr scott/tiger control=d:111.ctl log=d:111.log SQL*Loader: Release 11.2.0.1.0 - Production on 星期四 9月 15 09:47:44 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 达到提交点 - 逻辑记录计数 4
5.登录数据库查询
D:> sqlplus scott/tiger 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select * from dept_test; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
三、加载图片到数据库
1.创建测试表，由于图片在数据库中以blob的形式保存，这里是有了11g的新特性，securefiles LOB的形式存储图片。
SQL> create table t_load_sf (id number, full_name varchar2(100), 2 create_date date, contents blob, constraint pk_t_load_sf primary key(id)) 3 lob (contents) store as securefile; 表已创建。
2.创建sqlload的数据文件111.txt，内容如下：
1,DSC00086.JPG 2,DSC00122.JPG 3,DSC00145.JPG 4,DSC00146.JPG 5,psu.jpg
3.创建sqlload的控制文件111.ctl，内容如下：
LOAD DATA INFILE '111.txt' INTO TABLE T_LOAD_SF TRUNCATE FIELDS TERMINATED BY ',' (ID CHAR(255), FULL_NAME CHAR(255), CREATE_DATE SYSDATE, CONTENTS LOBFILE(FULL_NAME) TERMINATED BY EOF)
4.加载
D:test> sqlldr scott/tiger control=D:111.ctl log=d:111.log SQL*Loader: Release 11.2.0.1.0 - Production on 星期四 9月 15 11:29:13 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 达到提交点 - 逻辑记录计数 5
5.登录数据库验证
SQL> select * from t_load_sf where rownum=1; ID FULL_NAME CREATE_DATE CONTENTS -------- --------------- -------------- ----------------------------------- 1 DSC00086.JPG 15-9月 -11 FFD8FFE1538045786966000049492A00080 000000B000E01020020000000920000000F 01020005000000B20000001001020008000 000B8000000120103000100000001000000 1A01050001000000C000
如果使用plsqldev工具，可以看到加载进数据库的图片
利用sql load加载txt，csv及图片到数据库：等您坐沙发呢！
发表评论
