mysql游标循环例,mysql存储过程之游标循环♻️－－完整示例

某些数据库操做让存储过程来做效率比较高一些，这里是一个利用游标♻️的mysql存储过程的例子，示例使用了两个游标：cur_this、cur_all。

CREATE PROCEDURE `update_table` ()

BEGIN

/*定义局部变量*/

DECLARE i_user_id, avg_age INT;

DECLARE i_create_time DATETIME default '2016-07-19 00:00:00';

DECLARE sum INT default 0;

DECLARE stop INT default 1;

DECLARE cur_this CURSOR FOR select user_id,create_time from LOGIN_TABLE;

DECLARE cur_all CURSOR FOR select user_id,AVG(age) from USER_TABLE group by age;

#如果没有结果返回，程序继续

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET stop = null;

#也可以跟下面一样写

#DECLARE CONTINUE HANDLER FOR NOT FOUND SET stop = null;

/*更新table*/

OPEN cur_this;

FETCH cur_this INTO i_user_id,i_create_time;

WHILE ( stop is not null) DO

update LOGIN_TABLE set last_time=i_create_time where user_id = i_user_id;

/*游标取下一条数据*/

FETCH cur_this INTO i_user_id,i_create_time;

END WHILE;

CLOSE cur_this;

/*更新总记录数*/

OPEN cur_all;

FETCH cur_all INTO i_user_id,avg_age;

WHILE ( stop is not null) DO

update AVERAGE_AGE_TABLE set average_age=avg_age where user_id=i_user_id;

FETCH cur_all INTO i_user_id, avg_age;

END WHILE;

CLOSE cur_all;

END