MySQL 迴圈控制與儲存過程進階實戰指南

-- REPEAT 迴圈實現累加計算的完整範例
-- 此儲存過程展示了 REPEAT 迴圈的基本結構與執行流程

DELIMITER //

CREATE PROCEDURE demo_repeat_sum_calculation()
BEGIN
    -- 宣告計數器變數
    -- 使用 INT 資料型別儲存整數值
    -- DEFAULT 關鍵字設定初始值為 1
    DECLARE counter INT DEFAULT 1;

    -- 宣告累加器變數
    -- 用於儲存累加的總和結果
    DECLARE total_sum INT DEFAULT 0;

    -- 設定計算的目標值
    -- 我們要計算從 1 到 10 的總和
    DECLARE target_number INT DEFAULT 10;

    -- REPEAT 迴圈開始
    -- 迴圈體內的程式碼至少會執行一次
    REPEAT
        -- 將目前計數器的值加入累加器
        -- 這是核心的累加運算邏輯
        SET total_sum = total_sum + counter;

        -- 遞增計數器,準備處理下一個數字
        -- 每次迭代都會使計數器增加 1
        SET counter = counter + 1;

    -- UNTIL 子句定義終止條件
    -- 當 counter 的值超過 target_number 時,迴圈結束
    -- 注意:這是終止條件,而非繼續條件
    UNTIL counter > target_number
    END REPEAT;

    -- 輸出計算結果
    -- 使用 SELECT 陳述式將結果傳回給呼叫者
    -- 此時 total_sum 應該是 1+2+3+...+10 = 55
    SELECT 
        target_number AS '計算範圍',
        total_sum AS '累加總和',
        CONCAT('1 到 ', target_number, ' 的總和為 ', total_sum) AS '結果描述';

END //

DELIMITER ;

-- 執行儲存過程來觀察 REPEAT 迴圈的執行結果
CALL demo_repeat_sum_calculation();

-- WHILE 迴圈實現階乘計算的完整範例
-- 此儲存過程展示了 WHILE 迴圈的控制結構與應用技巧

DELIMITER //

CREATE PROCEDURE demo_while_factorial_calculation(
    -- 輸入參數:要計算階乘的目標數值
    -- 使用 IN 關鍵字明確標示這是輸入參數
    IN target_number INT
)
BEGIN
    -- 宣告計數器變數,從 1 開始
    -- 這個變數會在每次迭代中遞增
    DECLARE counter INT DEFAULT 1;

    -- 宣告階乘結果變數
    -- 使用 BIGINT 資料型別以支援較大的階乘值
    -- 初始值設為 1,因為任何數乘以 1 等於其自身
    DECLARE factorial_result BIGINT DEFAULT 1;

    -- 參數驗證:確保輸入的數值是非負整數
    -- 階乘只對非負整數有定義
    IF target_number < 0 THEN
        -- 使用 SIGNAL 產生錯誤訊息
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '錯誤:階乘運算不支援負數';
    END IF;

    -- 特殊情況處理:0 的階乘定義為 1
    IF target_number = 0 THEN
        SET factorial_result = 1;
    ELSE
        -- WHILE 迴圈開始
        -- 條件:當 counter 小於或等於 target_number 時繼續執行
        -- 注意:這是繼續條件,與 REPEAT 的終止條件不同
        WHILE counter <= target_number DO
            -- 階乘運算的核心邏輯
            -- 將目前的結果乘以計數器值
            SET factorial_result = factorial_result * counter;

            -- 遞增計數器,準備下一次迭代
            SET counter = counter + 1;

        END WHILE;
    END IF;

    -- 輸出計算結果
    -- 提供詳細的結果資訊供呼叫者參考
    SELECT 
        target_number AS '計算數值',
        factorial_result AS '階乘結果',
        CONCAT(target_number, '! = ', factorial_result) AS '運算式';

END //

DELIMITER ;

-- 執行儲存過程測試不同的輸入值
CALL demo_while_factorial_calculation(5);  -- 5! = 120
CALL demo_while_factorial_calculation(10); -- 10! = 3,628,800
CALL demo_while_factorial_calculation(0);  -- 0! = 1

-- LOOP 結構搭配標籤的進階應用範例
-- 此範例展示了複雜的流程控制邏輯

DELIMITER //

