DELIMITER //
CREATE DEFINER=`user`@`%` EVENT `EV_COLLECT_DATA_EXPORT` ON SCHEDULE EVERY 1 DAY STARTS '2016-10-04 01:00:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL SP_COLLECT_EXPORT_N_DELETE()//
DELIMITER ;
-- 프로시저 IOTDEV.SP_COLLECT_EXPORT_N_DELETE 구조 내보내기
DELIMITER //
CREATE DEFINER=`user`@`%` PROCEDURE `SP_COLLECT_EXPORT_N_DELETE`()
BEGIN
DECLARE exit handler for SQLEXCEPTION,SQLWARNING
BEGIN
ROLLBACK;
SHOW ERRORS;
SHOW WARNINGS;
END;
/* 트랜젝션 시작 */
START TRANSACTION;
SET @exp_date = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -6 DAY ), '%Y-%m-%d');
/* EXPORT */
SET @sql = CONCAT(' SELECT `data`
, `id`
, `reg_dt`
FROM TB_TEST
WHERE reg_dt < @exp_date'
, ' INTO OUTFILE \''
, 'test-', @exp_date,'.csv'
, '\' FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\' LINES TERMINATED BY \'\n\'');
PREPARE statement FROM @sql;
EXECUTE statement;
/* DELETE */
DELETE FROM TB_TEST
WHERE reg_dt < @exp_date;
COMMIT;
END//
DELIMITER ;