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 ;
mysql存储过程和游标遍历
最新推荐文章于 2024-07-28 21:37:11 发布