当前位置:Gxlcms > mysql > MySQL的存储过程写法和“Cursor”的使用_MySQL

MySQL的存储过程写法和“Cursor”的使用_MySQL

时间:2021-07-01 10:21:17 帮助过:45人阅读

  本文中介绍了一个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()

人气教程排行