MySQL預存程序-->通過遊標遍曆和異常處理遷移資料到曆史表

大表資料移轉,每天淩晨1點到5點執行,執行間隔時間10分鐘，遷移舊資料到曆史表。

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' 

   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 ;