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 '0001' item_id, '0002' item_code union all select '1001' item_id, '1002' item_code union all select '2001' item_id, '2002' item_code union all select '3001' item_id, '3002' item_code union all select '4001' item_id, '4002' item_code ;
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;
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 '0001' item_id, '0002' item_code union all
select '1001' item_id, '1002' item_code union all
select '2001' item_id, '2002' item_code union all
select '3001' item_id, '3002' item_code union all
select '4001' item_id, '4002' item_code ;
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
存储过程的返回值,用call sp(@A);select @A;这样获取。