NCDS_Procedure/CALC20_0.sql

DROP PROCEDURE IF EXISTS `CALC20_0`;
delimiter ;;
CREATE PROCEDURE `CALC20_0`(IN PLAYER INT,IN `PCPID` INT)
BEGIN
	DECLARE
		FLAG INT DEFAULT 0;
	DECLARE
		MUSERID,
		MMAXUSERID VARCHAR ( 50 );
	DECLARE
		MGPV DECIMAL ( 10, 2 );
	DECLARE
		MC CURSOR FOR SELECT
		C.USER_ID 
	FROM
		AR_BS_BONUS_103_CALC_NET C 
	WHERE
		C.DIRECTLY_UNDER >= 2 
		AND C.USER_TYPE = 0 
		AND LAYER = PLAYER;
	DECLARE
		CONTINUE HANDLER FOR NOT FOUND 
		SET FLAG = 1;-- 打开游标
	OPEN MC;-- 获取结果
	L2 :
	LOOP
			FETCH MC INTO MUSERID;
		IF
			FLAG = 1 THEN-- 当无法FETCH会触发HANDLER CONTINUE
				LEAVE L2;
			
		END IF;-- 这里是为了显示获取结果
		SELECT
			MIN( GPV ) INTO MGPV 
		FROM
			AR_BS_BONUS_103_CALC_NET C 
		WHERE
			C.INTRODUCER_ID = MUSERID 
			AND C.USER_TYPE >= 10;
		SELECT
			MAX( USER_ID ) INTO MMAXUSERID 
		FROM
			AR_BS_BONUS_103_CALC_NET C 
		WHERE
			C.INTRODUCER_ID = MUSERID 
			AND C.GPV = MGPV;
		UPDATE AR_BS_BONUS_103_CALC_NET C 
		SET C.USER_TYPE = 20,
		C.GPV = MGPV 
		WHERE
			USER_ID = MUSERID;
		UPDATE AR_BS_BONUS_103_CALC_NET C 
		SET C.GPV = 0 
		WHERE
			USER_ID = MMAXUSERID;-- 关闭游标
		
		INSERT INTO AR_CALC_BONUS_BS_DETAIL_GPV(USER_ID,INTRODUCER_ID,GPV,PERIOD_NUM)
		VALUES(MMAXUSERID,MUSERID,MGPV,PCPID);
	END LOOP;
	CLOSE MC;

END
;;