数据库批量操作锁定和释放库存

type类型：1锁定，2释放

实现思路：利用【case when and then end】来判断锁定和释放是否超过库存限制，若是超过则SQL不成立，执行后会报错，表示锁定或释放库存失败

1.  
   <update id="addReducePreemptQuantity">
2.  
   update
3.  
   stock_real_time
4.  
   <trim prefix="set" suffixOverrides=",">
5.  
   <trim prefix="preempt_count = case" suffix="end,">
6.  
   <foreach collection="list" index="index" item="item">
7.  
   <if test="item.preemptCount != null">
8.  
   <if test="item.type == 1">
9.  
   when
10.  
    erp_stock_code = #{item.erpStockCode}
11.  
    <if test="item.ddCode != null and item.ddCode != ''">
12.  
    and dd_code = #{item.ddCode}
13.  
    </if>
14.  
    <if test="item.channelId != null and item.channelId != ''">
15.  
    and channel_id = #{item.channelId}
16.  
    </if>
17.  
    <if test="item.channelCode != null and item.channelCode != ''">
18.  
    and channel_code = #{item.channelCode}
19.  
    </if>
20.  
    <if test="item.erpSkuCode != null and item.erpSkuCode != ''">
21.  
    and erp_sku_code = #{item.erpSkuCode}
22.  
    </if>
23.  
    and
24.  
    batch_code = #{item.batchCode}
25.  
    and
26.  
    stock_count &gt;= (preempt_count #{item.preemptCount})
27.  
    then
28.  
    preempt_count #{item.preemptCount}
29.  
    </if>
30.  
    <if test="item.type == 2">
31.  
    when
32.  
    erp_stock_code = #{item.erpStockCode}
33.  
    <if test="item.ddCode != null and item.ddCode != ''">
34.  
    and dd_code = #{item.ddCode}
35.  
    </if>
36.  
    <if test="item.channelId != null and item.channelId != ''">
37.  
    and channel_id = #{item.channelId}
38.  
    </if>
39.  
    <if test="item.channelCode != null and item.channelCode != ''">
40.  
    and channel_code = #{item.channelCode}
41.  
    </if>
42.  
    <if test="item.erpSkuCode != null and item.erpSkuCode != ''">
43.  
    and erp_sku_code = #{item.erpSkuCode}
44.  
    </if>
45.  
    and
46.  
    batch_code = #{item.batchCode}
47.  
    <!-- and
48.  
    0 &lt;= (preempt_count - #{item.preemptCount}) -->
49.  
    then
50.  
    preempt_count - #{item.preemptCount}
51.  
    </if>
52.  
    </if>
53.  
    </foreach>
54.  
    </trim>
55.  
    </trim>
56.  
    where
57.  
    is_deleted = 0
58.  
    and
59.  
    (
60.  
    <foreach close=")" collection="list" item="item" open="(" separator="or ">
61.  
    erp_stock_code = #{item.erpStockCode}
62.  
    <if test="item.ddCode != null and item.ddCode != ''">
63.  
    and dd_code = #{item.ddCode}
64.  
    </if>
65.  
    <if test="item.channelId != null and item.channelId != ''">
66.  
    and channel_id = #{item.channelId}
67.  
    </if>
68.  
    <if test="item.channelCode != null and item.channelCode != ''">
69.  
    and channel_code = #{item.channelCode}
70.  
    </if>
71.  
    <if test="item.erpSkuCode != null and item.erpSkuCode != ''">
72.  
    and erp_sku_code = #{item.erpSkuCode}
73.  
    </if>
74.  
    and
75.  
    batch_code = #{item.batchCode}
76.  
    </foreach>
77.  
    )
78.  
    </update>

这篇好文章是转载于：学新通技术网