热门标签 | HotTags
当前位置:  开发笔记 > 数据库 > 正文

Oracle数据库的Load详解

--常规load方式----load.ctl文件loaddatainfile

--常规load方式----load.ctl文件load datainfile

--常规load方式
----load.ctl文件
load data
infile 'c:\1.txt'
into table dept
fields terminated by ','
(deptno,dname,loc)


----数据如下
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia


----建表
create table dept
(
deptno number(2),
dname varchar2(14),s
loc varchar2(13)
)


sqlldr userid='sys/test as sysdba' cOntrol='c:\load.ctl' log='c:\log.log'


--sqlldr收到'exceeds maximum length',因为输入文件的字符串默认是255,超过即报错
alter table dept modify dname varchar2(1000);


----load.ctl文件
load data
infile 'c:\1.txt'
into table dept
fields terminated by ','
(deptno,dname,loc)


----数据如下
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Financeaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa,Virginia


sqlldr userid='sys/test as sysdba' cOntrol='c:\load.ctl' log='c:\log.log'
报错:'exceeds maximum length'


----修改load.ctl
Load data
infile 'c:\1.txt'
into table dept
fields terminated by ','
(deptno,dname char(1000),loc)


sqlldr userid='sys/test as sysdba' cOntrol='c:\load.ctl' log='c:\log.log'


--如何加载定界符
Load data
infile 'c:\1.txt'
into table dept
fields terminated by ',' optionally enclosed by '"'
(deptno,dname char(1000),loc)


----数据如下
10,Sales,"Virginia,USA"
20,Accounting,"Va,""Virginia"""
30,Consulting,Virginia
40,Finance,Virginia


----结果如下
10,Sales,Virginia,USA
20,Accounting,Va,"Virginia"
30,Consulting,Virginia
40,Finance,Virginia


----修改load.ctl
Load data
infile 'c:\1.txt'
into table dept
fields terminated by X'07' optionally enclosed by '"'
(deptno,dname char(1000),loc)


sqlldr userid='sys/test as sysdba' cOntrol='c:\load.ctl' log='c:\log.log'


----数据如下,用X'07'做分割符
10Sales"Virginia,USA"
20Accounting"Va,""Virginia"""
30ConsultingVirginia
40FinanceVirginia


----结果如下
10SalesVirginia,USA
20AccountingVa,"Virginia"
30ConsultingVirginia
40FinanceVirginia


--加载日期
alter table dept add last_update date;


----修改load.ctl
Load data
infile 'c:\1.txt'
into table dept
fields terminated by X'07' optionally enclosed by '"'
(deptno,dname char(1000),loc,
last_update Date "yyyy-mm-dd")sqlldr userid='sys/test as sysdba' cOntrol='c:\load.ctl' log='c:\log.log'----数据如下
10Sales"Virginia,USA""2008-01-01"
20Accounting"Va,""Virginia""""2008-01-01"
30ConsultingVirginia"2008-01-01"
40FinanceVirginia"2008-01-01"


--使用函数
alter table dept add entire_line varchar2(200);


----修改load.ctl
Load data
infile 'c:\1.txt'
into table dept
fields terminated by X'07' optionally enclosed by '"'
trailing nullcols
(deptno,
dname char(1000) "upper(:dname)",
loc "upper(:loc)",
last_update Date "yyyy-mm-dd",
entire_line ":deptno||:dname||:last_update")sqlldr userid='sys/test as sysdba' cOntrol='c:\load.ctl' log='c:\log.log'----数据如下
10Sales"Virginia,USA""2008-01-01"
20Accounting"Va,""Virginia""""2008-01-01"
30ConsultingVirginia"2008-01-01"
40FinanceVirginia"2008-01-01"


--如何加载内嵌换行符


----修改load.ctl
Load data
infile 'c:\1.txt'
into table dept
fields terminated by X'07' optionally enclosed by '"'
trailing nullcols
(deptno,
dname char(1000) "upper(:dname)",
loc "upper(:loc)",
last_update Date "yyyy-mm-dd",
entire_line "replace(:entire_line,'\\n','chr(10)')")sqlldr userid='sys/test as sysdba' cOntrol='c:\load.ctl' log='c:\log.log'


----数据如下
10Sales"Virginia,USA""2008-01-01"this is \n aaa
20Accounting"Va,""Virginia""""2008-01-01"this is \n aaa
30ConsultingVirginia"2008-01-01"this is \n aaa
40FinanceVirginia"2008-01-01"this is \n aaa


select utl_raw.cast_to_raw(chr(6)||chr(13)||chr(10)) from dual
------------
'060D0A'


----修改load.ctl
Load data
infile 'c:\1.txt' "str X'060D0A'"
into table dept
fields terminated by X'07' optionally enclosed by '"'
trailing nullcols
(deptno,
dname char(1000) "upper(:dname)",
loc "upper(:loc)",
last_update Date "yyyy-mm-dd",
entire_line)sqlldr userid='sys/test as sysdba' cOntrol='c:\load.ctl' log='c:\log.log'

----数据如下
10Sales"Virginia,USA""2008-01-01"this is aaa
20Accounting"Va,""Virginia""""2008-01-01"this is aaa
30ConsultingVirginia"2008-01-01"this is aaa

40FinanceVirginia"2008-01-01"this is aaa


推荐阅读
author-avatar
消失得珍贵_Cjh_662
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有