分红在mysql的存储过程_Mysql 直销系统定时结算存储过程

最新推荐文章于 2022-07-28 15:28:43 发布

rwwwwwwww

最新推荐文章于 2022-07-28 15:28:43 发布

阅读量165

点赞数

文章标签：分红在mysql的存储过程

本文链接：https://blog.csdn.net/weixin_34413579/article/details/113260308

版权

2858a6a550e1?utm_campaign=maleskine&utm_content=note&utm_medium=seo_notes&utm_source=recommendation

1.png

2858a6a550e1?utm_campaign=maleskine&utm_content=note&utm_medium=seo_notes&utm_source=recommendation

1.png

执行存储过程

CALL XXXXX()

2858a6a550e1?utm_campaign=maleskine&utm_content=note&utm_medium=seo_notes&utm_source=recommendation

1.png

定时执行

2858a6a550e1?utm_campaign=maleskine&utm_content=note&utm_medium=seo_notes&utm_source=recommendation

1.png

P_LeaderAward:领导奖结算

BEGIN #领导奖

#用户 | id

DECLARE s_u_id VARCHAR(255);

#用户 | 编号

DECLARE s_u_name VARCHAR(255);

#用户 | 本期PV

DECLARE s_u_pv DOUBLE;

#用户 | 推荐人 | id

DECLARE s_i_userid VARCHAR(255);

#用户 | 推荐人 | 余额

DECLARE s_i_blance DOUBLE;

#用户 | 推荐人 | 编号

DECLARE s_i_uname VARCHAR(255);

#用户 | 推荐人 | 等级

DECLARE s_i_level VARCHAR(255);

#领导奖 | 设置 | 当前期数(格式yyyyMM,例:201711)

DECLARE s_stage INT;

#领导奖 | 设置 | 作用层数(重消用户向上a_layer层)

DECLARE a_layer INT;

DECLARE a_layer_copy INT;

#领导奖 | 设置 | 经理 | 分红点

DECLARE s_03_point DOUBLE;

#领导奖 | 设置 | 总监 | 分红点

DECLARE s_04_point DOUBLE;

#领导奖 | 设置 | 董事 | 分红点

DECLARE s_05_point DOUBLE;

#领导奖 | 设置 | 当前可得奖的最低角色

DECLARE s_get_level VARCHAR(255);

#循环是否完成

DECLARE Done INT DEFAULT 0;

定义游标的关键字：CURSOR。

定义游标cursor_names，

游标cursor_names当前指针的记录

是一个表sys_user的多行结果集

DECLARE cursor_names CURSOR FOR SELECT USER_ID,SUM(PV) AS PV FROM zx_repeat t WHERE LEADER_STATE is NULL AND STAGE = s_stage AND PV>0 GROUP BY USER_ID ORDER BY PV DESC;

#异常处理结束循环(例:select XX into XXX from tablename,XX是null就会Done=1;帮助理解http://blog.sina.com.cn/s/blog_544c72960101bvl3.html)

DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = 1;

#DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET Done=1; 这里不需要,已经重逻辑上处理了into xx 是null的情况

#设置 | 赋初值

select date_format(date_sub(curdate(),interval 1 month),'%Y%m') INTO s_stage;

SELECT ITEM_VAL INTO a_layer FROM zx_initdata where INITDATA_ID='e6e39328b9204a30a0ccb7adc67b0114';

SELECT ITEM_VAL INTO s_03_point FROM zx_initdata where INITDATA_ID='e6e39328b9204a30a0ccb7adc67b0121';

SELECT ITEM_VAL INTO s_04_point FROM zx_initdata where INITDATA_ID='e6e39328b9204a30a0ccb7adc67b0122';

SELECT ITEM_VAL INTO s_05_point FROM zx_initdata where INITDATA_ID='e6e39328b9204a30a0ccb7adc67b0123';

SET s_get_level='LEVEL_03';#本系统默认最低角色是经理

#打开游标

OPEN cursor_names;

#逐个取出当前记录字段的值，需要进行加SD操作的判断

FETCH cursor_names INTO s_u_id,s_u_pv;

#开始循环，判断是否游标已经到达了最后作为循环条件

while Done <> 1 do

