ORACLE批量更新
提示:文章为项目一些总结
前言
提示:以下是本篇文章正文内容,下面案例可供参考
一、批量更新数据Oracle脚本?
示例:批量更新sql语句。
UPDATE table SET money =
(case
where id = '1' then money + 100
where id = '2' then money + 100
where id = '3' then money + 100
end),
repay_money =
(case
where id = '1' then repay_money - 100
where id = '2' then repay_money - 100
where id = '3' then repay_money - 100
end)
WHERE id in (1,2,3)
二、mapper.java接口代码
mapper.java代码如下(示例):
public void frozenLimitList(@Param("list") List<CreditDo> creditDo, @Param("tenantId") String tenantId)
三、map.xml文件
<update id="frozenLimitList" parameterType="java.util.List">
update table credit_personal_limit_detail set frozen_amount =
<foreach collection="list" item="item" open="(case" close="end)" separator="">
when credit_limit_id = #{item.creditLimitId, jdbcType=VARCHAR} then frozen_amount + #{item.frozenAmount, jdbcType=NUMERIC}
</foreach>
,update_time =
<foreach collection="list" item="item" open="(case" close="end)" separator="">
when credit_limit_id = #{item.creditLimitId, jdbcType=VARCHAR} then #{item.updateTime, jdbcType=DATE}
</foreach>
where tenant_id = #{tenantId, jdbcType=VARCHAR} and credit_limit_id in
<foreach collection="list" item="items" open="(" close=")" separator=",">
#{items.creditLimitId, jdbcType=VARCHAR}
</foreach>
</update>
该处使用的url网络请求的数据。
总结
提示:Oracle批量更新小结