CREATE PROCEDURE demo_advanced_loop_control()
BEGIN
    -- 宣告控制變數
    DECLARE counter INT DEFAULT 0;
    
    -- 宣告累加器變數
    -- 分別儲存偶數和奇數的總和
    DECLARE sum_even INT DEFAULT 0;
    DECLARE sum_odd INT DEFAULT 0;
    
    -- 宣告結果描述變數
    DECLARE result_description VARCHAR(500);

    -- 定義外層迴圈標籤
    -- 標籤名稱應該具有描述性,說明迴圈的用途
    main_calculation_loop: LOOP
        -- 遞增計數器
        SET counter = counter + 1;

        -- 設定迴圈終止條件
        -- LEAVE 陳述式用於跳出標籤所指定的迴圈
        -- 這裡我們設定當 counter 超過 20 時結束迴圈
        IF counter > 20 THEN
            LEAVE main_calculation_loop;
        END IF;

        -- 跳過特定條件的數值
        -- ITERATE 陳述式用於跳過本次迭代的剩餘程式碼
        -- 直接進入下一次迭代
        -- 這裡我們跳過所有 5 的倍數
        IF counter MOD 5 = 0 THEN
            ITERATE main_calculation_loop;
        END IF;

        -- 根據奇偶性進行不同的處理
        -- 使用 MOD 運算子判斷是否為偶數
        IF counter MOD 2 = 0 THEN
            -- 偶數累加
            SET sum_even = sum_even + counter;
        ELSE
            -- 奇數累加
            SET sum_odd = sum_odd + counter;
        END IF;

    END LOOP main_calculation_loop;

    -- 建立詳細的結果描述
    SET result_description = CONCAT(
        '處理範圍: 1 到 20 (排除 5, 10, 15, 20)',
        '\n偶數: 2+4+6+8+12+14+16+18 = ', sum_even,
        '\n奇數: 1+3+7+9+11+13+17+19 = ', sum_odd,
        '\n總和: ', sum_even + sum_odd
    );

    -- 輸出計算結果
    SELECT 
        sum_even AS '偶數總和',
        sum_odd AS '奇數總和',
        sum_even + sum_odd AS '整體總和',
        result_description AS '詳細說明';

END //

DELIMITER ;

-- 執行儲存過程觀察結果
CALL demo_advanced_loop_control();

-- 建立具有多個輸入參數的儲存過程
-- 此範例展示了參數驗證與動態查詢的實作技巧

DELIMITER //

