짬타이거 화이팅!

--프로시저 실행일 다음달 1일부터 말일까지 테이블 생성

--템플릿 테이블 필요(테이블명_exam)

--생성테이블 규칙 : TB_NAME_yyyyMMdd

DROP PROCEDURE IF EXISTS `create_log_table`;

DELIMITER $$

CREATE PROCEDURE create_log_table(IN TB_NAME VARCHAR(50))

BEGIN

DECLARE i INT DEFAULT 0;

        SET @startDay = DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY);

SET @lastDay = DAY(LAST_DAY(DATE_ADD(NOW(),interval 1 MONTH)));

WHILE i < @lastDay DO

SET @log_date = DATE_FORMAT(DATE_ADD(@startDay, INTERVAL i DAY), '%Y%m%d');

SET @stmt_query = CONCAT('CREATE TABLE ',TB_NAME,'_',@log_date,' LIKE ',TB_NAME,'_exam;');

            PREPARE stmt FROM @stmt_query;

            START TRANSACTION;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

COMMIT;

SET i = i + 1;

END WHILE;

END$$

DELIMITER $$

-- 1달에 1번 실행, 시작일 2017-12-01

DROP EVENT IF EXISTS event_create_log_table;

DELIMITER $$

CREATE EVENT event_create_log_table

     -- ON SCHEDULE EVERY 1 MONTH STARTS TIMESTAMP('2017-12-01') ENABLE

        ON SCHEDULE EVERY 1 MONTH STARTS '2017-12-01 00:00:00' ENABLE

    DO 

    job1:BEGIN

CALL log.create_log_table('viewlog');

CALL log.create_log_table('clicklog');

                CALL log.create_log_table('ad_log');

END job1

$$

DELIMITER ;

-- procedure 등록 구문

DELIMITER $$

CREATE PROCEDURE 프로시저명()

BEGIN

    DELETE FROM 테이블명A;

    INSERT 테이블명A

    SELECT cid

           ,count(*) AS viewcount

           ,SYSDATE() AS regdt

      FROM 테이블명B

     WHERE regdt BETWEEN DATE_ADD(SYSDATE(),INTERVAL -12 HOUR) AND SYSDATE()

     GROUP BY cid

     ORDER BY count(*) DESC,regdt DESC

     LIMIT 0,5;

END$$

DELIMITER ;

-- procedure 식행 구문

call 프로시저명();

-- event scheduler 구문

CREATE EVENT IF NOT EXISTS DB명.이벤트스케쥴러명

    ON SCHEDULE

        EVERY 10 MINUTE -- event scheduler 실행 간격(년,월,일,시,분,초)

        STARTS '2014-12-30 16:00:00' -- scheduler 등록시간 +10분

    DO

        call DB명.프로시저명(); -- event scheduler로 등록할 쿼리구문