#重置 | 作用层数

set a_layer_copy=a_layer;

#根据用户查询推荐人ID

SELECT INVITERID INTO s_i_userid FROM sys_user t WHERE USER_ID=s_u_id;

WHILE a_layer_copy > 0 and s_i_userid IS NOT NULL and s_i_userid <> '' and s_get_level <> 'NOLEVEL' DO

#推荐人编号、余额、等级

SELECT USERNAME,BLANCE,LEVEL INTO s_i_uname,s_i_blance,s_i_level FROM sys_user t WHERE USER_ID=s_i_userid;

CASE s_i_level

WHEN 'LEVEL_03' THEN #推荐人等级=经理

IF s_get_level='LEVEL_03' THEN

SET s_get_level='LEVEL_04';

#UPDATE sys_user set BLANCE_BONUS=BLANCE_BONUS+s_u_pv*s_03_point WHERE USER_ID=s_i_userid;

SELECT USERNAME INTO s_u_name FROM sys_user WHERE USER_ID=s_u_id;

insert into zx_bill_balance_bonus(BALANCE_BONUS_ID,USER_ID,VALUE,ADDTIME,ARRIVETIME,HANDLE_USER,TYPE,REMARKS,SURPLUS,STATE,V_TYPE) VALUES(UUID(),s_i_userid,s_u_pv*s_03_point,NOW(),'','1','BILL_08',CONCAT('领导奖定期结算 | ',s_u_name,'的重消订单 | ',s_i_uname,'作为向上',a_layer,'层的第一个经理推荐人获得领导奖'),0.0,'BILL_STATE_01',1);

END IF;

WHEN 'LEVEL_04' THEN#推荐人等级=总监

IF s_get_level='LEVEL_03' OR s_get_level='LEVEL_04' THEN

SET s_get_level='LEVEL_05';

#UPDATE sys_user set BLANCE_BONUS=BLANCE_BONUS+s_u_pv*s_04_point WHERE USER_ID=s_i_userid;

SELECT USERNAME INTO s_u_name FROM sys_user WHERE USER_ID=s_u_id;

insert into zx_bill_balance_bonus(BALANCE_BONUS_ID,USER_ID,VALUE,ADDTIME,ARRIVETIME,HANDLE_USER,TYPE,REMARKS,SURPLUS,STATE,V_TYPE) VALUES(UUID(),s_i_userid,s_u_pv*s_04_point,NOW(),'','1','BILL_08',CONCAT('领导奖定期结算 | ',s_u_name,'的重消订单 | ',s_i_uname,'作为向上',a_layer,'层的第一个总监推荐人获得领导奖'),0.0,'BILL_STATE_01',1);

END IF;

WHEN 'LEVEL_05' THEN#推荐人等级=董事

#IF s_get_level='LEVEL_03' OR s_get_level='LEVEL_04' OR s_get_level='LEVEL_05' THEN 最大级别可以不要这个IF,注释留这里为了逻辑清晰

SET s_get_level='NOLEVEL';

#UPDATE sys_user set BLANCE_BONUS=BLANCE_BONUS+s_u_pv*s_05_point WHERE USER_ID=s_i_userid;

SELECT USERNAME INTO s_u_name FROM sys_user WHERE USER_ID=s_u_id;

insert into zx_bill_balance_bonus(BALANCE_BONUS_ID,USER_ID,VALUE,ADDTIME,ARRIVETIME,HANDLE_USER,TYPE,REMARKS,SURPLUS,STATE,V_TYPE) VALUES(UUID(),s_i_userid,s_u_pv*s_05_point,NOW(),'','1','BILL_08',CONCAT('领导奖定期结算 | ',s_u_name,'的重消订单 | ',s_i_uname,'作为向上',a_layer,'层的第一个董事推荐人获得领导奖'),0.0,'BILL_STATE_01',1);

#END IF;

ELSE

#这你码CASE WHEN语法必须要一个ELSE而且ELSE后面必须要写一句话,所以后面这句就是写来没卵用的

SET s_get_level=s_get_level;

END CASE;

set a_layer_copy=a_layer_copy-1;

#根据推荐人查询下一个推荐人信息

