MySQL stored procedure -- & gt; migrate data to the History Table through cursor traversal and Exception Handling

Big Table data migration,Every morning1Point5Point execution, The execution interval is 10 minutes,Migrate old data to the History Table.

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

SELECToal. id FROM oal_xxx oal WHERE oal. 'ymmd' <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 '20140901'

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, 'cursor execution ends normally! '), 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, 'failed to move execution '), 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;