欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

多重循环存储过程

程序员文章站 2022-06-04 08:01:47
...
DELIMITER $$

USE `gds_qa`$$

DROP PROCEDURE IF EXISTS `stage_tatment_stastics`$$

CREATE DEFINER=`root`@`192.168.0.%` PROCEDURE `stage_tatment_stastics`(accountId VARCHAR(20))
BEGIN
	DECLARE _cnt INT(3);
	DECLARE _userName VARCHAR(20);
	SET _userName = accountId;
	SELECT COUNT(A.rewardId) INTO _cnt  FROM `activity_v2`.`user_stage_reward_money` A LEFT JOIN activity_v2.`reward_money` B ON A.rewardId =B.rewardId WHERE A.rewardType=12 AND A.userName = _userName  AND B.firstAward IS NOT NULL ORDER BY  A.recordTime ASC;  
	IF _cnt=1 THEN
		BEGIN
			DECLARE no_more_departments INT(3);
			DECLARE _consumptionType VARCHAR(255);
			DECLARE _rewardType VARCHAR(255);
			DECLARE _gameSubClass INT(5);
			DECLARE _gameCount INT(3);
			DECLARE _rewardId INT(11);
			DECLARE _rewardTimes INT(3);
			DECLARE _eachStatmentTal INT DEFAULT 0;
			DECLARE _stageTimes INT DEFAULT 0;
			DECLARE _needStatment DOUBLE DEFAULT 0;
			DECLARE _firstStatement DOUBLE DEFAULT 0;
			DECLARE _secondStatement DOUBLE DEFAULT 0;
			DECLARE _thirdStatement DOUBLE DEFAULT 0;
			DECLARE _fourthStatement DOUBLE DEFAULT 0;
			DECLARE _fifthStatement DOUBLE DEFAULT 0;
			DECLARE _sixthStatement DOUBLE DEFAULT 0;
			DECLARE _seventhStatement DOUBLE DEFAULT 0;
			DECLARE _eighthStatement DOUBLE DEFAULT 0;
			DECLARE _ninthStatement DOUBLE DEFAULT 0;
			DECLARE _rewardMoney DOUBLE DEFAULT 0;
			DECLARE _statmentTal DOUBLE DEFAULT 0;
			DECLARE _winLossMoneyAfterTax DOUBLE DEFAULT 0;
			DECLARE _recordTime DATETIME;
			DECLARE _gameTime DATETIME;
			#定义用户规定流水游标
			DECLARE getUserStageRewardMoney CURSOR FOR
			SELECT A.rewardId,rewardMoney,rewardTimes,A.recordTime FROM `activity_v2`.`user_stage_reward_money` A LEFT JOIN activity_v2.`reward_money` B ON A.rewardId =B.rewardId WHERE A.rewardType=12 AND A.userName = _userName  AND B.firstAward IS NOT NULL ORDER BY  A.recordTime ASC;
			#定义实际流水游标
			DECLARE getRewardMoney CURSOR FOR 
			SELECT consumptionType,winLossMoneyAfterTax,gameSubClass,gameTime  FROM gds_qa.play_log WHERE playerName = _userName;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1; 
			#打开规定流水游标
			OPEN getUserStageRewardMoney;
			FETCH getUserStageRewardMoney INTO _rewardId,_rewardMoney,_rewardTimes,_recordTime;
			#通过奖品rewardId查询每期需要的流水
			SELECT stageTimes, firstStatement,secondStatement,thirdStatement,fourthStatement, fifthStatement,sixthStatement,seventhStatement ,eighthStatement ,ninthStatement 
			INTO  _stageTimes,_firstStatement, _secondStatement,_thirdStatement,_fourthStatement,_fifthStatement,_sixthStatement,_seventhStatement,_eighthStatement,_ninthStatement
			FROM activity_v2.reward_money WHERE rewardId=_rewardId;
			#关闭规定流水游标
			CLOSE getUserStageRewardMoney;
			#统计规定流水
			SET _eachStatmentTal = CASE 
			WHEN (_stageTimes - _rewardTimes)=1 THEN _firstStatement 
			WHEN (_stageTimes - _rewardTimes)=2 THEN _secondStatement
			WHEN (_stageTimes - _rewardTimes)=3 THEN _thirdStatement
			WHEN (_stageTimes - _rewardTimes)=4 THEN _fourthStatement
			WHEN (_stageTimes - _rewardTimes)=5 THEN _fifthStatement
			WHEN (_stageTimes - _rewardTimes)=6 THEN _sixthStatement
			WHEN (_stageTimes - _rewardTimes)=7 THEN _seventhStatement
			WHEN (_stageTimes - _rewardTimes)=8 THEN _eighthStatement
			ELSE  _ninthStatement
			END ;
			#统计实际流水
			SET no_more_departments=0; 
			OPEN getRewardMoney;
			FETCH getRewardMoney INTO _consumptionType,_winLossMoneyAfterTax,_gameSubClass,_gameTime;
			WHILE no_more_departments !=1 DO  
				SELECT COUNT(*) INTO _gameCount  FROM DUAL WHERE _gameSubClass IN(1,2,3,4,5,12,13,14,15,16,17,18,19,20,21,23,24,1108,1110,1115,116,118,1103,5101,4101);	
				IF _gameCount>0 THEN
					IF _gameTime>=_recordTime THEN
						IF "CASH" =_consumptionType THEN
							SET _statmentTal = _statmentTal +ABS(_winLossMoneyAfterTax);
						END IF;
						IF "TANG_COIN" = _consumptionType THEN
							SET _statmentTal = _statmentTal +ABS(_winLossMoneyAfterTax/10000);
						END IF;
					END IF;
				END IF;
				FETCH getRewardMoney INTO _consumptionType,_winLossMoneyAfterTax,_gameSubClass,_gameTime;
			END WHILE;
			CLOSE getRewardMoney;
		        #显示数据
			SET _rewardType="钻石分期返奖";
			
			IF (_eachStatmentTal-_statmentTal)<0 THEN
				SET _needStatment = 0;
				SELECT _userName "用户名" ,_rewardType "奖励类型",1 "参加活动次数",0 "未完成活动次数",_statmentTal "已经完成的流水",_needStatment "还需要的流水",NOW() "时间";
		    
			ELSE
				SET _needStatment = _eachStatmentTal-_statmentTal;
				SELECT _userName "用户名" ,_rewardType "奖励类型",1 "参加活动次数",1 "未完成活动次数",_statmentTal "已经完成的流水",_needStatment "还需要的流水",NOW() "时间";
			END IF;
		END;
	END IF;
	IF _cnt>1 THEN
		BEGIN
			DECLARE i INT;
			DECLARE no_more_departments INT(3);
			DECLARE _z INT DEFAULT -1;
			DECLARE _x INT DEFAULT -1;
			DECLARE _y INT DEFAULT -1;
			DECLARE _v INT DEFAULT -1;
			DECLARE _statmentTotal INT DEFAULT 0;
			DECLARE _statmentTotal1 INT DEFAULT 0;
			DECLARE _rewardRate INT DEFAULT 0;
			DECLARE _need INT DEFAULT -1;
			DECLARE _consumptionType VARCHAR(255);
			DECLARE _rewardType VARCHAR(255);
			DECLARE _gameSubClass INT(5);
			DECLARE _gameCount INT(3);
			DECLARE _statmentCount INT(3);
			DECLARE _rewardId INT(11);
			DECLARE _rewardId2 INT(11);
			DECLARE _rewardTimes INT(3)DEFAULT 0;
			DECLARE _rewardTimes2 INT(3)DEFAULT 0;
			DECLARE _allStatment INT DEFAULT 0;
			DECLARE _eachStatmentTal INT DEFAULT 0;
			DECLARE _eachStatmentTal2 INT DEFAULT 0;
			DECLARE _stageTimes INT DEFAULT 0;
			DECLARE _stageTimes2 INT DEFAULT 0;
			DECLARE _needStatment DOUBLE DEFAULT 0;
			DECLARE _firstStatement DOUBLE DEFAULT 0;
			DECLARE _secondStatement DOUBLE DEFAULT 0;
			DECLARE _thirdStatement DOUBLE DEFAULT 0;
			DECLARE _fourthStatement DOUBLE DEFAULT 0;
			DECLARE _fifthStatement DOUBLE DEFAULT 0;
			DECLARE _sixthStatement DOUBLE DEFAULT 0;
			DECLARE _seventhStatement DOUBLE DEFAULT 0;
			DECLARE _eighthStatement DOUBLE DEFAULT 0;
			DECLARE _ninthStatement DOUBLE DEFAULT 0;
			DECLARE _firstStatement2 DOUBLE DEFAULT 0;
			DECLARE _secondStatement2 DOUBLE DEFAULT 0;
			DECLARE _thirdStatement2 DOUBLE DEFAULT 0;
			DECLARE _fourthStatement2 DOUBLE DEFAULT 0;
			DECLARE _fifthStatement2 DOUBLE DEFAULT 0;
			DECLARE _sixthStatement2 DOUBLE DEFAULT 0;
			DECLARE _seventhStatement2 DOUBLE DEFAULT 0;
			DECLARE _eighthStatement2 DOUBLE DEFAULT 0;
			DECLARE _ninthStatement2 DOUBLE DEFAULT 0;
			DECLARE _rewardMoney DOUBLE DEFAULT 0;
			DECLARE _rewardMoney2 DOUBLE DEFAULT 0;
			DECLARE _statmentTal DOUBLE DEFAULT 0;
			DECLARE _statment DOUBLE DEFAULT 0;
			DECLARE _winLossMoneyAfterTax DOUBLE DEFAULT 0;
			DECLARE _recordTime DATETIME;
			DECLARE _recordTime2 DATETIME;
			DECLARE _gameTime DATETIME;
			DECLARE _activtyTime DATETIME;	
			#定义用户规定流水游标
			DECLARE getUserStageRewardMoney CURSOR FOR 
			SELECT A.rewardId,rewardMoney,rewardTimes, A.recordTime FROM `activity_v2`.`user_stage_reward_money` A LEFT JOIN activity_v2.`reward_money` B ON A.rewardId =B.rewardId WHERE A.rewardType=12 AND A.userName = _userName  AND B.firstAward IS NOT NULL ORDER BY  A.recordTime ASC;
			#定义用户规定流水游标2，用户遍历当前位置的的后面游标数据
			DECLARE getUserStageRewardMoney2 CURSOR FOR 
			SELECT A.rewardId,rewardMoney,rewardTimes, A.recordTime FROM `activity_v2`.`user_stage_reward_money` A LEFT JOIN activity_v2.`reward_money` B  ON A.rewardId =B.rewardId WHERE A.rewardType=12 AND A.userName = _userName  AND B.firstAward IS NOT NULL ORDER BY  A.recordTime ASC;
			#统计实际流水建立游标
			DECLARE getRewardMoney CURSOR FOR 
			SELECT consumptionType,winLossMoneyAfterTax,gameSubClass,gameTime  FROM gds_qa.play_log WHERE playerName = _userName;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1; 
			#打开规定流水游标
			SET no_more_departments=0; 
			SET i=-1;
			OPEN getUserStageRewardMoney;
			FETCH getUserStageRewardMoney INTO _rewardId,_rewardMoney,_rewardTimes,_recordTime;
			SET _activtyTime = _recordTime;
			read_loop0:LOOP 
				SET i = i+1;
				IF i=_cnt THEN
					LEAVE read_loop0;
				END IF;
			#获取礼品需要流水
			SELECT rewardRate,stageTimes, firstStatement,secondStatement,thirdStatement,fourthStatement, fifthStatement,sixthStatement,seventhStatement ,eighthStatement ,ninthStatement 
			INTO  _rewardRate,_stageTimes,_firstStatement, _secondStatement,_thirdStatement,_fourthStatement,_fifthStatement,_sixthStatement,_seventhStatement,_eighthStatement,_ninthStatement
			FROM activity_v2.reward_money WHERE rewardId=_rewardId;
			#统计规定流水
			SET _eachStatmentTal = CASE 
				WHEN (_stageTimes - _rewardTimes)=1 THEN _firstStatement 
				WHEN (_stageTimes - _rewardTimes)=2 THEN _secondStatement
				WHEN (_stageTimes - _rewardTimes)=3 THEN _thirdStatement
				WHEN (_stageTimes - _rewardTimes)=4 THEN _fourthStatement
				WHEN (_stageTimes - _rewardTimes)=5 THEN _fifthStatement
				WHEN (_stageTimes - _rewardTimes)=6 THEN _sixthStatement
				WHEN (_stageTimes - _rewardTimes)=7 THEN _seventhStatement
				WHEN (_stageTimes - _rewardTimes)=8 THEN _eighthStatement
				ELSE  _ninthStatement
			END ;
		        #统计需要流水的数量，开实际流水游标
			SELECT COUNT(*) INTO _statmentCount FROM gds_qa.play_log WHERE playerName = _userName;
			SET no_more_departments=0; 
			SET _statment=0;	
			OPEN getRewardMoney;
			FETCH getRewardMoney INTO _consumptionType,_winLossMoneyAfterTax,_gameSubClass,_gameTime;
			read_loop:LOOP	
				SET _z =_z +1;
				IF _z>_statmentCount-1 THEN
					LEAVE read_loop; 
				END IF ;
				#只统计手机端规定需要的流水
				SELECT COUNT(*) INTO _gameCount  FROM DUAL WHERE _gameSubClass IN(1,2,3,4,5,12,13,14,15,16,17,18,19,20,21,23,24,1108,1110,1115,116,118,1103,5101,4101);	
				IF _gameCount>0 THEN
					IF _gameTime>=_activtyTime THEN
						IF "CASH" =_consumptionType THEN
							SET _statment = _statment +ABS(_winLossMoneyAfterTax);
						END IF;
						IF "TANG_COIN" = _consumptionType THEN
							SET _statment = _statment +(ABS(_winLossMoneyAfterTax)/10000);
						END IF;
					END IF;
				END IF;	
				FETCH getRewardMoney INTO _consumptionType,_winLossMoneyAfterTax,_gameSubClass,_gameTime;
			END LOOP; 
			CLOSE getRewardMoney;
                       #核心处理逻辑，判断当前活动是否满足流水情况，不满足统计所有活动需要的流水，满足就需要看下个活动是否满足（满足的条件：当前流水大于所有活动的流水）
			SET _x=-1;	
			SET _v=-1;  
			IF  _statment >= _eachStatmentTal THEN     
				IF (i+1)<_cnt THEN
					#获取下一个活动规定流水游标位置
					OPEN getUserStageRewardMoney2 ;	
					FETCH getUserStageRewardMoney2 INTO _rewardId2,_rewardMoney2,_rewardTimes2,_recordTime2;
					FETCH getUserStageRewardMoney2 INTO _rewardId2,_rewardMoney2,_rewardTimes2,_recordTime2;
					read_loop1:LOOP	
						SET _x =_x +1;
						IF _x=i THEN
							LEAVE read_loop1;
						END IF ;
						FETCH getUserStageRewardMoney2 INTO _rewardId2,_rewardMoney2,_rewardTimes2,_recordTime2;     
					END LOOP; 
					#获取剩余活动礼品需要的流水
					SET no_more_departments =0;
					SET _y =_x+1 ;
					SET _statmentTotal = 0;
					read_loop2:LOOP	
						IF _y=_cnt THEN
							LEAVE read_loop2;
						END IF ;
						SELECT stageTimes, firstStatement,secondStatement,thirdStatement,fourthStatement, fifthStatement,sixthStatement,seventhStatement ,eighthStatement ,ninthStatement 
						INTO  _stageTimes2,_firstStatement2, _secondStatement2,_thirdStatement2,_fourthStatement2,_fifthStatement2,_sixthStatement2,_seventhStatement2,_eighthStatement2,_ninthStatement2
						FROM activity_v2.reward_money WHERE rewardId=_rewardId2;
						#统计规定流水
						SET _eachStatmentTal2 = CASE 
							WHEN (_stageTimes2 - _rewardTimes2)=1 THEN _firstStatement2 
							WHEN (_stageTimes2 - _rewardTimes2)=2 THEN _secondStatement2
							WHEN (_stageTimes2 - _rewardTimes2)=3 THEN _thirdStatement2
							WHEN (_stageTimes2 - _rewardTimes2)=4 THEN _fourthStatement2
							WHEN (_stageTimes2 - _rewardTimes2)=5 THEN _fifthStatement2
							WHEN (_stageTimes2 - _rewardTimes2)=6 THEN _sixthStatement2
							WHEN (_stageTimes2 - _rewardTimes2)=7 THEN _seventhStatement2
							WHEN (_stageTimes2 - _rewardTimes2)=8 THEN _eighthStatement2
							ELSE  _ninthStatement2
						END ;
						SET _y= _y+1;
						SET _statmentTotal = _statmentTotal + _eachStatmentTal2;
						FETCH getUserStageRewardMoney2 INTO _rewardId2,_rewardMoney2,_rewardTimes2,_recordTime2;
					END LOOP;	
					CLOSE getUserStageRewardMoney2;
					#如果当前活动和剩余活动规定流水大于了实际流水，不再做循环统计，反之把时间移动到下一个活动，继续循环
					IF _statmentTotal+_eachStatmentTal>_statment THEN
						SET _allStatment = _statmentTotal + _eachStatmentTal;
						SET _need = i+1;
						LEAVE read_loop0; 
					ELSE
						OPEN getUserStageRewardMoney2 ;	
						FETCH getUserStageRewardMoney2 INTO _rewardId2,_rewardMoney2,_rewardTimes2,_recordTime2;
						FETCH getUserStageRewardMoney2 INTO _rewardId2,_rewardMoney2,_rewardTimes2,_recordTime2;
						read_loop5:LOOP	
							SET _v =_v +1;
							IF _v=i THEN
								LEAVE read_loop5;
							END IF ;
							FETCH getUserStageRewardMoney2 INTO _rewardId2,_rewardMoney2,_rewardTimes2,_recordTime2;     
						END LOOP; 
						CLOSE getUserStageRewardMoney2;
						SET _activtyTime= _recordTime2; 
					END IF;
				END IF;
			ELSE         
				IF (i+1)<_cnt THEN
					#本次活动流水已经不满足，直接统计剩下活动需要的流水，不再循环最外层循环
					OPEN getUserStageRewardMoney2 ;	
					FETCH getUserStageRewardMoney2 INTO _rewardId2,_rewardMoney2,_rewardTimes2,_recordTime2;
					FETCH getUserStageRewardMoney2 INTO _rewardId2,_rewardMoney2,_rewardTimes2,_recordTime2;
					read_loop3:LOOP	
						SET _x =_x +1;
						IF _x=i THEN
							LEAVE read_loop3;
						END IF ;
						FETCH getUserStageRewardMoney2 INTO _rewardId2,_rewardMoney2,_rewardTimes2,_recordTime2;     
					END LOOP; 
					#获取剩下活动游标礼品需要流水
					SET no_more_departments =0;
					SET _y =_x+1 ;
					SET _statmentTotal1=0;
					read_loop4:LOOP	
						IF _y=_cnt THEN
							LEAVE read_loop4;
						END IF ;
						SELECT stageTimes, firstStatement,secondStatement,thirdStatement,fourthStatement, fifthStatement,sixthStatement,seventhStatement ,eighthStatement ,ninthStatement 
						INTO  _stageTimes2,_firstStatement2, _secondStatement2,_thirdStatement2,_fourthStatement2,_fifthStatement2,_sixthStatement2,_seventhStatement2,_eighthStatement2,_ninthStatement2
						FROM activity_v2.reward_money WHERE rewardId=_rewardId2;
						#统计规定流水
						SET _eachStatmentTal2 = CASE 
							WHEN (_stageTimes2 - _rewardTimes2)=1 THEN _firstStatement2 
							WHEN (_stageTimes2 - _rewardTimes2)=2 THEN _secondStatement2
							WHEN (_stageTimes2 - _rewardTimes2)=3 THEN _thirdStatement2
							WHEN (_stageTimes2 - _rewardTimes2)=4 THEN _fourthStatement2
							WHEN (_stageTimes2 - _rewardTimes2)=5 THEN _fifthStatement2
							WHEN (_stageTimes2 - _rewardTimes2)=6 THEN _sixthStatement2
							WHEN (_stageTimes2 - _rewardTimes2)=7 THEN _seventhStatement2
							WHEN (_stageTimes2 - _rewardTimes2)=8 THEN _eighthStatement2
							ELSE  _ninthStatement2
						END;
						SET _y= _y+1;
						SET _statmentTotal1 = _statmentTotal1 + _eachStatmentTal2;
						FETCH getUserStageRewardMoney2 INTO _rewardId2,_rewardMoney2,_rewardTimes2,_recordTime2;
					END LOOP;	
					CLOSE getUserStageRewardMoney2;	
					SET _allStatment = _statmentTotal1 + _eachStatmentTal;
					SET _need = i;
					LEAVE read_loop0; 
				ELSE 
					SET _allStatment = _statmentTotal1 + _eachStatmentTal;
					SET _need = i;		
									
				END IF;
			END IF;
			SET no_more_departments =0;
			FETCH getUserStageRewardMoney INTO _rewardId,_rewardMoney,_rewardTimes,_recordTime;
			END LOOP; 
			#关闭游标
			CLOSE getUserStageRewardMoney;
			#存储显示数据
			IF _need = -1 THEN
				SET _need = 0;
			ELSE
				SET _need = _cnt -_need;	
			END IF;
			IF _allStatment-_statment>0 THEN
				SET  _needStatment = ROUND(_allStatment-_statment,2);
			ELSE
				SET _needStatment= 0;
			END IF;
			SET _rewardType = "钻石分期返奖";
			SELECT _userName "用户名" ,_rewardType "奖励类型",_cnt "参加活动次数",_need  "未完成活动次数",ROUND(_statment,2) "已经完成的流水",_needStatment "还需要的流水",NOW() "时间"; 
		END;
	END IF;
END$$

DELIMITER ;

多重循环的存储过程，但不建议使用，复杂的逻辑最好使用合理的代码进行处理

相关标签： 存储过程

上一篇： 你知道怎么在 HTML 页面中使用 React吗

下一篇： 风扇狂魔！酷冷发布HAF 700 EVO机箱：支持18个120风扇

推荐阅读