MySQL存储过程-->通过游标遍历和异常处理迁移数据到历史表

DELIMITER $$
 
USE `dbx`$$
 
DROP PROCEDURE IF EXISTS `pro_xx`$$
 
CREATE PROCEDURE `pro_xx`()
BEGIN 
 DECLARE p_oalid INT DEFAULT 0;
 DECLARE STOP INT DEFAULT 0; 
 
 DECLARE cur_oalid CURSOR FOR
 SELECToal.id FROM oal_xxx oal WHERE oal.`ymd` <CONCAT(YEAR(DATE_ADD(NOW(),INTERVAL -1 MONTH)),'-',MONTH(DATE_ADD(NOW(),INTERVAL -1 MONTH )),'-',
 DAY(DATE_ADD(NOW(),INTERVAL-1 MONTH ))) LIMIT 1000; 
 DECLARE EXIT HANDLER FOR SQLSTATE '02000' /**包含游标not found*/
 BEGIN
 SET STOP=1;
 INSERTINTO db_logs(log_type,table_name,action_name,log_msg,create_time)
 SELECT1, 'oal_xxx','pro_oal_log_move',CONCAT('primary key:',p_oalid,' 游标执行正常结束！'),NOW(); 
 END;
 
 DECLARE EXIT HANDLER FOR SQLEXCEPTION
 BEGIN
 SETSTOP=1;
 INSERTINTO db_logs(log_type,table_name,action_name,log_msg,create_time)
 SELECT2, 'oal_xxx','pro_oal_log_move',CONCAT('primary key:',p_oalid,' 移动执行失败'),NOW();
 END; 
 
 OPEN cur_oalid;

-- 读取一行数据到变量

FETCH cur_oalid INTO p_oalid;

-- 这个就是判断是否游标已经到达了最后

 WHILE STOP <> 1 DO 
 -- select p_id;
 START TRANSACTION;

-- 进行数据迁移

 REPLACE INTO oal_xxx_history SELECT oal.*FROM oal_xxx oal WHERE oal.id=p_oalid ;
 DELETE FROM oal_xxx WHERE id=p_oalid;
 
 -- INSERT INTO t (tid) VALUES (p_tid);
 COMMIT;

-- 读取下一行的数据

 FETCH cur_oalid INTO p_oalid;
 END WHILE; 
 CLOSE cur_oalid; -- 关闭游标 
 END$$
 
DELIMITER ;