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;

• 游標只能在儲存程序/函數/觸發器中使用
• FETCH 逐行讀取，NOT FOUND 處理器用於結束迴圈
• MySQL 游標為唯讀、單向、非捲動

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

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

DELIMITER $$
DROP PROCEDURE IF EXISTS `batch_insert_merchant_route`$$
CREATE PROCEDURE `batch_insert_merchant_route`()
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 done INT DEFAULT 0;

    -- 宣告游標
    DECLARE cur_partner 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_partner;
    read_loop: LOOP
        FETCH cur_partner INTO v_partner_id, v_partner_no, v_sc_pid;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 生成 MAC_NO：MAC + sc_pid + 流水號
        SET v_mac_no = CONCAT('MAC', v_sc_pid, n_mac_no);
        
        -- 生成 MAC_ADDR：CC:XX:XX:XX:XX:XX 格式
        SET v_mac_addr = CONCAT('CC:', 
            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 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 LOOP;
    CLOSE cur_partner;
END$$
DELIMITER ;

關鍵技巧解析

• 變數初始化：DEFAULT 子句簡化宣告
• 迴圈控制：使用 LOOP ... LEAVE 替代 REPEAT UNTIL 0，語意更清晰
• 字串組裝：CONCAT + SUBSTR 格式化 MAC 位址
• 流水號遞增：在迴圈內遞增，確保唯一性

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

場景：將 partner_no 為空的記錄，按 comp_id 批量更新為「2015415parno」+ 流水號格式。

DELIMITER $$
DROP PROCEDURE IF EXISTS `update_empty_partner_no`$$
CREATE PROCEDURE `update_empty_partner_no`()
BEGIN
    DECLARE v_partner_id VARCHAR(32);
    DECLARE v_partner_no VARCHAR(32);
    DECLARE n BIGINT DEFAULT 10000000;
    DECLARE done INT DEFAULT 0;

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

    OPEN cur_partner;
    read_loop: LOOP
        FETCH cur_partner INTO v_partner_id;
        IF done THEN LEAVE read_loop; END IF;

        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 LOOP;
    CLOSE cur_partner;
END$$
DELIMITER ;

進階技巧：動態 SQL 與預處理語句

游標常搭配 PREPARE/EXECUTE 處理動態表名/欄位：

DECLARE cur_tables CURSOR FOR 
    SELECT table_name FROM information_schema.tables 
    WHERE table_schema = 'mydb' AND table_name LIKE 'log_%';

OPEN cur_tables;
table_loop: LOOP
    FETCH cur_tables INTO @tbl;
    IF done THEN LEAVE table_loop; END IF;

    SET @sql = CONCAT('TRUNCATE TABLE ', @tbl);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE cur_tables;

效能與注意事項

1. 集合操作優先：能用單條 INSERT ... SELECT / UPDATE ... JOIN 解決時，勿用游標
2. 批次提交：大量寫入時，每 1000-5000 行 COMMIT 一次，避免長事務鎖死
3. 索引支援：游標查詢的 WHERE 條件欄位需有索引
4. 權限需求：建立儲存程序需 CREATE ROUTINE 權限，執行需 EXECUTE
5. 除錯困難：建議先在測試庫驗證邏輯，再部署生產

呼叫與管理

-- 呼叫
CALL batch_insert_merchant_route();

-- 查看程序
SHOW CREATE PROCEDURE batch_insert_merchant_route;

-- 刪除
DROP PROCEDURE IF EXISTS batch_insert_merchant_route;