MySQL 存儲進程和Cursor的應用辦法

MySQL 存儲進程和"Cursor"的應用辦法。本站提示廣大學習愛好者：（MySQL 存儲進程和"Cursor"的應用辦法）文章只能為提供參考，不一定能成為您想要的結果。以下是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;
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、留意設置游標的countinue handler：DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;
2、留意REPEAT和UTILE [停滯前提] END REPEAT的應用，不然不會輪回；
3、若何RUN，輸出並履行：call justifyGroupNum()