从一表中查询出一列数据,根据这一列数据来从另外一表中进行 判断修改等操作


BEGIN
	DECLARE done tinyint default 0;
	DECLARE uid int(11);
	DECLARE cs CURSOR FOR SELECT userId FROM tbuser_red_envelope_award_log WHERE fromActiveId="1454136362";
	DECLARE continue handler for sqlstate '02000' set done=1;
	open cs;
	while done<>1 do
    fetch cs into uid;
		SELECT giftNum into @giftNum FROM tbuser_red_envelope_award_log WHERE userId=uid AND gifttemplateId=101;
		IF EXISTS(SELECT id FROM tbuser_bag WHERE awardNum>=@giftNum AND userId=uid AND gifttemplateId=101)THEN 
			UPDATE tbuser_bag SET awardNum=awardNum-@giftNum WHERE awardNum>=@giftNum AND userId=uid AND gifttemplateId=101;
		END IF;

		IF EXISTS(SELECT id FROM tbuser_bag_exchange WHERE userId=uid AND excNum>=@giftNum-5 AND addTime>"2016-01-30 18:00:00")THEN
			UPDATE tbuser_bag_exchange SET excNum=excNum-@giftNum,excTotalScore=excTotalScore-@giftNum  WHERE userId=uid AND excNum>=@giftNum-5 AND addTime>"2016-01-30 18:00:00";
		END IF;

		DELETE FROM tbuser_bag_exchange WHERE excNum<=0 AND addTime>"2016-01-30 18:00:00";
	end while;
	close cs;	
END


例2:

BEGIN

		DECLARE $gameId int;			
		DECLARE STOP INT DEFAULT 0;
		DECLARE game_id CURSOR FOR SELECT id FROM tbgames_box_activity;
		DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOP=1;
		OPEN  game_id;
		FETCH game_id INTO $gameId;

		WHILE STOP <> 1 DO

				SET @today = CONCAT(date(NOW())," 00:00:01");

				IF EXISTS(SELECT id  FROM tbgames_box_activity WHERE id = $gameId AND addTime<@today AND isOpen=1 LIMIT 1) THEN

					SELECT awardPool,smallAwardBoxNum,awardAllocType,awardPoolType,awardPoolRemainder INTO @awardPool, @smallAwardBoxNum,@awardAllocType,@awardPoolType,@awardPoolRemainder 
							FROM tbgames_box_activity WHERE id = $gameId AND addTime<@today AND isOpen=1 LIMIT 1;

					IF @awardPoolType=0 THEN

						IF @awardAllocType=1 THEN 
							SET @num=FLOOR(1 + (RAND() * 3));
							UPDATE tbgames_box_activity SET awardPoolRemainder=@awardPool+@awardPoolRemainder,realBoxNum=@smallAwardBoxNum,addTime=NOW(),bigAwardBoxNum=@num WHERE id = $gameId AND addTime<@today;
						ELSE 
							UPDATE tbgames_box_activity SET awardPoolRemainder=@awardPool+@awardPoolRemainder,realBoxNum=@smallAwardBoxNum,addTime=NOW() WHERE id = $gameId AND addTime<@today;
						END IF;

					ELSE 

						IF @awardAllocType=1 THEN 
							SET @num=FLOOR(1 + (RAND() * 3));
							UPDATE tbgames_box_activity SET awardPoolRemainder=@awardPool,realBoxNum=@smallAwardBoxNum,addTime=NOW(),bigAwardBoxNum=@num WHERE id = $gameId AND addTime<@today;
						ELSE 
							UPDATE tbgames_box_activity SET awardPoolRemainder=@awardPool,realBoxNum=@smallAwardBoxNum,addTime=NOW() WHERE id = $gameId AND addTime<@today;
						END IF;

					END IF;

				END IF;	

		FETCH game_id INTO $gameId;  
		
		END WHILE; 

		CLOSE game_id;

END




Logo

更多推荐