SELECT INVITERID INTO s_i_userid FROM sys_user t WHERE USER_ID=s_i_userid;

END WHILE;

SET s_get_level='LEVEL_03';

UPDATE zx_repeat SET LEADER_STATE=1 WHERE USER_ID=s_u_id AND LEADER_STATE is NULL AND STAGE = s_stage AND PV>0;

FETCH cursor_names INTO s_u_id,s_u_pv;

end while;

#关闭游标

CLOSE cursor_names;

END

P_RedAward:分红奖结算

BEGIN #分红奖

#用户 | id

DECLARE s_u_id VARCHAR(255);

#用户 | 等级

DECLARE s_u_level VARCHAR(255);

#用户 | 本季度可分红总PV

DECLARE s_u_pv DOUBLE;

#分红奖 | 设置 | 经理 | 分红点位

DECLARE s_l3_point DOUBLE;

#分红奖 | 设置 | 总监 | 分红点位

DECLARE s_l4_point DOUBLE;

#分红奖 | 设置 | 董事 | 分红点位

DECLARE s_l5_point DOUBLE;

#分红奖 | 经理 | 人数

DECLARE s_l3_count DOUBLE;

#分红奖 | 总监 | 人数

DECLARE s_l4_count DOUBLE;

#分红奖 | 董事 | 人数

DECLARE s_l5_count DOUBLE;

#分红奖 | 经理 | 分红 | 每人

DECLARE s_l3_blance DOUBLE;

#分红奖 | 总监 | 分红 | 每人

DECLARE s_l4_blance DOUBLE;

#分红奖 | 董事 | 分红 | 每人

DECLARE s_l5_blance DOUBLE;

#循环是否完成

DECLARE Done INT DEFAULT 0;

定义游标的关键字：CURSOR。

定义游标cursor_names，

游标cursor_names当前指针的记录

是一个表sys_user的多行结果集

DECLARE cursor_names CURSOR FOR SELECT USER_ID,`LEVEL` FROM sys_user WHERE `LEVEL`>='LEVEL_03';

#异常处理结束循环(例:select XX into XXX from tablename,XX是null就会Done=1;帮助理解http://blog.sina.com.cn/s/blog_544c72960101bvl3.html)

DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = 1;

#DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET Done=1; 这里不需要,已经重逻辑上处理了into xx 是null的情况

SELECT SUM(PV) INTO s_u_pv FROM zx_repeat t WHERE RED_STATE is NULL AND PV>0;

SELECT ITEM_VAL INTO s_l3_point FROM zx_initdata where INITDATA_ID='e6e39328b9204a30a0ccb7adc67b0131';

SELECT ITEM_VAL INTO s_l4_point FROM zx_initdata where INITDATA_ID='e6e39328b9204a30a0ccb7adc67b0132';

SELECT ITEM_VAL INTO s_l5_point FROM zx_initdata where INITDATA_ID='e6e39328b9204a30a0ccb7adc67b0133';

SELECT COUNT(USER_ID) INTO s_l3_count FROM sys_user t WHERE t.`LEVEL`='LEVEL_03';

SELECT COUNT(USER_ID) INTO s_l4_count FROM sys_user t WHERE t.`LEVEL`='LEVEL_04';

SELECT COUNT(USER_ID) INTO s_l5_count FROM sys_user t WHERE t.`LEVEL`='LEVEL_05';

set s_l3_blance=FORMAT(s_u_pv*s_l3_point/s_l3_count,2);

set s_l4_blance=FORMAT(s_u_pv*s_l4_point/s_l4_count,2);

set s_l5_blance=FORMAT(s_u_pv*s_l5_point/s_l5_count,2);

#打开游标

OPEN cursor_names;

#逐个取出当前记录字段的值，需要进行加SD操作的判断

FETCH cursor_names INTO s_u_id,s_u_level;

#开始循环，判断是否游标已经到达了最后作为循环条件

while Done <> 1 AND s_u_pv IS NOT NULL do

CASE s_u_level

WHEN 'LEVEL_03' THEN #经理

