MySQL 动态游标样例！

delimiter $$
use `test`$$
drop procedure if exists `proc_cursor_t`$$
use `test`$$

CREATE  PROCEDURE `proc_cursor_t`(p_code varchar(30), p_cumu int)
    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 item_id, item_code
    from csf_maec.report_data
    where rpt_code = p_code and cumu = p_cumu        
        and data_scale_code in('UN','HU','TH','TT','MI','TM','HM','BI','TB','HB','TR') limit 5 ; 
      
    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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25267658/viewspace-687835/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25267658/viewspace-687835/