MySQL 儲存過程游標實戰：批量資料處理與動態 SQL 構建範例

游標基礎語法回顧

DECLARE cursor_name CURSOR FOR SELECT_statement;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cursor_name;
FETCH cursor_name INTO var1, var2, ...;
CLOSE cursor_name;

案例一：批量插入商戶路由關聯資料

場景：從 anl_partner 讀取商戶資訊，依規則生成 MAC 地址與編號，批量寫入 t_machine_sc_config。

DELIMITER $$
CREATE PROCEDURE `批量插入商戶路由關聯資料`()
BEGIN
    DECLARE v_partner_no VARCHAR(32);
    DECLARE v_partner_id INT;
    DECLARE v_sc_pid INT;
    DECLARE v_mac_no VARCHAR(32);
    DECLARE v_mac_addr VARCHAR(32);
    DECLARE n_mac_no BIGINT DEFAULT 100000000;
    DECLARE n_mac_addr BIGINT DEFAULT 1000000000;
    DECLARE n_mac_addr_str VARCHAR(32);
    DECLARE done INT DEFAULT 0;

    DECLARE cur_partnerlist CURSOR FOR 
        SELECT comp_id, partner_no, sc_pid FROM mmm_partner.anl_partner;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur_partnerlist;
    REPEAT
        FETCH cur_partnerlist INTO v_partner_id, v_partner_no, v_sc_pid;
        IF NOT done THEN
            SET v_mac_no = CONCAT('MAC', v_sc_pid, n_mac_no);
            SET n_mac_addr_str = CONCAT(
                SUBSTR(n_mac_addr,1,2),':',SUBSTR(n_mac_addr,3,2),':',
                SUBSTR(n_mac_addr,5,2),':',SUBSTR(n_mac_addr,7,2),':',
                SUBSTR(n_mac_addr,9,2)
            );
            SET v_mac_addr = CONCAT('CC:', n_mac_addr_str);
            SET n_mac_no = n_mac_no + 1;
            SET n_mac_addr = n_mac_addr + 1;

            INSERT INTO t_machine_sc_config(mac_no, partner_no, partner_id, sc_pid, mac_addr, comp_id, is_lock)
            VALUES(v_mac_no, v_partner_no, v_partner_id, v_sc_pid, v_mac_addr, 1, 1);
        END IF;
    UNTIL done END REPEAT;
    CLOSE cur_partnerlist;
END$$
DELIMITER ;

案例二：更新商戶表空白 partner_no

場景：將 partner_no 為空的記錄，按序號批量更新為 2015415parno10000000、2015415parno10000001…

DELIMITER $$
CREATE PROCEDURE `更新商戶表`()
BEGIN
    DECLARE v_partner_id VARCHAR(32);
    DECLARE v_partner_no VARCHAR(32);
    DECLARE n BIGINT DEFAULT 10000000;
    DECLARE done INT DEFAULT 0;

    DECLARE partnerid_list CURSOR FOR 
        SELECT comp_id FROM 100msh_partner.anl_partner WHERE TRIM(partner_no) = '';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN partnerid_list;
    REPEAT
        FETCH partnerid_list INTO v_partner_id;
        IF NOT done THEN
            SET v_partner_no = CONCAT('2015415parno', n);
            SET n = n + 1;
            UPDATE mmm_partner.anl_partner SET partner_no = v_partner_no WHERE comp_id = v_partner_id;
        END IF;
    UNTIL done END REPEAT;
    CLOSE partnerid_list;
END$$
DELIMITER ;

關鍵最佳實踐

1. 必須宣告 NOT FOUND HANDLER，否則 FETCH 到末尾會拋錯誤導致程序中斷。
2. 使用 REPEAT ... UNTIL done END REPEAT 迴圈，配合 HANDLER 變數控制結束。
3. 變數預設值：DECLARE var TYPE DEFAULT value; 避免 NULL 參與運算報錯。
4. 大量寫入考慮事務：可在程序外層包裹 START TRANSACTION; CALL proc(); COMMIT; 或程序內分批提交。
5. 效能提示：單條 INSERT 在迴圈中極慢，建議改用 INSERT ... VALUES (),(),() 批次寫入或 LOAD DATA。

進階：動態 SQL 與 Prepared Statement

若表名、欄位名需動態決定，可在游標迴圈中構建 SQL 字串：

SET @sql = CONCAT('INSERT INTO ', tbl_name, ' VALUES(', val1, ',', val2, ')');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;