insert into zx_bill_balance_bonus(BALANCE_BONUS_ID,USER_ID,VALUE,ADDTIME,ARRIVETIME,HANDLE_USER,TYPE,REMARKS,SURPLUS,STATE,V_TYPE) VALUES(UUID(),s_u_id,s_l3_blance,NOW(),'','1','BILL_081',CONCAT('分红奖定期结算 | 本期可分红总PV:',s_u_pv,' | 本期经理级别总人数:',s_l3_count,' | 本期经理级别分红拨比:',s_l3_point*100,'%'),0.0,'BILL_STATE_01',1);

WHEN 'LEVEL_04' THEN #总监

insert into zx_bill_balance_bonus(BALANCE_BONUS_ID,USER_ID,VALUE,ADDTIME,ARRIVETIME,HANDLE_USER,TYPE,REMARKS,SURPLUS,STATE,V_TYPE) VALUES(UUID(),s_u_id,s_l4_blance,NOW(),'','1','BILL_081',CONCAT('分红奖定期结算 | 本期可分红总PV:',s_u_pv,' | 本期总监级别总人数:',s_l4_count,' | 本期总监级别分红拨比:',s_l4_point*100,'%'),0.0,'BILL_STATE_01',1);

WHEN 'LEVEL_05' THEN #董事

insert into zx_bill_balance_bonus(BALANCE_BONUS_ID,USER_ID,VALUE,ADDTIME,ARRIVETIME,HANDLE_USER,TYPE,REMARKS,SURPLUS,STATE,V_TYPE) VALUES(UUID(),s_u_id,s_l5_blance,NOW(),'','1','BILL_081',CONCAT('分红奖定期结算 | 本期可分红总PV:',s_u_pv,' | 本期董事级别总人数:',s_l5_count,' | 本期董事级别分红拨比:',s_l5_point*100,'%'),0.0,'BILL_STATE_01',1);

ELSE

#这你码CASE WHEN语法必须要一个ELSE而且ELSE后面必须要写一句话,所以后面这句就是写来没卵用的

SET s_u_pv=s_u_pv;

END CASE;

FETCH cursor_names INTO s_u_id,s_u_level;

end while;

UPDATE zx_repeat SET RED_STATE=1 WHERE RED_STATE is NULL AND PV>0;

#关闭游标

CLOSE cursor_names;

END

P_RepeatAward:重消奖结算

BEGIN #重消奖

#用户 | id

DECLARE s_u_id VARCHAR(255);

#用户 | 编号

DECLARE s_u_name VARCHAR(255);

#用户 | 本期PV

DECLARE s_u_pv DOUBLE;

#用户 | 重消奖金额

DECLARE s_u_blance DOUBLE;

#用户 | 推荐人 | id

DECLARE s_i_userid VARCHAR(255);

#用户 | 推荐人 | 本期PV

DECLARE s_i_pv DOUBLE;

#用户 | 推荐人 | 余额

DECLARE s_i_blance DOUBLE;

#用户 | 推荐人 | 编号

DECLARE s_i_uname VARCHAR(255);

#重消奖 | 当前期数(格式yyyyMM,例:201711)

DECLARE s_stage INT;

#重消奖 | 设置 | 获得比例(用户本期重消PV*a_amoumt即重消奖金额)

DECLARE a_amoumt DOUBLE;

#重消奖 | 设置 | 获得资格(每期必须购买重消PV>=a_standard)

DECLARE a_standard DOUBLE;

#重消奖 | 设置 | 作用层数(重消用户向上a_layer层)

DECLARE a_layer INT;

DECLARE a_layer_copy INT;

#循环是否完成

DECLARE Done INT DEFAULT 0;

定义游标的关键字：CURSOR。

定义游标cursor_names，

游标cursor_names当前指针的记录

是一个表sys_user的多行结果集

DECLARE cursor_names CURSOR FOR SELECT USER_ID,SUM(PV) AS PV FROM zx_repeat t WHERE REPEAT_STATE is NULL AND STAGE = s_stage AND PV>0 GROUP BY USER_ID ORDER BY PV DESC;

#异常处理结束循环(例:select XX into XXX from tablename,XX是null就会Done=1;帮助理解http://blog.sina.com.cn/s/blog_544c72960101bvl3.html)

DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = 1;

#DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET Done=1; 这里不需要,已经重逻辑上处理了into xx 是null的情况

