mysql存储过程和游标遍历

作者: _少爺血統
来源: 51数据库
2020-08-23

DELIMITER $$
CREATE PROCEDURE alarm_replay_insert_procedure()
BEGIN
-- 定义变量
DECLARE _nowTime DATETIME;
DECLARE _id INT;
DECLARE _name VARCHAR(50);
DECLARE stop_flag TINYINT;

-- 遍历数据结束标志
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT id,`name` FROM `user`;

-- 将结束标志绑定到游标
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET stop_flag=1;

-- 为变量赋值
SET _nowTime = NOW();
SET stop_flag = 0;

OPEN cur; -- 打开游标
FETCH cur INTO _id, _name;
WHILE stop_flag<>1 DO -- 若游标有下一条记录，循环
    INSERT INTO user_replay(id,`name`, `time` ) VALUES(_id,_name,_nowTime);
    FETCH cur INTO _id, _name;
END WHILE;
CLOSE cur; -- 关闭游标

END $$
DELIMITER ;

推荐阅读