資料庫函式程式觸發器與事件排程器完整實作指南

-- 暫時改變陳述式結束符號
-- 因為函式定義內部會使用分號,需要使用不同的結束符號
DELIMITER //

-- 建立計算年資的函式
-- 接受員工入職日期作為參數,回傳以年為單位的年資
CREATE FUNCTION calculate_years_of_service(
    -- 輸入參數: 員工入職日期
    hire_date DATE
)
-- 回傳型態: 整數,代表完整年數
RETURNS INT
-- 標記函式為確定性函式
-- 相同輸入必定產生相同輸出
DETERMINISTIC
-- 標記函式不修改SQL資料
-- 這是一個純粹的讀取操作
READS SQL DATA
BEGIN
    -- 宣告區域變數儲存計算結果
    -- 使用DECLARE陳述式定義變數型態
    DECLARE years INT;
    
    -- 使用TIMESTAMPDIFF函式計算年數
    -- YEAR表示以年為單位計算差異
    -- CURDATE()取得當前日期
    SET years = TIMESTAMPDIFF(YEAR, hire_date, CURDATE());
    
    -- 回傳計算出的年資
    RETURN years;
END//

-- 恢復預設的陳述式結束符號
DELIMITER ;

-- 使用函式範例: 查詢所有員工的年資
-- 函式可以直接在SELECT陳述式中使用
SELECT 
    employee_id AS '員工編號',
    CONCAT(last_name, first_name) AS '姓名',
    hire_date AS '入職日期',
    -- 呼叫自訂函式計算年資
    calculate_years_of_service(hire_date) AS '年資(年)'
FROM employee
-- 篩選年資超過五年的資深員工
WHERE calculate_years_of_service(hire_date) >= 5
ORDER BY hire_date ASC;

-- 暫時改變陳述式結束符號
DELIMITER //

-- 建立員工薪資調整預存程序
-- 根據績效評等調整員工薪資並記錄調整歷程
CREATE PROCEDURE adjust_employee_salary(
    -- 輸入參數: 員工編號
    IN emp_id INT,
    -- 輸入參數: 績效評等 (1-5分)
    IN performance_rating INT,
    -- 輸出參數: 調整後的薪資
    OUT new_salary DECIMAL(15, 2),
    -- 輸出參數: 調整百分比
    OUT adjustment_percentage DECIMAL(5, 2)
)
BEGIN
    -- 宣告區域變數
    -- 儲存員工當前薪資
    DECLARE current_salary DECIMAL(15, 2);
    -- 儲存薪資調整金額
    DECLARE adjustment_amount DECIMAL(15, 2);
    -- 錯誤處理旗標
    DECLARE error_flag INT DEFAULT 0;
    
    -- 宣告錯誤處理器
    -- 當發生SQL異常時設定錯誤旗標
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        SET error_flag = 1;
    END;
    
    -- 開始交易
    -- 確保所有操作具有原子性
    START TRANSACTION;
    
    -- 查詢員工當前薪資
    -- 使用SELECT INTO將查詢結果指派給變數
    SELECT salary INTO current_salary
    FROM employee
    WHERE employee_id = emp_id
    -- 使用FOR UPDATE鎖定該筆記錄
    -- 防止並行修改導致的資料不一致
    FOR UPDATE;
    
    -- 根據績效評等計算調整百分比
    -- 使用CASE陳述式實現多分支邏輯
    SET adjustment_percentage = CASE
        WHEN performance_rating = 5 THEN 10.00  -- 傑出: 調薪10%
        WHEN performance_rating = 4 THEN 7.00   -- 優良: 調薪7%
        WHEN performance_rating = 3 THEN 5.00   -- 符合期望: 調薪5%
        WHEN performance_rating = 2 THEN 2.00   -- 待改善: 調薪2%
        WHEN performance_rating = 1 THEN 0.00   -- 不符期望: 不調薪
        ELSE 0.00                                -- 無效評等: 不調薪
    END;
    
    -- 計算調整金額
    -- 當前薪資乘以調整百分比
    SET adjustment_amount = current_salary * (adjustment_percentage / 100);
    
    -- 計算調整後的新薪資
    SET new_salary = current_salary + adjustment_amount;
    
    -- 更新員工薪資
    -- 只有在沒有發生錯誤時才執行
    IF error_flag = 0 THEN
        UPDATE employee
        SET salary = new_salary,
            last_salary_review_date = CURDATE()
        WHERE employee_id = emp_id;
        
        -- 記錄薪資調整歷程
        -- 寫入稽核表以供後續追蹤
        INSERT INTO salary_adjustment_history (
            employee_id,
            adjustment_date,
            old_salary,
            new_salary,
            adjustment_percentage,
            performance_rating
        ) VALUES (
            emp_id,
            NOW(),
            current_salary,
            new_salary,
            adjustment_percentage,
            performance_rating
        );
        
        -- 提交交易
        -- 所有變更永久生效
        COMMIT;
    ELSE
        -- 發生錯誤時回復交易
        -- 所有變更都不會生效
        ROLLBACK;
        
        -- 設定輸出參數為NULL表示失敗
        SET new_salary = NULL;
        SET adjustment_percentage = NULL;
    END IF;
