Mybatis+Oracle批量插入(自动过滤重复数据)与删除

       在DAO层Mapper方法中参数为List<roleuser>

<!--插入用户角色信息  -->

    <insert id="insertUserRole" parameterType="ArrayList" >
    insert into usi_user_role (user_id,role_id)
    <foreach collection="list" item="item" index="index" separator="union all">
    select #{item.userid,jdbcType=VARCHAR},
    #{item.roleid,jdbcType=VARCHAR} from dual  where not exists(select * from usi_user_role
     where user_id = #{item.userid,jdbcType=VARCHAR}
       and role_id = #{item.roleid,jdbcType=VARCHAR})
    </foreach>

  </insert>
 
    <!-解除用户角色信息  -->
    <delete id="relieveUserRole" parameterType="ArrayList" >
    delete from usi_user_role A  
  where exists(   
   select role_id from(  
    <foreach collection="list" item="item" index="index" separator="union all">    
     select  B.* from usi_user_role B where 1=1 and B.user_id = #{item.userid} and B.role_id = #{item.roleid}     
    </foreach>  
    )S where  A.role_id=S.role_id  
  )  
  </delete>