delimiter $$
use `test`$$
drop procedure if exists `proc_cursor_t`$$
use `test`$$
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
-- 开始业务操作
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/