Rotina de limpeza - MySQL

DROP PROCEDURE IF EXISTS limpezaOutput;

DELIMITER //

CREATE PROCEDURE limpezaOutput()

 BEGIN

 DECLARE registros INT;

 SELECT COUNT(*) into registros FROM (SELECT * from nfceoutput WHERE UNIX_TIMESTAMP(insertdate) < UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY) LIMIT 1000) t;

 WHILE (registros > 0) DO

         SELECT ("Deletando no maximo 1000 registros...");

         DELETE FROM nfceoutput WHERE UNIX_TIMESTAMP(insertdate) < UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY) LIMIT 1000;

         SELECT COUNT(*) into registros FROM (SELECT * from nfceoutput WHERE UNIX_TIMESTAMP(insertdate) < UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY) LIMIT 1000) t;        

 END WHILE;

 END //

DELIMITER ;

 

DROP PROCEDURE IF EXISTS limpezaInput;

DELIMITER //

CREATE PROCEDURE limpezaInput()

 BEGIN

 DECLARE registros INT;

 SELECT COUNT(*) into registros FROM (SELECT * from nfceinput WHERE UNIX_TIMESTAMP(insertdate) < UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY) LIMIT 1000) t;

 WHILE (registros > 0) DO

         SELECT ("Deletando no maximo 1000 registros...");

         DELETE FROM nfceinput WHERE UNIX_TIMESTAMP(insertdate) < UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY) LIMIT 1000;

         SELECT COUNT(*) into registros FROM (SELECT * from nfceinput WHERE UNIX_TIMESTAMP(insertdate) < UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY) LIMIT 1000) t;        

 END WHILE;

 END //

DELIMITER ;

 

 

DROP EVENT IF EXISTS EvtLimpezaTabelas;

DELIMITER //

CREATE EVENT EvtLimpezaTabelas

ON SCHEDULE EVERY 10 MINUTE

COMMENT 'Remover registros com mais de 30 dias'

 DO

 BEGIN

         CALL limpezaInput();

         CALL limpezaOutput();                

 END //

DELIMITER ;