业务测试遇到的oracle sql_控制商品数不被扣成负数 、图片地址拼装

兑换商品控制day_current_num不被扣成负数

兑换商品的时候，SQl写的很漂亮，可以实现“控制day_current_num不被扣成负数” 

SQL里面有个逻辑，判断：day_limit=0就是不限量，day_current_num=0（保持和dai_limit字段一致），只扣减current_num当前剩余量

UPDATE T_EXCHANGE_GOODS

   SET operate_date    = sysdate,

       current_num     = current_num - 1,---用户兑换商品的数量

       day_current_num = (case sign(day_limit) when 0 then 0 else day_current_num - 1 end)

 WHERE id = 11--商品ID

 

--我的兑换列表接口控制返回兑换品的图片地址，用了nvl2函数

select *
   from (select row_limit.*, rownum rownum_
           from (SELECT to_char(a.create_date, 'YYYY-MM-DD HH24:MI') create_date,
                      a.goods_name,
                       a.equal_mcoin,
                      a.equal_score,
                      b.link_url,
                      a.prop_code,
                      a.id as award_id,
                    nvl2(b.img_url, 'http://211.139.191.111:12630/rs/ppp2/' || b.img_url, b.img_url) as logo_url
                   FROM T_MCOIN_EXCHANGE a, t_exchange_goods b
                  where a.award_type_id = b.id
                    and a.mobile = '10000000756'
                    and a.partition_id = '56'
                  ORDER BY a.id DESC) row_limit
          where rownum <= 13)
  where rownum_ > 0