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博客

MySQL 动态游标样例！

最新文章