某些数据库操做让存储过程来做效率比较高一些,这里是一个利用游标♻️的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