mysql存储过程和游标遍历
时间:2019-02-18 13:08 来源:linux.it.net.cn 作者:IT
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 ;
(责任编辑：IT)
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 ; (责任编辑：IT) |