END//

DELIMITER ;

-- 呼叫預存程序範例
-- 使用使用者變數接收輸出參數
CALL adjust_employee_salary(
    1001,           -- 員工編號
    4,              -- 績效評等
    @result_salary, -- 接收新薪資的變數
    @result_pct     -- 接收調整百分比的變數
);

-- 查詢輸出參數的值
-- 檢視程序執行結果
SELECT 
    @result_salary AS '調整後薪資',
    @result_pct AS '調整百分比';

DELIMITER //

-- 建立使用遊標的預存程序
-- 計算各部門的薪資統計並產生報表
CREATE PROCEDURE generate_department_salary_report()
BEGIN
    -- 宣告變數儲存遊標提取的資料
    DECLARE dept_id INT;
    DECLARE dept_name VARCHAR(100);
    DECLARE emp_count INT;
    DECLARE avg_salary DECIMAL(15, 2);
    DECLARE total_salary DECIMAL(15, 2);
    
    -- 宣告結束旗標
    -- 當遊標沒有更多資料時設為TRUE
    DECLARE done INT DEFAULT FALSE;
    
    -- 宣告遊標
    -- 定義要處理的資料集
    DECLARE dept_cursor CURSOR FOR
        SELECT 
            d.department_id,
            d.department_name,
            COUNT(e.employee_id) AS employee_count,
            AVG(e.salary) AS average_salary,
            SUM(e.salary) AS total_salary
        FROM department d
        LEFT JOIN employee e ON d.department_id = e.department_id
        GROUP BY d.department_id, d.department_name
        ORDER BY d.department_name;
    
    -- 宣告NOT FOUND處理器
    -- 當遊標沒有更多資料時觸發
    DECLARE CONTINUE HANDLER FOR NOT FOUND 
        SET done = TRUE;
    
    -- 清空報表表格
    -- 移除舊的報表資料
    TRUNCATE TABLE department_salary_report;
    
    -- 開啟遊標
    -- 執行SELECT查詢並準備資料集
    OPEN dept_cursor;
    
    -- 開始遊標迴圈
    read_loop: LOOP
        -- 提取下一列資料
        -- 將欄位值指派給對應變數
        FETCH dept_cursor INTO 
            dept_id, 
            dept_name, 
            emp_count, 
            avg_salary, 
            total_salary;
        
        -- 檢查是否已處理完所有資料
        IF done THEN
            -- 離開迴圈
            LEAVE read_loop;
        END IF;
        
        -- 處理當前資料列
        -- 計算額外的統計資訊
        -- 將結果寫入報表表格
        INSERT INTO department_salary_report (
            department_id,
            department_name,
            employee_count,
            average_salary,
            total_salary,
            salary_range,
            report_date
        ) VALUES (
            dept_id,
            dept_name,
            emp_count,
            avg_salary,
            total_salary,
            -- 計算薪資範圍等級
            CASE
                WHEN avg_salary >= 100000 THEN '高薪資部門'
                WHEN avg_salary >= 60000 THEN '中薪資部門'
                ELSE '一般薪資部門'
            END,
            CURDATE()
        );
    END LOOP;
    
    -- 關閉遊標
    -- 釋放資源
    CLOSE dept_cursor;
    
    -- 回傳處理結果
    SELECT '報表生成完成' AS '狀態訊息';
END//

DELIMITER ;

DELIMITER //

