批量删除
delete from jc_kt where ktid in
<foreach item="ktid" collection="array" open="(" separator="," close=")">
#{ktid}
</foreach>
批量修改
<foreach item="ckryid" collection="array" separator=";">
update jc_ry set zt=0,JSSJ=now(),CZSJ=now() where ckryid = #{ckryid}
</foreach>
批量插入
方法一
insert into jc_wl_gys_lxfx (gyslxid,gyslxido,flag,kssj,jssj,zt,czsj,czrid,
czr,gysid,lxr,sjhm,lxdh,dzyx,px) values
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.gyslxid},
#{item.gyslxido},
#{item.flag},
#{item.kssj},
#{item.jssj},
#{item.zt},
#{item.czsj},
#{item.czrid},
#{item.czr},
#{item.gysid},
#{item.lxr},
#{item.sjhm},
#{item.lxdh},
#{item.dzyx},
#{index}
)
</foreach>
方法二 ,oracle数据库支持dual
insert into role_flow (rfid, roleid,rolename, lcslid, nodeid, flowsign)
<foreach collection="list" item="item" index="index" separator="union all">
(
select
#{item.rfid},
#{item.roleid},
#{item.rolename},
#{item.lcslid},
#{item.nodeid},
#{item.flowsign}
from dual
)
</foreach>
方法三 oracle实现(生成一条sql执行,效率高)
insert all
<foreach item="item" index="index" collection="list">
into scgl_byzxxmpz
<trim prefix="(" suffix=")" suffixOverrides=",">
byzxxmpzid,
byzxxmpzido,
flag,
kssj,
zt,
czsj,
czrid,
czr,
jxxmid,
<if test="item.jhxz != null ">jhxz,</if>
<if test="item.clmc != null ">clmc,</if>
<if test="item.clgg != null and item.clgg != '' ">clgg,</if>
<if test="item.clsl != null and item.clsl != '' ">clsl,</if>
<if test="item.wxgw != null ">wxgw,</if>
<if test="item.wxgz != null ">wxgz,</if>
byzxid
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
#{item.byzxxmpzid},
#{item.byzxxmpzido},
#{item.flag},
#{item.kssj},
#{item.zt},
#{item.czsj},
#{item.czrid},
#{item.czr},
#{item.jxxmid},
<if test="item.jhxz != null ">#{item.jhxz},</if>
<if test="item.clmc != null ">#{item.clmc},</if>
<if test="item.clgg != null and item.clgg != '' ">#{item.clgg},</if>
<if test="item.clsl != null and item.clsl != '' ">#{item.clsl},</if>
<if test="item.wxgw != null ">#{item.wxgw},</if>
<if test="item.wxgz != null ">#{item.wxgz},</if>
#{item.byzxid}
</trim>
</foreach>
SELECT 1 FROM DUAL