CREATE PROCEDURE sp_query_county_population(
    -- 輸入參數:州份名稱
    -- 使用 _param 後綴命名避免與欄位名稱衝突
    IN state_param VARCHAR(100),
    
    -- 輸入參數:最小人口數門檻
    -- 允許 NULL 值表示不限制最小人口數
    IN min_population_param INT,
    
    -- 輸入參數:排序欄位
    -- 控制結果的排序方式
    IN sort_by_param VARCHAR(20)
)
BEGIN
    -- 宣告區域變數用於建構動態 SQL
    DECLARE sql_statement VARCHAR(1000);
    DECLARE where_clause VARCHAR(500) DEFAULT '';
    DECLARE order_clause VARCHAR(100) DEFAULT '';

    -- 參數驗證:確保 state_param 不為空
    IF state_param IS NULL OR TRIM(state_param) = '' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '錯誤:州份名稱不得為空';
    END IF;

    -- 建構 WHERE 子句
    SET where_clause = CONCAT('state = ''', state_param, '''');
    
    -- 如果指定了最小人口數,加入相應的條件
    IF min_population_param IS NOT NULL AND min_population_param > 0 THEN
        SET where_clause = CONCAT(
            where_clause,
            ' AND population >= ',
            min_population_param
        );
    END IF;

    -- 建構 ORDER BY 子句
    -- 根據參數決定排序方式
    IF sort_by_param = 'population_desc' THEN
        SET order_clause = 'ORDER BY population DESC';
    ELSEIF sort_by_param = 'population_asc' THEN
        SET order_clause = 'ORDER BY population ASC';
    ELSEIF sort_by_param = 'name' THEN
        SET order_clause = 'ORDER BY county ASC';
    ELSE
        -- 預設排序方式
        SET order_clause = 'ORDER BY population DESC';
    END IF;

    -- 建構完整的查詢陳述式
    SET sql_statement = CONCAT(
        'SELECT ',
        'county AS 縣市名稱, ',
        'FORMAT(population, 0) AS 人口數, ',
        'ROUND(population / 1000000, 2) AS 人口百萬 ',
        'FROM county_population ',
        'WHERE ', where_clause, ' ',
        order_clause
    );

    -- 執行動態 SQL
    -- 使用 PREPARE 和 EXECUTE 執行動態建構的查詢
    SET @dynamic_sql = sql_statement;
    PREPARE stmt FROM @dynamic_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END //

DELIMITER ;

-- 呼叫儲存過程的多種方式
CALL sp_query_county_population('New York', NULL, 'population_desc');
CALL sp_query_county_population('California', 1000000, 'name');
CALL sp_query_county_population('Texas', 500000, 'population_asc');

-- 建立具有多個輸出參數的儲存過程
-- 此範例展示了如何同時傳回多個計算結果

DELIMITER //

CREATE PROCEDURE sp_calculate_population_statistics(
    -- 輸入參數:要分析的州份名稱
    IN state_param VARCHAR(100),
    
    -- 輸出參數:該州的總人口數
    OUT total_population_param BIGINT,
    
    -- 輸出參數:縣市數量
    OUT county_count_param INT,
    
    -- 輸出參數:平均人口數
    OUT average_population_param DECIMAL(15,2),
    
    -- 輸出參數:人口最多的縣市名稱
    OUT largest_county_param VARCHAR(100)
)
BEGIN
    -- 查詢總人口數和縣市數量
    -- 使用 SELECT...INTO 語法將結果存入輸出參數
    SELECT 
        SUM(population),
        COUNT(*)
    INTO 
        total_population_param,
        county_count_param
    FROM county_population
    WHERE state = state_param;

    -- 檢查查詢結果是否有效
    -- 如果沒有找到資料,輸出參數會是 NULL
    IF county_count_param IS NULL OR county_count_param = 0 THEN
        -- 設定預設值表示沒有找到資料
        SET total_population_param = 0;
        SET county_count_param = 0;
        SET average_population_param = 0;
        SET largest_county_param = 'N/A';
    ELSE
        -- 計算平均人口數
        SET average_population_param = total_population_param / county_count_param;

        -- 查詢人口最多的縣市
        SELECT county
        INTO largest_county_param
        FROM county_population
        WHERE state = state_param
        ORDER BY population DESC
        LIMIT 1;
    END IF;

END //

DELIMITER ;

-- 使用輸出參數的標準方式
-- 需要提供使用者變數來接收輸出值
CALL sp_calculate_population_statistics(
    'New York',
    @total_pop,
    @county_cnt,
    @avg_pop,
    @largest_county
);

-- 檢視所有輸出參數的值
SELECT 
    FORMAT(@total_pop, 0) AS '總人口數',
    @county_cnt AS '縣市數量',
    FORMAT(@avg_pop, 0) AS '平均人口數',
    @largest_county AS '最大縣市';

-- 建立使用 INOUT 參數的儲存過程
-- 此範例展示了參數值的雙向傳遞

DELIMITER //

CREATE PROCEDURE sp_apply_population_growth(
    -- INOUT 參數:傳入目前人口,傳回成長後的人口
    INOUT population_param INT,
    
    -- 輸入參數:年度成長率(百分比)
    IN annual_growth_rate_param DECIMAL(5,2),
    
    -- 輸入參數:計算年數
    IN years_param INT,
    
    -- 輸出參數:總成長量
    OUT growth_amount_param INT
)
BEGIN
    -- 宣告區域變數
    DECLARE year_counter INT DEFAULT 0;
    DECLARE original_population INT;

    -- 儲存原始人口數用於計算成長量
    SET original_population = population_param;

    -- 參數驗證
    IF population_param <= 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '錯誤:人口數必須大於零';
    END IF;

    IF annual_growth_rate_param < -100 OR annual_growth_rate_param > 100 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '錯誤:成長率必須在 -100% 到 100% 之間';
    END IF;

    IF years_param <= 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '錯誤:計算年數必須大於零';
    END IF;

    -- 使用複利公式計算人口成長
    -- 逐年計算以提高精確度
    WHILE year_counter < years_param DO
        -- 計算該年度的人口成長
        SET population_param = ROUND(
            population_param * (1 + annual_growth_rate_param / 100)
        );
        
        -- 遞增年度計數器
        SET year_counter = year_counter + 1;
    END WHILE;

    -- 計算總成長量
    SET growth_amount_param = population_param - original_population;

END //

DELIMITER ;

-- 使用 INOUT 參數的範例
SET @current_population = 1000000;
CALL sp_apply_population_growth(
    @current_population,  -- INOUT: 原始值 1000000
    2.5,                   -- 年成長率 2.5%
    10,                    -- 計算 10 年
    @growth_amount        -- 輸出總成長量
);

SELECT 
    FORMAT(@current_population, 0) AS '預估人口',
    FORMAT(@growth_amount, 0) AS '成長數量',
    CONCAT(ROUND(@growth_amount / 1000000 * 100, 2), '%') AS '成長百分比';

-- 設計模組化的儲存過程系統
-- 首先建立基礎的查詢模組

DELIMITER //

CREATE PROCEDURE sp_get_state_population(
    IN state_param VARCHAR(100),
    OUT population_param BIGINT
)
BEGIN
    -- 查詢指定州份的人口總數
    SELECT SUM(population)
    INTO population_param
    FROM county_population
    WHERE state = state_param;

    -- 如果沒有找到資料,設定為 0
    IF population_param IS NULL THEN
        SET population_param = 0;
    END IF;
END //

CREATE PROCEDURE sp_get_county_count(
    IN state_param VARCHAR(100),
    OUT count_param INT
)
BEGIN
    -- 查詢指定州份的縣市數量
    SELECT COUNT(*)
    INTO count_param
    FROM county_population
    WHERE state = state_param;
END //

-- 建立組合多個模組的主控儲存過程

CREATE PROCEDURE sp_generate_population_report(
    IN state_list_param VARCHAR(500)
)
BEGIN
    -- 宣告變數
    DECLARE state_name VARCHAR(100);
    DECLARE state_pop BIGINT;
    DECLARE county_cnt INT;
    DECLARE position INT DEFAULT 1;
    DECLARE delimiter_pos INT;
    DECLARE remaining_list VARCHAR(500);

    -- 建立暫存表儲存報表結果
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_report (
        state_name VARCHAR(100),
        total_population BIGINT,
        county_count INT,
        average_population DECIMAL(15,2)
    );

    -- 清空暫存表
    TRUNCATE TABLE temp_report;

    -- 初始化剩餘清單
    SET remaining_list = state_list_param;

    -- 解析州份清單(以逗號分隔)
    parse_loop: LOOP
        -- 如果剩餘清單為空,結束迴圈
        IF remaining_list IS NULL OR TRIM(remaining_list) = '' THEN
            LEAVE parse_loop;
        END IF;

        -- 尋找下一個逗號的位置
        SET delimiter_pos = LOCATE(',', remaining_list);

        -- 如果找到逗號,提取州份名稱
        IF delimiter_pos > 0 THEN
            SET state_name = TRIM(SUBSTRING(remaining_list, 1, delimiter_pos - 1));
            SET remaining_list = SUBSTRING(remaining_list, delimiter_pos + 1);
        ELSE
            -- 這是最後一個州份
            SET state_name = TRIM(remaining_list);
            SET remaining_list = '';
        END IF;

        -- 呼叫模組化的儲存過程取得資料
        CALL sp_get_state_population(state_name, state_pop);
        CALL sp_get_county_count(state_name, county_cnt);

        -- 將結果插入暫存表
        IF county_cnt > 0 THEN
            INSERT INTO temp_report VALUES (
                state_name,
                state_pop,
                county_cnt,
                state_pop / county_cnt
            );
        END IF;

    END LOOP parse_loop;

    -- 輸出完整的報表
    SELECT 
        state_name AS '州份',
        FORMAT(total_population, 0) AS '總人口',
        county_count AS '縣市數',
        FORMAT(average_population, 0) AS '平均人口',
        CONCAT(
            ROUND(total_population / 1000000, 1),
            'M'
        ) AS '人口簡記'
    FROM temp_report
    ORDER BY total_population DESC;

    -- 清理暫存表
    DROP TEMPORARY TABLE IF EXISTS temp_report;

END //

DELIMITER ;

-- 執行主控儲存過程
CALL sp_generate_population_report('New York,California,Texas,Florida');

-- 使用遊標實現複雜的資料處理邏輯
-- 此範例展示了遊標的完整使用流程

DELIMITER //

CREATE PROCEDURE sp_split_large_counties(
    IN population_threshold_param INT
)
BEGIN
    -- ==================== 變數宣告區 ====================
    -- 注意:在 MySQL 中,所有變數宣告必須在其他陳述式之前

    -- 宣告用於儲存遊標讀取值的變數
    DECLARE v_county_id INT;
    DECLARE v_state VARCHAR(100);
    DECLARE v_county VARCHAR(100);
    DECLARE v_population INT;

    -- 宣告用於計算的區域變數
    DECLARE v_split_count INT;
    DECLARE v_split_population INT;
    DECLARE v_counter INT;

    -- 宣告迴圈控制變數
    -- 用於判斷遊標是否已讀取完所有記錄
    DECLARE done BOOLEAN DEFAULT FALSE;

    -- 宣告統計變數
    DECLARE total_processed INT DEFAULT 0;
    DECLARE total_created INT DEFAULT 0;

    -- ==================== 遊標宣告區 ====================
    -- 宣告遊標,定義要處理的資料集
    -- 選擇人口超過指定門檻的縣市
    DECLARE county_cursor CURSOR FOR
        SELECT 
            county_id,
            state,
            county,
            population
        FROM county_population
        WHERE population > population_threshold_param
        ORDER BY population DESC;

    -- ==================== 例外處理器宣告區 ====================
    -- 宣告 NOT FOUND 處理器
    -- 當遊標讀取完所有記錄時,會觸發此處理器
    -- CONTINUE 表示繼續執行後續程式碼
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- ==================== 主要處理邏輯 ====================

    -- 建立暫存表儲存處理記錄
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_split_log (
        original_county VARCHAR(100),
        original_population INT,
        split_count INT,
        split_population INT,
        process_timestamp DATETIME
    );

    -- 開啟遊標,執行關聯的 SELECT 陳述式
    OPEN county_cursor;

    -- 使用標籤定義外層迴圈
    fetch_loop: LOOP
        -- 從遊標讀取一筆記錄到變數中
        FETCH county_cursor INTO 
            v_county_id,
            v_state,
            v_county,
            v_population;

        -- 檢查是否已讀取完所有記錄
        IF done THEN
            LEAVE fetch_loop;
        END IF;

        -- 計算需要分割成幾個行政區
        -- 假設每個行政區的目標人口不超過 100 萬
        SET v_split_count = CEIL(v_population / 1000000);
        
        -- 計算每個分割行政區的人口數
        SET v_split_population = FLOOR(v_population / v_split_count);

        -- 初始化分割計數器
        SET v_counter = 1;

        -- 內層迴圈:建立分割後的行政區
        split_loop: LOOP
            -- 插入新的分割行政區
            -- 行政區名稱加上編號後綴
            INSERT INTO county_population (
                state,
                county,
                population,
                created_from_split,
                created_date
            )
            VALUES (
                v_state,
                CONCAT(v_county, '-分區', v_counter),
                IF(v_counter = v_split_count,
                   -- 最後一個分割區包含剩餘的人口
                   v_population - (v_split_population * (v_split_count - 1)),
                   v_split_population
                ),
                TRUE,
                NOW()
            );

            -- 更新建立的行政區計數
            SET total_created = total_created + 1;

            -- 遞增分割計數器
            SET v_counter = v_counter + 1;

            -- 檢查是否已建立足夠的分割行政區
            IF v_counter > v_split_count THEN
                LEAVE split_loop;
            END IF;

        END LOOP split_loop;

        -- 記錄處理日誌
        INSERT INTO temp_split_log VALUES (
            v_county,
            v_population,
            v_split_count,
            v_split_population,
            NOW()
        );

        -- 刪除原始的大型縣市記錄
        DELETE FROM county_population
        WHERE county_id = v_county_id;

        -- 更新處理計數
        SET total_processed = total_processed + 1;

    END LOOP fetch_loop;

    -- 關閉遊標,釋放資源
    CLOSE county_cursor;

    -- 輸出處理摘要
    SELECT 
        total_processed AS '處理的原始縣市數',
        total_created AS '建立的分割行政區數',
        population_threshold_param AS '人口門檻';

    -- 輸出詳細處理記錄
    SELECT 
        original_county AS '原始縣市',
        FORMAT(original_population, 0) AS '原始人口',
        split_count AS '分割數量',
        FORMAT(split_population, 0) AS '每區人口',
        DATE_FORMAT(process_timestamp, '%Y-%m-%d %H:%i:%s') AS '處理時間'
    FROM temp_split_log
    ORDER BY original_population DESC;

    -- 清理暫存表
    DROP TEMPORARY TABLE IF EXISTS temp_split_log;

END //

DELIMITER ;

-- 執行儲存過程
CALL sp_split_large_counties(2000000);

-- ==================== 建立稽核記錄表 ====================
-- 此表用於儲存所有對 payable 表的變更記錄

CREATE TABLE IF NOT EXISTS payable_audit (
    -- 稽核記錄的唯一識別碼
    -- 使用自動遞增確保每筆記錄有唯一 ID
    audit_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    
    -- 變更發生的精確時間戳記
    -- 使用 DATETIME(6) 包含微秒精度
    audit_datetime DATETIME(6) NOT NULL,
    
    -- 執行變更的資料庫使用者
    -- 儲存完整的使用者識別資訊
    audit_user VARCHAR(200) NOT NULL,
    
    -- 變更類型:INSERT、UPDATE 或 DELETE
    audit_action VARCHAR(10) NOT NULL,
    
    -- 受影響的記錄識別碼
    -- 用於追溯特定記錄的變更歷史
    affected_record_id INT,
    
    -- 變更的詳細描述
    -- 使用 TEXT 資料型別支援長文字
    audit_change TEXT,
    
    -- 變更前的完整記錄(JSON 格式)
    -- 僅用於 UPDATE 和 DELETE 操作
    old_values JSON,
    
    -- 變更後的完整記錄(JSON 格式)
    -- 僅用於 INSERT 和 UPDATE 操作
    new_values JSON,
    
    -- 建立索引以加速查詢
    INDEX idx_audit_datetime (audit_datetime),
    INDEX idx_audit_user (audit_user),
    INDEX idx_audit_action (audit_action),
    INDEX idx_affected_record (affected_record_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==================== 建立 BEFORE INSERT 觸發器 ====================
-- 用於驗證和修正即將插入的資料

DELIMITER //

CREATE TRIGGER tr_payable_before_insert
BEFORE INSERT ON payable
FOR EACH ROW
BEGIN
    -- 驗證金額必須為正數
    -- 負數金額在業務邏輯上不合理
    IF NEW.amount <= 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '錯誤:應付金額必須大於零';
    END IF;

    -- 驗證公司名稱不得為空
    -- 空白的公司名稱會造成資料品質問題
    IF NEW.company IS NULL OR TRIM(NEW.company) = '' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '錯誤:公司名稱不得為空白';
    END IF;

    -- 自動修正:將公司名稱轉為大寫並移除多餘空白
    -- 這確保了資料的一致性
    SET NEW.company = UPPER(TRIM(NEW.company));

    -- 自動修正:移除服務描述的前後空白
    IF NEW.service IS NOT NULL THEN
        SET NEW.service = TRIM(NEW.service);
    END IF;

    -- 如果未設定建立時間,自動設定為目前時間
    IF NEW.created_date IS NULL THEN
        SET NEW.created_date = NOW();
    END IF;

    -- 設定最後修改時間
    SET NEW.last_modified_date = NOW();

END //

-- ==================== 建立 AFTER INSERT 觸發器 ====================
-- 用於記錄新增操作的稽核日誌

CREATE TRIGGER tr_payable_after_insert
AFTER INSERT ON payable
FOR EACH ROW
BEGIN
    -- 宣告變數用於建構 JSON
    DECLARE new_values_json JSON;

    -- 建立新記錄的 JSON 表示
    SET new_values_json = JSON_OBJECT(
        'payable_id', NEW.payable_id,
        'company', NEW.company,
        'amount', NEW.amount,
        'service', NEW.service,
        'created_date', NEW.created_date
    );

    -- 插入稽核記錄
    INSERT INTO payable_audit (
        audit_datetime,
        audit_user,
        audit_action,
        affected_record_id,
        audit_change,
        old_values,
        new_values
    )
    VALUES (
        NOW(6),  -- 使用微秒精度的時間戳記
        USER(),  -- 目前的資料庫使用者
        'INSERT',
        NEW.payable_id,
        CONCAT(
            '新增應付款項 - ',
            '公司:', NEW.company, ', ',
            '金額:', FORMAT(NEW.amount, 2), ', ',
            '服務:', COALESCE(NEW.service, 'N/A')
        ),
        NULL,  -- INSERT 操作沒有舊值
        new_values_json
    );

END //

-- ==================== 建立 BEFORE UPDATE 觸發器 ====================
-- 用於驗證更新操作並記錄變更

CREATE TRIGGER tr_payable_before_update
BEFORE UPDATE ON payable
FOR EACH ROW
BEGIN
    -- 驗證更新後的金額仍然為正數
    IF NEW.amount <= 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '錯誤:更新後的金額必須大於零';
    END IF;

    -- 驗證更新後的公司名稱不得為空
    IF NEW.company IS NULL OR TRIM(NEW.company) = '' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '錯誤:更新後的公司名稱不得為空白';
    END IF;

    -- 自動修正資料格式
    SET NEW.company = UPPER(TRIM(NEW.company));
    
    IF NEW.service IS NOT NULL THEN
        SET NEW.service = TRIM(NEW.service);
    END IF;

    -- 更新最後修改時間
    SET NEW.last_modified_date = NOW();

    -- 如果金額變更超過 20%,記錄警告
    IF ABS((NEW.amount - OLD.amount) / OLD.amount) > 0.2 THEN
        INSERT INTO payable_alerts (
            alert_datetime,
            payable_id,
            alert_type,
            alert_message
        )
        VALUES (
            NOW(),
            NEW.payable_id,
            'LARGE_AMOUNT_CHANGE',
            CONCAT(
                '金額變動超過 20%: ',
                FORMAT(OLD.amount, 2), ' -> ', FORMAT(NEW.amount, 2)
            )
        );
    END IF;

END //

-- ==================== 建立 AFTER UPDATE 觸發器 ====================
-- 用於詳細記錄更新操作的稽核日誌

CREATE TRIGGER tr_payable_after_update
AFTER UPDATE ON payable
FOR EACH ROW
BEGIN
    -- 宣告變數
    DECLARE change_description TEXT DEFAULT '';
    DECLARE old_values_json JSON;
    DECLARE new_values_json JSON;
    DECLARE has_changes BOOLEAN DEFAULT FALSE;

    -- 建構詳細的變更描述
    -- 只記錄實際發生變更的欄位

    IF OLD.company != NEW.company THEN
        SET change_description = CONCAT(
            change_description,
            '公司名稱: [', OLD.company, '] → [', NEW.company, ']; '
        );
        SET has_changes = TRUE;
    END IF;

    IF OLD.amount != NEW.amount THEN
        SET change_description = CONCAT(
            change_description,
            '金額: [', FORMAT(OLD.amount, 2), '] → [', FORMAT(NEW.amount, 2), ']; '
        );
        SET has_changes = TRUE;
    END IF;

    IF (OLD.service IS NULL AND NEW.service IS NOT NULL) OR
       (OLD.service IS NOT NULL AND NEW.service IS NULL) OR
       (OLD.service != NEW.service) THEN
        SET change_description = CONCAT(
            change_description,
            '服務: [', COALESCE(OLD.service, 'N/A'), '] → [', COALESCE(NEW.service, 'N/A'), ']; '
        );
        SET has_changes = TRUE;
    END IF;

    -- 只有在確實有變更時才記錄
    IF has_changes THEN
        -- 建立 JSON 格式的舊值和新值記錄
        SET old_values_json = JSON_OBJECT(
            'payable_id', OLD.payable_id,
            'company', OLD.company,
            'amount', OLD.amount,
            'service', OLD.service
        );

        SET new_values_json = JSON_OBJECT(
            'payable_id', NEW.payable_id,
            'company', NEW.company,
            'amount', NEW.amount,
            'service', NEW.service
        );

        -- 插入詳細的稽核記錄
        INSERT INTO payable_audit (
            audit_datetime,
            audit_user,
            audit_action,
            affected_record_id,
            audit_change,
            old_values,
            new_values
        )
        VALUES (
            NOW(6),
            USER(),
            'UPDATE',
            NEW.payable_id,
            CONCAT('更新記錄 ID ', NEW.payable_id, ' - ', change_description),
            old_values_json,
            new_values_json
        );
    END IF;

END //

-- ==================== 建立 AFTER DELETE 觸發器 ====================
-- 用於記錄刪除操作的稽核日誌

CREATE TRIGGER tr_payable_after_delete
AFTER DELETE ON payable
FOR EACH ROW
BEGIN
    -- 宣告變數
    DECLARE old_values_json JSON;

    -- 建立被刪除記錄的 JSON 表示
    SET old_values_json = JSON_OBJECT(
        'payable_id', OLD.payable_id,
        'company', OLD.company,
        'amount', OLD.amount,
        'service', OLD.service
    );

    -- 記錄刪除操作
    -- DELETE 操作只有 OLD 值,沒有 NEW 值
    INSERT INTO payable_audit (
        audit_datetime,
        audit_user,
        audit_action,
        affected_record_id,
        audit_change,
        old_values,
        new_values
    )
    VALUES (
        NOW(6),
        USER(),
        'DELETE',
        OLD.payable_id,
        CONCAT(
            '刪除應付款項 - ',
            'ID:', OLD.payable_id, ', ',
            '公司:', OLD.company, ', ',
            '金額:', FORMAT(OLD.amount, 2), ', ',
            '服務:', COALESCE(OLD.service, 'N/A')
        ),
        old_values_json,
        NULL  -- DELETE 操作沒有新值
    );

END //

DELIMITER ;

-- ==================== 完整的資料遷移系統 ====================
-- 整合迴圈控制、儲存過程、遊標和觸發器的企業級範例

DELIMITER //

-- 主控遷移儲存過程
CREATE PROCEDURE sp_migrate_population_data(
    IN batch_size_param INT,
    IN validation_level_param VARCHAR(20),
    OUT total_migrated_param INT,
    OUT total_errors_param INT
)
BEGIN
    -- 變數宣告
    DECLARE v_batch_count INT DEFAULT 0;
    DECLARE v_record_count INT DEFAULT 0;
    DECLARE v_error_count INT DEFAULT 0;
    DECLARE v_continue BOOLEAN DEFAULT TRUE;
    
    -- 建立遷移日誌表
    CREATE TEMPORARY TABLE IF NOT EXISTS migration_log (
        log_id INT AUTO_INCREMENT PRIMARY KEY,
        batch_number INT,
        records_processed INT,
        errors_encountered INT,
        start_time DATETIME,
        end_time DATETIME,
        status VARCHAR(20)
    );

    -- 開始遷移迴圈
    WHILE v_continue DO
        SET v_batch_count = v_batch_count + 1;
        
        -- 呼叫批次處理儲存過程
        CALL sp_process_migration_batch(
            batch_size_param,
            validation_level_param,
            @batch_records,
            @batch_errors
        );
        
        -- 累積統計資訊
        SET v_record_count = v_record_count + @batch_records;
        SET v_error_count = v_error_count + @batch_errors;
        
        -- 記錄批次處理結果
        INSERT INTO migration_log (
            batch_number,
            records_processed,
            errors_encountered,
            end_time,
            status
        )
        VALUES (
            v_batch_count,
            @batch_records,
            @batch_errors,
            NOW(),
            IF(@batch_records = 0, 'COMPLETED', 'SUCCESS')
        );
        
        -- 檢查是否還有更多資料需要處理
        IF @batch_records = 0 THEN
            SET v_continue = FALSE;
        END IF;
        
    END WHILE;

    -- 設定輸出參數
    SET total_migrated_param = v_record_count;
    SET total_errors_param = v_error_count;

    -- 輸出遷移摘要
    SELECT 
        v_batch_count AS '批次數量',
        FORMAT(v_record_count, 0) AS '遷移記錄數',
        FORMAT(v_error_count, 0) AS '錯誤數量',
        CONCAT(
            ROUND((v_record_count - v_error_count) / v_record_count * 100, 2),
            '%'
        ) AS '成功率';

END //

DELIMITER ;