shell中sqlldr、ctl的基本使用与示例

sqlldr

在shell脚本中，向oracle数据库表插入数据

要先确认环境是否有sqlldr命令

cvs、txt文件示例

"编码","订购状态","产品编码","产品订购实例ID","订购生效时间","订购失效时间","计费开始时间","受理工号","BBOSS商品名称","商品规格编码","商品实例ID","产品名称","产品规格编码","产品订购实例ID"
"3511143538","0","111601","9004714567","20181229170033","20991231235959","20181229170033","351afiKln@@351","","","","","",""
"3526218531","0","111601","9004704255","20181227111850","20991231235959","20181227111850","351gkka9M@@351","","","","","",""
"3510068521","0","111789","9004703175","20181226205029","20991231235959","20181226205029","bbss_ecopinter","","","","","",""
"3513509860","0","111800","9004702728","20181226183348","20991231235959","20181226183348","351glf7Yk@@351","","","","","",""
"3521168039","0","111716","9004702545","20181226173146","20991231235959","20181226173146","bbss_ecopinter","","","","","",""

CTL文件示例

load data CHARACTERSET ZHS16GBK
infile 'xxxxxx.csv'
truncate into Table Table_Name 
fields terminated by "," optionally enclosed by '"' TRAILING NULLCOLS
(
 	CUSTOMERNUMBER ENCLOSED BY '"',
	STATE ENCLOSED BY '"',
	PRODID ENCLOSED BY '"',
	PRODUCTID ENCLOSED BY '"' ,
	EFFDATE ENCLOSED BY '"' "to_date(:EFFDATE, 'yyyymmddhh24miss')",
	EXPDATE ENCLOSED BY '"' "to_date(:EXPDATE, 'yyyymmddhh24miss')",
	ZFJFDATE ENCLOSED BY '"' "to_date(:ZFJFDATE, 'yyyymmddhh24miss')",
	LOGIN_NO ENCLOSED BY '"',
	BBOSS_GOODS_NAME ENCLOSED BY '"',
	BBOSS_GOODS_ATTRCODE ENCLOSED BY '"',
	BBOSS_GOODS_PRODUCTID ENCLOSED BY '"',
	BBOSS_PROD_NAME ENCLOSED BY '"',
	BBOSS_PROD_ATTRCODE ENCLOSED BY '"',
	BBOSS_PROD_PRODUCTID ENCLOSED BY '"',
  OP_TIME sysdate
)

解释

INFILE：需要导入的数据文件名+路径txt或者csv格式

truncate：插入数据的方式

APPEND：追加

INSERT：空表插入，非空报错

REPLACE：替换，先删除（delete）表数据，再插入

TRUNCATE：替换，先删除（TRUNCATE）表数据，再插入

into Table PD_ORDER_INFO_TEMP：目标的表名

fields terminated by "," 表示【INFILE】列值使用","进行分割

optionally enclosed by '"' 表示【INFILE】中列值使用""包裹

TRAILING NULLCOLS：如要导入源文件此列内容为空，在导入到表中时，此列内容就是null

( ........) ：括号内为目标表里的字段名，对于date和timestamp的数据类型需要注意,需要转一下数据类型

注意

当通过SQLLDR导入数据TXT文件时，每个字段的最大值不能超过255，当数据库中原有数据长度超过255时，需要在控制文件中将这个字段char(2000)

(d1 ,
d2 char(2000)
)

Shell脚本sqldr示例

sqlldr user/password@数据库名 control=xxxx.ctl skip=1 load=200000 errors=100 rows=1000  bindsize=33554432 bad=E:/bad.bad   log = E:/ log.log

user/password //数据库的用户名密码

control //sqlldr控制文件位置

skip=1 //表示跳过第一行，从第二行开始导入

load=200000 //表示并不导入所有的数据，只导入跳过skip参数后的200000条数据

rows=1000 //表示一次加载的行数，默认值为64，此处设置为1000

errors=100 //表示出错100次后，停止加载

bindsize=33554432 //表示每次提交记录缓冲区的大小，默认256k

bad = E:/bad.bad --- 当导入文件出错时就会在 E盘生成.bad 的文件 ， 里面会记录你出错误没有导入的数据

log = E:/log.log -- 当导入文件 ， 会记录你导入文件详细的内容 ， 包括出错的信息