最近用到了Oracle的批量插入,批量插入的效率是真的高。
Oracle的批量插入可以有以下两种:
1、这种可以支持生成ID的写法,注意没有values
<insert id="insertBatchDeliverableSubmit" parameterType="cn.xx.xxx.model.PPAP">
insert into TABLE_DELIVERABLE_SUBMIT
(ID,
PPAPCODE,
DELIVERABLEID,
STATUS
)
select table_deliverable_submit_sq.NEXTVAL,a.* from(
<foreach collection="deliverIds" item="did" separator="union all">
select
#{ppapCode,jdbcType=VARCHAR},
#{did,jdbcType=BIGINT},
'4'
from dual
</foreach>
) a
</insert>
2、这种不支持生成ID,不能在id的位置写上SEQ.NEXTVAL
。或者你可以单独写insert触发器,注意这个有values。
insert all
into TABLE_DELIVERABLE_SUBMIT(ID,OTSCODE,DELIVERABLEID,STATUS) values(1,'OTS001',41,'4')
into TABLE_DELIVERABLE_SUBMIT(ID,OTSCODE,DELIVERABLEID,STATUS) values(2,'OTS001',21,'4')
into TABLE_DELIVERABLE_SUBMIT(ID,OTSCODE,DELIVERABLEID,STATUS) values(3,'OTS001',22,'4')
into TABLE_DELIVERABLE_SUBMIT(ID,OTSCODE,DELIVERABLEID,STATUS) values(4,'OTS001',23,'4')
select 1 from dual;