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