Press "Enter" to skip to content

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

参考:http://www.cnblogs.com/fuge/p/3582930.html,有修正,删改

发表评论

电子邮件地址不会被公开。 必填项已用*标注

Captcha Code

提示