mysql 存储过程临时表代替游标

BEGIN

DECLARE maxCnt INT DEFAULT 0;
DECLARE i INT DEFAULT 0;

DECLARE tId BIGINT DEFAULT 0;
DECLARE tColorId INT  DEFAULT 0;

/*创建临时表*/
DROP TABLE IF EXISTS tmp_expire_player_car_color;
CREATE TEMPORARY TABLE tmp_expire_player_car_color(
`Tmp_Id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`id` BIGINT NOT NULL,
`color_id` int NOT NULL,
PRIMARY KEY (`Tmp_Id`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

/*清空临时表*/
TRUNCATE TABLE tmp_expire_player_car_color;
/*插入数据*/
SET @tSql = CONCAT('INSERT INTO tmp_expire_player_car_color (`id`,`color_id`)
select a.Id,b.color from player_car a,s_car b,player_car_color c
where a.car_id = b.id
and a.color !=b.color
and c.player_id = a.player_id
and a.color = c.color_id
and c.expire_date is not null
and c.expire_date<now();
');
PREPARE gatherData FROM @tSql;
EXECUTE gatherData;

SELECT MIN(`Tmp_Id`) INTO i FROM tmp_expire_player_car_color;
SELECT MAX(`Tmp_Id`) INTO maxCnt FROM tmp_expire_player_car_color;


WHILE i <= maxCnt DO
SELECT id, color_id  INTO tId, tColorId FROM tmp_expire_player_car_color WHERE Tmp_Id = i;
update player_car t set t.color = tColorId where t.Id = tId;
SET i = i + 1;
END WHILE;
COMMIT;
END