mysql 游标中带赋值的

 

drop procedure if exists pro_initUserBirdCount;

DELIMITER $$
create procedure pro_initUserBirdCount()
BEGIN
declare uid int(10);
declare b int(10) default 0; /*是否达到记录的末尾控制变量*/
DECLARE cur_1 CURSOR FOR select distinct user_id from species_pedia.species_user_birds;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
OPEN cur_1;
FETCH cur_1 INTO uid; /*获取第一条记录*/
while b<>1 do
SET SQL_SAFE_UPDATES=0;/*此句很重要*/

set @count_birds=(select count(1) from species_pedia.species_user_birds where user_id=uid);
set @year_birds=(select count(1) from species_pedia.species_user_birds where user_id=uid and year=2014);

-- select @count_birds:=count(1) from g3db.node_bird nb ,g3db.node n where nb.NodeID=n.NodeID and nb.IsVerify=1 and n.UserID=uid;
-- select @year_birds:=count(1) from g3db.node_bird nb ,g3db.node n where nb.NodeID=n.NodeID and nb.IsVerify=1 and n.UserID=uid and nb.RecTimeYear=2016;

insert into species_pedia.species_users set user_id=uid,count_bird_species=@count_birds,year_bird_species=@year_birds;
FETCH cur_1 INTO uid; /*取下一条记录*/
END while;
close cur_1;
END$$
DELIMITER ;

call pro_initUserBirdCount();