MySQL存储过程游标（cursor）示例

MySQL中的cursor用法比较特殊，而且很容易忘记，故小记一下：

drop procedure if exists pr_product_fix;

-- ---------------------------------------------------------------------------
-- author      : pcwl
-- create date : 2010-08-21
-- description : fix product detail
-- ---------------------------------------------------------------------------

create procedure pr_product_fix
(
   i_product_id     int
  ,out o_ret        int    -- 0:ok, -1:system error
)
labproc:begin
   declare v_fetch_status int default 1;
   declare v_product_id   int;
   declare v_ret          int;

   -- ------------------------------------------------------------------------
   -- cursor for select products
   -- ------------------------------------------------------------------------
   declare cur_sel cursor for
      select id
        from product
       where id = i_product_id or i_product_id is null
       order by id;

   declare continue handler for NOT FOUND
   begin
      set v_fetch_status = 0;
   end;
      
   declare exit handler for sqlexception
   begin
      rollback;
   end;   

   set o_ret = -1;

   -- ------------------------------------------------------------------------
   -- fix data
   -- ------------------------------------------------------------------------
   open cur_sel;

   lab1:loop
      fetch cur_sel into v_product_id;

      -- fix data ...

      if v_fetch_status = 0 then
         leave lab1;
      end if;
   end loop lab1;

   close cur_sel;

   set o_ret = 0;
end;

下面是去掉干扰部分，只突出显示游标cursor的又一个过程。

drop procedure if exists pr_product_fix;

create procedure pr_product_fix
(
   i_product_id     int
)
begin
   declare v_fetch_status int default 1;
   declare v_product_id   int;

   -- ------------------------------------------------------------------------
   -- cursor for select products
   -- ------------------------------------------------------------------------
   declare cur_sel cursor for
      select id
        from product
       where id = i_product_id or i_product_id is null
       order by id;

   declare continue handler for NOT FOUND
   begin
      set v_fetch_status = 0;
   end;
      
   -- ------------------------------------------------------------------------
   -- open cursor and loop cursor
   -- ------------------------------------------------------------------------
   open cur_sel;

   lab1:loop
      fetch cur_sel into v_product_id;

      -- fix data ...
      -- update product set detail = '' where id = v_product_id;

      if v_fetch_status = 0 then
         leave lab1;
      end if;
   end loop lab1;

   close cur_sel;
end;