oracle sqlldr使用心得

在从A库往B库倒表数据的时候，可以使用sqlldr命令。

好处就是即使A库中的TableA表结构和B库中的TableB表结构不一样，也是可以将A表的数据导入到B表中的。前提是需要写控制文件test.ctl。

优点：TableA和TableB数据字段可以不一样。

缺点：需要些控制文件test.ctl

 

在从A库中往外倒数据的时候，最好将字段内容中有回车换行的数据进行替换，将回车换行去掉。因为后面在导入数据的时候是以一行数据作为一个导入单位的。语法如下：

replace(replace(content,CHR(10),''),CHR(13),'')  其中chr(10),chr(13)一个是回车一个是换行。

 

从A库中导出的文件内容如下：

"MONTHDAY","INVOKESN","MSGID","MEDIATYPE","SENDNO","RECEIVERNO","NEEDREPORT","SUBJECT","REPLACE(REPLACE(CONTENT,CHR(10","ACCESSORYINFO","SWSPID","SWSERVICEID","SWFEETYPE","SWFEEADDR","SWFEE","PRIVILEGE","SERIALNUM","SENDSTATE","REPORTSTATE","STAFFNO","INVOKERID","SERVICEID","UNITID","LINKID","SCHEDULETIME","INSERTIME","SENDTIME","REPORTIME","SENDWAY","CHARGED","SCANID","WSSPGID"
"0711","MSA12071100534431806","MSP1207110053445595","SM","01068926519","13401000062","true","","奔驰授权经销商北京鹏龙星徽4S店位于东南三环内，交通便利。提供奔驰车辆售后维修服务及24小时免费救援服务。114/116114","","","","","01068926519","","1","SN000000059884850","2","true","3025","114001","114001001","10667558","","","2012-07-110:53:44","2012-07-11 0:53:59","2012-07-110:54:15","MSP","","120711005359088",""
"0711","MSA12071100534431807","MSP1207110053445596","SM","01068926526","13401000062","true","","拨打114，全天提供汽车救援、拖车、搭电、送油、充气、换胎服务，汽车救援忠实守护您和爱车。更多服务登录www.114menhu.com","","","","","01068926526","","1","SN000000059884851","2","true","3025","114001","114001006","10667558","","","2012-07-110:53:44","2012-07-11 0:53:59","2012-07-110:54:26","MSP","","120711005359088",""
"0711","MSA12071100533431786","MSP1207110053345571","SM","01085294510","18741487989","true","","1/2:您已成功预约空军总医院皮肤科门诊的主治医师,预约识别码是:【36860833】,就诊日期是2012-07-31上午,取号时间当日","","","","","01085294510","","0","SN000000059884827","2","true","","100002","100002000","","","","2012-07-110:53:34","2012-07-11 0:53:59","2012-07-110:54:16","MSP","","120711005359088",""

 

控制文件书写示例如下：

load data
infile'D:\20120711_t_dz_msa_mediasend_his_2.csv'   --这是从数据库A中导出的TableA的数据文件
append into tableT_DZ_MSA_MEDIASEND_HIS   --这是将向B库中导入数据的表名称
fields terminated by ',' OPTIONALLY ENCLOSED BY'"'   --此处说明文件中字段是以逗号“，”进行分隔的

每个字段使用双引号“"”进行包裹的。
(
 MONTHDAY     ,--此为B库中表TableB 的字段名称，默认字段类型是varchar2的
 INVOKESN     ,
 MSGID        ,
 MEDIATYPE    ,
 SENDNO       ,
 RECEIVERNO   ,
 NEEDREPORT   ,
 SUBJECT      ,
 CONTENT      ,
  ACCESSORYINFO ,
 SWSPID       ,
  SWSERVICEID  ,
 SWFEETYPE    ,
 SWFEEADDR    ,
 SWFEE        ,
 PRIVILEGE    ,
 SERIALNUM    ,
 SENDSTATE    ,
  REPORTSTATE  ,
 STAFFNO      ,
 INVOKERID    ,
 SERVICEID    ,
 UNITID       ,
 LINKID       ,
  SCHEDULETIME Date "yyyy-mm-dd hh24:mi:ss",--如果TableB中的字段是Date类型那么，在此需要做一些说明，这样在导入的时候数据就会进行相应的转换了。
  INSERTIME Date "yyyy-mm-ddhh24:mi:ss"   ,
  SENDTIME Date "yyyy-mm-ddhh24:mi:ss"   ,
  REPORTIME Date "yyyy-mm-ddhh24:mi:ss"   ,
 SENDWAY      ,
 CHARGED      ,
 SCANID       ,
 WSSPGID      
)

在执行的时候需要打开命令窗口(前提是需要在机器上安装oracle客户端才能使用该命令)：

命令如下：sqlldr 用户名/密码@sid control=控制文件名

一般都是进入到控制文件所在目录然后再执行上面的命令。sid就是你在本机器上配置的数据库连接名称（tnsnames.ora中配置的）。

 

导数据还可以使用imp/exp命令。不过此命令的不足之处在于。TableA和TableB两个表的名称以及字段都得一样才可以。不过此命令不需要写控制文件。此命令的使用见其他文章。