mybaits+oracle批量操作

批量插入
对于没有sequence可以这么做

<insert id = “insert”parameterType = “java.util.List”>
  Insert  into xxx_I(no,name,age)
<foreach collection = “list” item=”item”index = “index” separator=”union  all”>
  Select
       #{item.no,jdbcType=VARCHAR}as no,
       #{item.name,jdbcType=VARCHAR} as name,
       #{item.age,jdbcType=VARCHAR} as age
  From dual
 </foreach>
</insert>

那么对于有sequence的话可以像以下那么做（no_seq 为no 的 sequence）

<insert id = “insert”parameterType = “java.util.List”>
  Insert  into xxx_I(no,name,age)
  select no_seq.nextval，
   t.name,
   t.age
  from
 <foreach collection = “list” item=”item”index = “index” open="(" close=")t" separator=”union  all”>
  Select
       #{item.name,jdbcType=VARCHAR} as name,
       #{item.age,jdbcType=VARCHAR} as age
  From dual
 </foreach>
</insert>

批量更新：

<update id= "update" parameterType= "java.util.List">
 begin  
<foreach collection="list"  item="item"  index="index" separator=";">  
 update xxx
  <set> 
    name = #{item.name,jdbcType=VARCHAR},
    age = #{item.age,jdbcType=VARCHAR}  
  </set> 
<where>
    no = #{item.no,jdbcType=VARCHAR}
</where>
</foreach>
;end;
</update>
 

多条件批量删除：

<delete id= "delete" parameterType= "java.util.List">
  delete xxx x where exists(	
    select 1 from(
 <foreach collection="list" item="item" index="index" separator="union all">
     select * from xxx where 
        name = #{item.name,jdbcType=VARCHAR} 
       and age = #{item.age,jdbcType=VARCHAR}
 </foreach>
  )s where s.no = x.no
 )
</delete>

单条件批量删除：

<delete id= "delete" parameterType= "java.util.List">
  delete xxx x where no in(	
 <foreach collection="list" item="item" index="index" separator=",">
     #{item.no,jdbcType=VARCHAR}
 </foreach>
</delete>