oracle批量更新优化

使用union

update
        IL_FREIGHT_STOCK t1
        set DETAIL_PROD_CODE= (
           select t2.detail_prod_code
            from (
               <foreach collection="list" open="(" close=")" separator="," item="item">
                   select #{item.stockId} stock_id, #{item.detailProdCode} detail_prod_code from dual
               </foreach>
            ) t2 where t2.stock_id=t1.STOCK_ID
        )
        <where>
            t1.STOCK_ID in
            <foreach collection="list" item="item" separator="," open="(" close=")">
                #{item.stockId}
            </foreach>
            <if test="loadNo != null and loadNo != ''">
                and t1.LOAD_NO=#{loadNo}
            </if>
        </where>

批量插入

insert into hr_user (id, name)
select t1.id, t2.name from (
  select '' id, '' user_no from dual
) t1
left join hr_user_info t2 on t1.id = t2.id