SELECT ITEM_VAL INTO a_standard FROM zx_initdata where INITDATA_ID='e6e39328b9204a30a0ccb7adc67b0113';

SELECT ITEM_VAL INTO a_layer FROM zx_initdata where INITDATA_ID='e6e39328b9204a30a0ccb7adc67b0114';

SELECT ITEM_VAL INTO a_amoumt FROM zx_initdata where INITDATA_ID='e6e39328b9204a30a0ccb7adc67b0115';

select date_format(date_sub(curdate(),interval 1 month),'%Y%m') INTO s_stage;

#打开游标

OPEN cursor_names;

#逐个取出当前记录字段的值，需要进行加SD操作的判断

FETCH cursor_names INTO s_u_id,s_u_pv;

#开始循环，判断是否游标已经到达了最后作为循环条件

while Done <> 1 do

#重置 | 重消奖金额

set s_u_blance=s_u_pv*a_amoumt;

#重置 | 作用层数

set a_layer_copy=a_layer;

#根据用户查询推荐人信息

SELECT INVITERID INTO s_i_userid FROM sys_user t WHERE USER_ID=s_u_id;

WHILE a_layer_copy > 0 and s_i_userid IS NOT NULL and s_i_userid <> '' DO

SELECT USERNAME,BLANCE INTO s_i_uname,s_i_blance FROM sys_user t WHERE USER_ID=s_i_userid;

#推荐人 | 重消PV

SELECT SUM(PV) AS PV INTO s_i_pv FROM zx_repeat t WHERE USER_ID=s_i_userid AND STAGE = s_stage;

IF s_i_pv >= a_standard THEN

#推荐人 | 重消奖结算

#UPDATE sys_user set BLANCE_BONUS=BLANCE_BONUS+s_u_blance WHERE USER_ID=s_i_userid;

SELECT USERNAME INTO s_u_name FROM sys_user WHERE USER_ID=s_u_id;

#推荐人 | 收支记录

insert into zx_bill_balance_bonus(BALANCE_BONUS_ID,USER_ID,VALUE,ADDTIME,ARRIVETIME,HANDLE_USER,TYPE,REMARKS,SURPLUS,STATE,V_TYPE) VALUES(UUID(),s_i_userid,s_u_blance,NOW(),'','1','BILL_06',CONCAT('重消奖定期结算 | ',s_u_name,'的重消订单 | ',s_i_uname,'作为向上',a_layer,'层的第',a_layer_copy,'层推荐人获得重消奖'),0.0,'BILL_STATE_01',1);

end if;

set s_i_pv=0.00;

set a_layer_copy=a_layer_copy-1;

#根据推荐人查询下一个推荐人信息

SELECT INVITERID INTO s_i_userid FROM sys_user t WHERE USER_ID=s_i_userid;

END WHILE;

UPDATE zx_repeat SET REPEAT_STATE=1 WHERE USER_ID=s_u_id AND REPEAT_STATE is NULL AND STAGE = s_stage AND PV>0;

FETCH cursor_names INTO s_u_id,s_u_pv;

end while;

#关闭游标

CLOSE cursor_names;

END

P_RoseSD:SD升值

BEGIN #SD升值

#实际增涨的SD数量

DECLARE MY_SD_VALUE DOUBLE;

#总共可以增涨的SD天数

DECLARE MY_SD_ROSE_DAY DOUBLE;

#每天可增涨的SD比例

DECLARE MY_SD_ROSE_AMOUNT DOUBLE;

#最高可增值倍数

DECLARE MY_SD_MAX_MULTIPLE DOUBLE;

#当前用户SD增涨的天数

DECLARE MY_USER_SD_DAY INT;

#游标的Names

#用户ID

DECLARE MY_USER_ID VARCHAR(255);

#用户名称

DECLARE MY_USER_NAME VARCHAR(255);

#用户总SD

DECLARE MY_SD DOUBLE;

#SD基数

DECLARE MY_SD_BASE DOUBLE;

#SD增长池

DECLARE MY_SD_ROSE_POOL DOUBLE;

#循环是否完成

DECLARE Done INT DEFAULT 0;

定义游标的关键字：CURSOR。

定义游标cursor_names，

游标cursor_names当前指针的记录