-- 建立庫存自動更新觸發器
-- 當訂單明細新增時自動扣減庫存
CREATE TRIGGER tr_order_detail_ai
AFTER INSERT ON order_detail
FOR EACH ROW
BEGIN
    -- 宣告區域變數
    -- 儲存當前庫存數量
    DECLARE current_stock INT;
    
    -- 查詢產品當前庫存
    SELECT stock_quantity INTO current_stock
    FROM product
    WHERE product_id = NEW.product_id;
    
    -- 檢查庫存是否充足
    -- 這個檢查在AFTER觸發器中主要用於警示
    IF current_stock < NEW.quantity THEN
        -- 記錄庫存不足警告
        INSERT INTO inventory_alert (
            product_id,
            alert_type,
            alert_message,
            alert_datetime
        ) VALUES (
            NEW.product_id,
            'INSUFFICIENT_STOCK',
            CONCAT('訂單 ', NEW.order_id, ' 的商品庫存不足'),
            NOW()
        );
    END IF;
    
    -- 扣減庫存數量
    -- 即使庫存不足也執行扣減
    -- 允許負庫存以便後續處理
    UPDATE product
    SET stock_quantity = stock_quantity - NEW.quantity,
        last_updated = NOW()
    WHERE product_id = NEW.product_id;
    
    -- 記錄庫存異動
    INSERT INTO inventory_transaction (
        product_id,
        transaction_type,
        quantity_change,
        reference_type,
        reference_id,
        transaction_datetime
    ) VALUES (
        NEW.product_id,
        'SALES',
        -NEW.quantity,  -- 負數表示庫存減少
        'ORDER',
        NEW.order_id,
        NOW()
    );
END//

DELIMITER ;

-- 建立訂單取消觸發器
-- 當訂單明細刪除時自動回復庫存
CREATE TRIGGER tr_order_detail_ad
AFTER DELETE ON order_detail
FOR EACH ROW
BEGIN
    -- 回復庫存數量
    -- 將之前扣減的數量加回去
    UPDATE product
    SET stock_quantity = stock_quantity + OLD.quantity,
        last_updated = NOW()
    WHERE product_id = OLD.product_id;
    
    -- 記錄庫存回復
    INSERT INTO inventory_transaction (
        product_id,
        transaction_type,
        quantity_change,
        reference_type,
        reference_id,
        transaction_datetime
    ) VALUES (
        OLD.product_id,
        'RETURN',
        OLD.quantity,  -- 正數表示庫存增加
        'ORDER_CANCEL',
        OLD.order_id,
        NOW()
    );
END//

DELIMITER ;

DELIMITER //

-- 建立員工薪資驗證觸發器
-- 確保薪資調整符合業務規則
CREATE TRIGGER tr_employee_salary_bu
BEFORE UPDATE ON employee
FOR EACH ROW
BEGIN
    -- 宣告區域變數
    DECLARE salary_increase DECIMAL(15, 2);
    DECLARE increase_percentage DECIMAL(5, 2);
    DECLARE max_allowed_increase DECIMAL(5, 2);
    DECLARE min_salary DECIMAL(15, 2);
    DECLARE max_salary DECIMAL(15, 2);
    
    -- 只有在薪資欄位被修改時才執行驗證
    IF NEW.salary != OLD.salary THEN
        
        -- 計算薪資增加金額與百分比
        SET salary_increase = NEW.salary - OLD.salary;
        SET increase_percentage = (salary_increase / OLD.salary) * 100;
        
        -- 查詢職位的薪資範圍
        SELECT 
            min_salary_range,
            max_salary_range
        INTO 
            min_salary,
            max_salary
        FROM job_position
        WHERE position_id = NEW.position_id;
        
        -- 驗證1: 新薪資不可低於職位最低薪資
        IF NEW.salary < min_salary THEN
            -- 使用SIGNAL拋出錯誤
            -- 阻止資料變更
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '新薪資低於職位最低薪資標準';
        END IF;
        
        -- 驗證2: 新薪資不可超過職位最高薪資
        IF NEW.salary > max_salary THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '新薪資超過職位最高薪資標準';
        END IF;
        
        -- 驗證3: 單次調薪幅度不可超過20%
        -- 避免異常的薪資調整
        IF ABS(increase_percentage) > 20 THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = CONCAT(
                '薪資調整幅度 ',
                ROUND(increase_percentage, 2),
                '% 超過允許範圍 (±20%)'
            );
        END IF;
        
        -- 驗證4: 降薪需要有特殊原因
        -- 防止意外的薪資降低
        IF salary_increase < 0 AND NEW.salary_adjustment_reason IS NULL THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '薪資調降必須提供調整原因';
        END IF;
        
    END IF;
END//

DELIMITER ;

-- 檢查事件排程器狀態
-- 確認事件排程器是否已啟用
SHOW VARIABLES LIKE 'event_scheduler';

-- 啟用事件排程器
-- 需要SUPER權限
SET GLOBAL event_scheduler = ON;

-- 建立每日資料備份事件
-- 每天凌晨2點執行完整資料備份
DELIMITER //

