DS_CALC_Procedure/NGDS_CALC_PROCEDURE/CALCDELNET.sql

DROP PROCEDURE IF EXISTS `CALCDELNET`;
delimiter ;;
CREATE PROCEDURE `CALCDELNET`(IN `PLAYER` INT)
BEGIN
	DECLARE
		FLAG INT DEFAULT 0;
	DECLARE
		MUSERID,
		MMAXUSERID,
		MINTRODUCERID VARCHAR ( 50 );
	/*DECLARE
		MGPV DECIMAL ( 10, 2 );
	DECLARE
		DN CURSOR FOR SELECT
		USER_ID 
	FROM
		AR_BS_BONUS_103_CALC_NET 
	WHERE
		LAYER = PLAYER 
		AND USER_TYPE = 0;
	DECLARE
		CONTINUE HANDLER FOR NOT FOUND 
		SET FLAG = 1;*/
	START TRANSACTION;-- 打开游标
	/*OPEN DN;-- 获取结果
	L2 :
	LOOP
			FETCH DN INTO MUSERID;
		IF
			FLAG = 1 THEN-- 当无法FETCH会触发HANDLER CONTINUE
				LEAVE L2;
			
		END IF;-- 查询需要删除会员的推荐人ID
		SELECT
			T.INTRODUCER_ID INTO MINTRODUCERID 
		FROM
			AR_BS_BONUS_103_CALC_NET T 
		WHERE
			T.USER_ID = MUSERID;-- 更改需要删除会员第一代的推荐人ID以及层数
		UPDATE AR_BS_BONUS_103_CALC_NET T 
		SET T.INTRODUCER_ID = MINTRODUCERID,
		T.LAYER = T.LAYER - 1 
		WHERE
			T.INTRODUCER_ID = MUSERID;-- 关闭游标
		
	END LOOP;
	CLOSE DN;*/
	UPDATE AR_BS_BONUS_103_CALC_NET
	SET DELETED = 1
	WHERE
		LAYER = PLAYER 
		AND USER_TYPE = 0;
	UPDATE AR_BS_BONUS_103_CALC_NET T1
	INNER JOIN AR_BS_BONUS_103_CALC_NET T2
	ON T1.INTRODUCER_ID = T2.USER_ID AND T1.LAYER = PLAYER+1 AND T2.LAYER =  PLAYER AND T2.USER_TYPE = 0
	SET T1.INTRODUCER_ID = T2.INTRODUCER_ID,T1.LAYER = PLAYER;
	DELETE
	FROM
		AR_BS_BONUS_103_CALC_NET 
	WHERE
		LAYER = PLAYER 
		AND USER_TYPE = 0
		AND DELETED = 1;
	COMMIT;

END
;;