是一个表sys_user的多行结果集

DECLARE cursor_names CURSOR FOR

SELECT

USER_ID,

USERNAME,

SD,

SD_BASE,

SD_ROSE_POOL

from

sys_user

where

SD_BASE>0

AND

SD_ROSE_DAY

AND

SD_ROSE_POOL

#异常处理结束循环

DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = 1;

SELECT ITEM_VAL INTO MY_SD_ROSE_DAY FROM zx_initdata where INITDATA_ID='e6e39328b9204a30a0ccb7adc67b0111';

SELECT ITEM_VAL INTO MY_SD_ROSE_AMOUNT FROM zx_initdata where INITDATA_ID='e6e39328b9204a30a0ccb7adc67b0112';

SELECT ITEM_VAL INTO MY_SD_MAX_MULTIPLE FROM zx_initdata where INITDATA_ID='e6e39328b9204a30a0ccb7adc67b01121';

#打开游标

OPEN cursor_names;

#逐个取出当前记录字段的值，需要进行加SD操作的判断

FETCH cursor_names INTO MY_USER_ID,MY_USER_NAME,MY_SD,MY_SD_BASE,MY_SD_ROSE_POOL;

#开始循环，判断是否游标已经到达了最后作为循环条件

while Done <> 1 do

#判断增涨金额是否超标

IF (MY_SD_BASE+MY_SD_ROSE_POOL)*MY_SD_ROSE_AMOUNT+MY_SD_ROSE_POOL<=MY_SD_BASE*MY_SD_MAX_MULTIPLE THEN

#未超标

set MY_SD_VALUE=(MY_SD_BASE+MY_SD_ROSE_POOL)*MY_SD_ROSE_AMOUNT;

ELSE

#部分超标

set MY_SD_VALUE=MY_SD_BASE*MY_SD_MAX_MULTIPLE-MY_SD_ROSE_POOL;

end if;

#简单防止一下存储过程执行中数据被改的情况

IF MY_SD_VALUE>0 THEN

UPDATE

sys_user

SET

SD=SD+MY_SD_VALUE,

SD_ROSE_POOL=SD_ROSE_POOL+MY_SD_VALUE,

SD_ROSE_DAY=SD_ROSE_DAY+1

where

USER_ID=MY_USER_ID;

SELECT SD_ROSE_DAY INTO MY_USER_SD_DAY FROM sys_user where USER_ID=MY_USER_ID;

#收支记录

insert into ZX_BILL_SD(SD_ID,USER_ID,VALUE,ADDTIME,ARRIVETIME,HANDLE_USER,TYPE,REMARKS,SURPLUS,STATE,V_TYPE) VALUES(UUID(),MY_USER_ID,MY_SD_VALUE,NOW(),NOW(),'1','BILL_07',CONCAT('SD自增收益 | 第',MY_USER_SD_DAY,'天收入',convert(MY_SD_VALUE,decimal(10,2)),'SD'),MY_SD+MY_SD_VALUE,'BILL_STATE_02',1);

end if;

#读取下一行的数据

FETCH cursor_names INTO MY_USER_ID,MY_USER_NAME,MY_SD,MY_SD_BASE,MY_SD_ROSE_POOL;

end while;

#关闭游标

CLOSE cursor_names;

END

P_RoseSDShop:商城SD升值

BEGIN #商城SD升值

#实际增涨的SD数量

DECLARE MY_SD_VALUE DOUBLE;

#总共可以增涨的SD天数

DECLARE MY_SD_ROSE_DAY DOUBLE;

#每天可增涨的SD比例

DECLARE MY_SD_ROSE_AMOUNT DOUBLE;

#最高可增值倍数

DECLARE MY_SD_MAX_MULTIPLE DOUBLE;

#当前用户SD增涨的天数

DECLARE MY_USER_SD_DAY INT;

DECLARE MY_USER_SD_DAY1 INT;

#游标的Names

#用户ID

DECLARE MY_USER_ID VARCHAR(255);

#用户名称

DECLARE MY_USER_NAME VARCHAR(255);

#用户总SD

DECLARE MY_SD DOUBLE;

#SD基数

DECLARE MY_SD_BASE DOUBLE;

