MySQL Event Scheduler 定時執行存儲過程實戰案例

啟用事件調度器

SET GLOBAL event_scheduler = ON;  -- 或 1
-- 永久生效：my.cnf [mysqld] event_scheduler=ON

實戰存儲過程：每日統計 Action Item 狀態

DELIMITER $$
CREATE PROCEDURE `Get_Info_Every_Day`()
BEGIN
    DECLARE pIntSumTotalAction INT DEFAULT 0;
    DECLARE pIntSumNoduedate INT DEFAULT 0;
    DECLARE pIntSumClosed INT DEFAULT 0;
    DECLARE pIntSumForinfo INT DEFAULT 0;
    DECLARE pIntSumOverdue INT DEFAULT 0;
    DECLARE pIntSumTBDin1Week INT DEFAULT 0;
    DECLARE pIntSumTBDafter1Week INT DEFAULT 0;
    DECLARE pIntSumPendingJPMO INT DEFAULT 0;
    DECLARE pIntSumEPS INT DEFAULT 0;
    DECLARE pIntSumWCI INT DEFAULT 0;
    DECLARE pIntSumOnTimeClosed INT DEFAULT 0;
    DECLARE pIntTotal INT DEFAULT 0;
    DECLARE strStatus VARCHAR(40);
    DECLARE dDuedate, dClosedDate DATETIME;
    DECLARE nOverdue, nCountOnTime INT;
    DECLARE fetchSeqOk BOOLEAN DEFAULT FALSE;

    -- 游標 1：明細資料
    DECLARE my_cursor CURSOR FOR
        SELECT b.status, b.duedate, b.closedate, DATEDIFF(NOW(), b.duedate) AS overdue
        FROM actionitem a
        JOIN actionitemdetail b ON a.id_actionitem = b.fk_actionitem
        WHERE a.finishdate = 0
          AND b.status <> 'forinfo'
          AND (b.actionby LIKE '%WEC%' OR b.actionby LIKE '%Consortium%');

    -- 游標 2：準時關閉統計
    DECLARE my_cursor2 CURSOR FOR
        SELECT CAST(COUNT(*) AS UNSIGNED)
        FROM actionitemdetail
        WHERE DATEDIFF(NOW(), duedate) BETWEEN 0 AND 7
          AND (actionby LIKE '%WEC%' OR actionby LIKE '%Consortium%')
          AND status = 'Closed' AND DATEDIFF(closedate, duedate) < 0
        ORDER BY duedate;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetchSeqOk = TRUE;

    OPEN my_cursor;
    fetchLoop: LOOP
        FETCH my_cursor INTO strStatus, dDuedate, dClosedDate, nOverdue;
        IF fetchSeqOk THEN LEAVE fetchLoop; END IF;

        IF LOWER(strStatus) = 'open' THEN
            CASE
                WHEN nOverdue IS NULL THEN SET pIntSumNoduedate = pIntSumNoduedate + 1;
                WHEN nOverdue > 0 THEN SET pIntSumOverdue = pIntSumOverdue + 1;
                WHEN nOverdue <= 0 AND nOverdue > -7 THEN SET pIntSumTBDin1Week = pIntSumTBDin1Week + 1;
                ELSE SET pIntSumTBDafter1Week = pIntSumTBDafter1Week + 1;
            END CASE;
        ELSE
            CASE LOWER(strStatus)
                WHEN 'closed' THEN SET pIntSumClosed = pIntSumClosed + 1;
                WHEN 'forinfo' THEN SET pIntSumForinfo = pIntSumForinfo + 1;
                WHEN 'pending jpmo' THEN SET pIntSumPendingJPMO = pIntSumPendingJPMO + 1;
                WHEN 'escalated to pcc for support' THEN SET pIntSumEPS = pIntSumEPS + 1;
                WHEN 'waiting for customer input' THEN SET pIntSumWCI = pIntSumWCI + 1;
            END CASE;
        END IF;
    END LOOP;
    CLOSE my_cursor;

    SET pIntTotal = pIntSumTBDafter1Week + pIntSumOverdue + pIntSumTBDin1Week +
                    pIntSumNoduedate + pIntSumPendingJPMO + pIntSumEPS + pIntSumWCI + pIntSumClosed;

    SET fetchSeqOk = FALSE;
    OPEN my_cursor2;
    my_loop: LOOP
        FETCH my_cursor2 INTO nCountOnTime;
        IF fetchSeqOk THEN LEAVE my_loop; END IF;
        SET pIntSumOnTimeClosed = nCountOnTime;
    END LOOP;
    CLOSE my_cursor2;

    -- 寫入統計表
    INSERT INTO daily_statistic (Total, Open, overdue, DueWithin7Days, PTP, NoDueDate, PendingJPMO, EPS, WCI, Closed)
    VALUES (pIntTotal, pIntSumTBDafter1Week, pIntSumOverdue, pIntSumTBDin1Week,
            pIntSumOnTimeClosed, pIntSumNoduedate, pIntSumPendingJPMO, pIntSumEPS, pIntSumWCI, pIntSumClosed);

    -- 測試用
    INSERT INTO mytest(testdate) VALUES (NOW());
END $$
DELIMITER ;

建立定時事件

USE cddl;
DROP EVENT IF EXISTS e_statistics_daily;
CREATE EVENT e_statistics_daily
ON SCHEDULE EVERY 1 DAY
STARTS '2013-10-18 16:45:00'
ON COMPLETION PRESERVE
DO CALL Get_Info_Every_Day();

驗證

SELECT * FROM daily_statistic;
SHOW EVENTS;

關鍵技術點

• 雙游標分離業務邏輯：明細分類統計 + 準時關閉率
• CONTINUE HANDLER FOR NOT FOUND 優雅結束游標迴圈
• DATEDIFF 計算逾期天數，CASE 分支統計
• 事件 ON COMPLETION PRESERVE 執行後保留，支援 ENABLE/DISABLE 控制
• 生產環境建議加入錯誤處理、執行日誌、執行時間監控