CREATE EVENT evt_daily_backup
-- 設定執行排程
-- EVERY 1 DAY表示每天執行一次
ON SCHEDULE EVERY 1 DAY
-- 設定開始時間為今天的凌晨2點
STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 2 HOUR)
-- 加入註解說明事件用途
COMMENT '每日凌晨2點執行資料備份'
DO
BEGIN
    -- 宣告區域變數
    DECLARE backup_status VARCHAR(50);
    DECLARE backup_file VARCHAR(255);
    
    -- 生成備份檔案名稱
    -- 包含日期時間戳記以便識別
    SET backup_file = CONCAT(
        'backup_',
        DATE_FORMAT(NOW(), '%Y%m%d_%H%i%s'),
        '.sql'
    );
    
    -- 呼叫備份預存程序
    -- 實際的備份邏輯封裝在程序中
    CALL sp_create_database_backup(backup_file, backup_status);
    
    -- 記錄備份執行狀態
    INSERT INTO backup_history (
        backup_datetime,
        backup_file,
        backup_status,
        backup_type
    ) VALUES (
        NOW(),
        backup_file,
        backup_status,
        'DAILY'
    );
    
    -- 清理超過30天的舊備份記錄
    DELETE FROM backup_history
    WHERE backup_datetime < DATE_SUB(NOW(), INTERVAL 30 DAY);
END//

DELIMITER ;

-- 建立過期資料清理事件
-- 每週日凌晨3點清理過期的日誌資料
CREATE EVENT evt_cleanup_old_logs
ON SCHEDULE EVERY 1 WEEK
-- 指定在週日執行
STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 
    (7 - WEEKDAY(CURRENT_DATE)) DAY + INTERVAL 3 HOUR)
COMMENT '每週日凌晨3點清理過期日誌'
DO
BEGIN
    -- 刪除超過90天的系統日誌
    DELETE FROM system_log
    WHERE log_datetime < DATE_SUB(NOW(), INTERVAL 90 DAY);
    
    -- 刪除超過180天的稽核記錄
    DELETE FROM audit_log
    WHERE audit_datetime < DATE_SUB(NOW(), INTERVAL 180 DAY);
    
    -- 刪除超過365天的錯誤日誌
    DELETE FROM error_log
    WHERE error_datetime < DATE_SUB(NOW(), INTERVAL 365 DAY);
    
    -- 記錄清理執行資訊
    INSERT INTO maintenance_log (
        maintenance_type,
        maintenance_datetime,
        records_affected
    ) VALUES (
        'LOG_CLEANUP',
        NOW(),
        ROW_COUNT()
    );
END;

-- 建立一次性事件
-- 在特定時間執行資料遷移任務
CREATE EVENT evt_data_migration
-- AT指定精確的執行時間
ON SCHEDULE AT '2025-12-31 23:00:00'
-- ON COMPLETION PRESERVE保留事件定義
-- 即使執行完成也不刪除
ON COMPLETION PRESERVE
COMMENT '年度資料遷移至歷史表'
DO
BEGIN
    -- 將本年度的交易資料遷移至歷史表
    INSERT INTO transaction_history
    SELECT * FROM transaction
    WHERE transaction_date < '2025-01-01';
    
    -- 刪除已遷移的資料
    DELETE FROM transaction
    WHERE transaction_date < '2025-01-01';
    
    -- 最佳化歷史表
    OPTIMIZE TABLE transaction_history;
    
    -- 記錄遷移完成
    INSERT INTO migration_log (
        migration_type,
        migration_datetime,
        records_migrated
    ) VALUES (
        'ANNUAL_ARCHIVE',
        NOW(),
        ROW_COUNT()
    );
END;

-- 查詢所有事件的資訊
-- 從INFORMATION_SCHEMA取得事件元資料
SELECT 
    event_name AS '事件名稱',
    event_definition AS '事件定義',
    interval_value AS '間隔值',
    interval_field AS '間隔單位',
    starts AS '開始時間',
    ends AS '結束時間',
    status AS '狀態',
    last_executed AS '上次執行',
    event_comment AS '說明'
FROM information_schema.EVENTS
WHERE event_schema = DATABASE()
ORDER BY event_name;

-- 暫時停用事件
-- 不刪除事件定義
ALTER EVENT evt_daily_backup DISABLE;

-- 重新啟用事件
ALTER EVENT evt_daily_backup ENABLE;

-- 修改事件執行時間
-- 變更為每天凌晨3點執行
ALTER EVENT evt_daily_backup
ON SCHEDULE EVERY 1 DAY
STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 3 HOUR);

-- 刪除事件
-- 永久移除事件定義
DROP EVENT IF EXISTS evt_data_migration;