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()