当前位置: 首页 > Linux, oracle 10g, oracle 11g > 正文

利用sql load加载txt,csv及图片到数据库

朋友打电话问我如何利用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工具,可以看到加载进数据库的图片

本文固定链接: http://www.dbdream.com.cn/2011/09/%e5%88%a9%e7%94%a8sql-load%e5%8a%a0%e8%bd%bdtxt%ef%bc%8ccsv%e5%8f%8a%e5%9b%be%e7%89%87%e5%88%b0%e6%95%b0%e6%8d%ae%e5%ba%93/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2011年09月15日发表在 Linux, oracle 10g, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 利用sql load加载txt,csv及图片到数据库 | 信春哥,系统稳,闭眼上线不回滚!
关键字: , ,

利用sql load加载txt,csv及图片到数据库:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter