mysql游标实例

DELIMITER $$

DROP PROCEDURE IF EXISTS `proc_cursor_admin` $$
CREATE PROCEDURE `proc_cursor_admin`()
    SQL SECURITY INVOKER
begin
    declare _done int default 0;
    declare v_flag tinyint default 0;
    declare v_item_id int default 0;
    declare v_item_code varchar(20) default '';
    declare cur_code  cursor for select id,name from admin;
    declare continue handler for not found
    begin
        set _done=1;
        select 'not found';
        commit;
    end;
   
    -- handler exception
    declare exit handler for sqlexception
    begin
        -- 异常处理
        select 'exception';
    end;  
   
    -- 打开游标
    open cur_code;
    allcodes:loop
        -- 开始遍历游标
        fetch cur_code into v_item_id, v_item_code;
        if  not _done then  
             -- 开始业务操作
             if p_cumu = 2 then
                 select 'cumu 2', v_item_id, v_item_code,now();
             else
                 select 'cumu 1', v_item_id, v_item_code,now();
            end if;
        else
            leave allcodes;
        end if;
    end loop allcodes;
    -- 关闭游标
    close cur_code;
   
end $$

DELIMITER ;