最近写了一个关于销售返现的存储过程，与大家分享一下

一、销售返现的存储过程

我也是很长时间没有写存储过程了，最近也是项目场景需要，在网上查了一些相关资料，也写了一下，有不足的地方希望大家指出来，感谢！

SQL语句：

DROP PROCEDURE IF EXISTS PRO_PAY_BONUS;

DELIMITER $$

CREATE PROCEDURE PRO_PAY_BONUS(IN lowestArea int,in otherArea1 int,in otherArea2 int)

BEGIN

#select "三分区销售额最低区域:"+lowestArea;

# 1.获取三分区返现最新配置数据。

select @lowerAreaRebate:= lower_area_rebate_ratio,

@lotteryThreshold:= lottery_mini_threshold,

@lotteryTimes:= others_area_lottery_times

from mall_tg_three_area_rebate where retbate_status=1 order by effect_date desc limit 1;

#select @lowerAreaRebate;

#select @lotteryThreshold;

#select @lotteryTimes;

# 2.处理三分区销售额最低区域的用户列表

begin

declare user_id bigint(20);#用户ID

declare price decimal(10,2);#用户金额

declare tmp_price decimal(10,2);#临时用户金额

declare cnt int default 0;#定义判断标识符

# 创建结束标志变量

declare done int default false;

# 创建游标 获取user_id和price的集合

declare lowSalesAreaUser cursor for select b.user_id,sum(a.price) as price from

mall_gd_order_goods a left join mall_gd_order b on a.order_id = b.id

where a.special_area = lowestArea and b.user_id is not null group by b.user_id;

# 指定游标循环结束时的返回值

declare continue HANDLER for not found set done = true;

# 打开游标

open lowSalesAreaUser;

# 开始循环游标里的数据

read_loop:LOOP

# 根据游标当前指向的一条数据 插入到上面申明的局部变量中

fetch lowSalesAreaUser into user_id,price;

# 判断游标的循环是否结束

IF done THEN

LEAVE read_loop;

END IF;

################################## 发放奖励:销售区域最低的用户返现 #############################

set tmp_price = price*@lowerAreaRebate;

select user_id,tmp_price;

if tmp_price > 0 then

###############返现用户插入用户流水表：mall_user_capital_flow

insert into mall_user_capital_flow(flow_type,expenses_receipts_type,amount,user_id,create_time)

values(1,0,tmp_price,user_id,now());

###############返现用户账户表:mall_user_account

SET SQL_SAFE_UPDATES = 0;

select count(1) into cnt from mall_user_account ua where ua.user_id = user_id;

if(cnt > 0) then

update mall_user_account set available_amount=available_amount + tmp_price,

can_reflect_amount = can_reflect_amount + tmp_price where user_id = user_id;

else

insert into mall_user_account(user_id,available_amount,can_reflect_amount,create_time)

values(user_id,tmp_price,tmp_price,now());

end if;

end if;

# 有loop 就一定要有end loop

END LOOP;

# 关闭游标

close lowSalesAreaUser;

end;

# 3.处理其他两个区域的用户列表

begin

declare user_id bigint(20);#用户ID

declare price decimal(10,2);#用户金额

declare times int default 0;#临时用户金额

declare cnt int default 0;#定义判断标识符

# 创建结束标志变量

declare done int default false;

# 创建游标 获取user_id和price的集合

declare lowSalesAreaUser cursor for select b.user_id,sum(a.price) as price from

mall_gd_order_goods a left join mall_gd_order b on a.order_id = b.id

where (a.special_area = otherArea1 or a.special_area = otherArea2) and b.user_id is not null

group by b.user_id;

# 指定游标循环结束时的返回值

declare continue HANDLER for not found set done = true;

# 打开游标

open lowSalesAreaUser;

# 开始循环游标里的数据

read_loop:LOOP

# 根据游标当前指向的一条数据 插入到上面申明的局部变量中

fetch lowSalesAreaUser into user_id,price;

# 判断游标的循环是否结束

IF done THEN

LEAVE read_loop;

END IF;

################################## 赠送抽奖次数：（其他2个区的用户送通证次数，见业务规则：抽奖） #############################

if price > @lotteryTimes then set times = price/@lotteryTimes;

elseif price>= @lotteryThreshold then set times = 1;

else set times = 0;

end if;

select user_id,times;

################ 赠送抽奖次数,其他2个区的用户送通证次数向mall_gd_prize_sources 插入数据

if times > 0 then

insert into mall_gd_prize_sources(user_id,prize_type,times,create_time) values(user_id,0,times,now());

select count(1) into cnt from mall_gd_prize_sum gp where gp.user_id = user_id;

if(cnt > 0) then

update mall_gd_prize_sum set prize_accumulation=prize_accumulation +times,surplus_times=surplus_times + times

where user_id = user_id;

else

insert into mall_gd_prize_sum(user_id,prize_accumulation,surplus_times,today_date,create_time)

values(user_id,times,times,DATE_FORMAT(now(),'%Y-%m-%d'),now());

end if;

end if;

# 有loop 就一定要有end loop

END LOOP;

# 关闭游标

close lowSalesAreaUser;

end;

END$$

DELIMITER ;

 

call PRO_PAY_BONUS(1,2,3)