MySQL 預存程序與 Cursor 遊標實戰範例

範例：統計各群組有效成員數並更新

CREATE PROCEDURE `justifyGroupNum`()
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    /* how to run: CALL justifyGroupNum() */
    DECLARE p_group_id INT;
    DECLARE p_num INT;
    DECLARE stopFlag INT DEFAULT 0;

    DECLARE cursor_name CURSOR FOR
        SELECT c_group_id, COUNT(*) AS num
        FROM `t_group_member`
        WHERE c_valid IN (3,4)
        GROUP BY c_group_id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag = 1;

    OPEN cursor_name;
    REPEAT
        FETCH cursor_name INTO p_group_id, p_num;
        BEGIN
            UPDATE t_groupinfo SET c_member_number = p_num WHERE c_group_id = p_group_id;
        END;
    UNTIL stopFlag = 1
    END REPEAT;
    CLOSE cursor_name;
END;

關鍵點總結

1. 必須設定遊標的 CONTINUE HANDLER：DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag=1;
2. 使用 REPEAT ... UNTIL stopFlag = 1 END REPEAT 迴圈，否則不會循環。
3. 執行方式：CALL justifyGroupNum()