#SD增长池

DECLARE MY_SD_ROSE_POOL DOUBLE;

#循环是否完成

DECLARE Done INT DEFAULT 0;

定义游标的关键字：CURSOR。

定义游标cursor_names，

游标cursor_names当前指针的记录

是一个表sys_user的多行结果集

DECLARE cursor_names CURSOR FOR

SELECT

id as USER_ID,

userName as USERNAME,

integral as SD,

SD_BASE,

SD_ROSE_POOL

from

shopping_user

where

SD_BASE>0

AND

SD_ROSE_DAY

AND

SD_ROSE_POOL

#异常处理结束循环

DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = 1;

SELECT ITEM_VAL INTO MY_SD_ROSE_DAY FROM zx_initdata where INITDATA_ID='1';

SELECT ITEM_VAL INTO MY_SD_ROSE_AMOUNT FROM zx_initdata where INITDATA_ID='2';

SELECT ITEM_VAL INTO MY_SD_MAX_MULTIPLE FROM zx_initdata where INITDATA_ID='3';

#打开游标

OPEN cursor_names;

#逐个取出当前记录字段的值，需要进行加SD操作的判断

FETCH cursor_names INTO MY_USER_ID,MY_USER_NAME,MY_SD,MY_SD_BASE,MY_SD_ROSE_POOL;

#开始循环，判断是否游标已经到达了最后作为循环条件

while Done <> 1 do

#判断增涨金额是否超标

IF (MY_SD_BASE+MY_SD_ROSE_POOL)*MY_SD_ROSE_AMOUNT+MY_SD_ROSE_POOL<=MY_SD_BASE*MY_SD_MAX_MULTIPLE THEN

#未超标

set MY_SD_VALUE=(MY_SD_BASE+MY_SD_ROSE_POOL)*MY_SD_ROSE_AMOUNT;

ELSE

#部分超标

set MY_SD_VALUE=MY_SD_BASE*MY_SD_MAX_MULTIPLE-MY_SD_ROSE_POOL;

#满5倍，清空

UPDATE shopping_user SET SD_ROSE_POOL =0,SD_ROSE_DAY=0,SD_BASE=0 where id=MY_USER_ID;

end if;

#简单防止一下存储过程执行中数据被改的情况

IF MY_SD_VALUE>0 THEN

UPDATE

shopping_user

SET

integral=integral+MY_SD_VALUE,

SD_ROSE_POOL=SD_ROSE_POOL+MY_SD_VALUE,

SD_ROSE_DAY=SD_ROSE_DAY+1

where

id=MY_USER_ID;

SELECT SD_ROSE_DAY INTO MY_USER_SD_DAY1 FROM shopping_user where id=MY_USER_ID;

#满最大天数，清空

IF (MY_USER_SD_DAY <= MY_USER_SD_DAY1) THEN

UPDATE shopping_user SET SD_ROSE_POOL =0,SD_ROSE_DAY=0,SD_BASE=0 where id=MY_USER_ID;

END IF;

#收支记录

INSERT INTO shopping_integrallog (

integral,

deleteStatus,

addTime,

integral_user_id,

type,

content )

VALUES

(

MY_SD_VALUE,

NOW(),

MY_USER_ID,

'SD增值',

CONCAT(

'SD自增收益 | 第',

MY_USER_SD_DAY1,

'天收入',

cast(MY_SD_VALUE as SIGNED),

'SD'

)

);

end if;

#读取下一行的数据

FETCH cursor_names INTO MY_USER_ID,MY_USER_NAME,MY_SD,MY_SD_BASE,MY_SD_ROSE_POOL;

end while;

#关闭游标

CLOSE cursor_names;

END

rwwwwwwww

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
分红在mysql的存储过程_Mysql 直销系统定时结算存储过程

1.png1.png执行存储过程CALL XXXXX()1.png定时执行1.pngP_LeaderAward:领导奖结算BEGIN #领导奖#用户 | idDECLARE s_u_id VARCHAR(255);#用户 | 编号DECLARE s_u_name VARCHAR(255);#用户 | 本期PVDECLARE s_u_pv DOUBLE;#用户 | 推荐人 | idDECLARE s...
复制链接

扫一扫