简介
记录了在MySQL中使用游标和存储过程的写法
delimiter $$
use `uportal`$$
drop procedure if exists `update_refid_feedback`$$
create definer=`root`@`localhost` procedure `update_refid_feedback`()
begin
# 创建接收游标值的变量
declare ex_id varchar(100);
declare app_refid varchar(100);
declare done boolean; # 创建结束变量
# 创建游标
declare cur cursor for (select id,refid from T_CENTER_TCPEXPENSE where refid <> ' ' and type =15);
# 如果没有返回数据 则done赋值为true
declare continue handler for not found set done = true;
# 打开游标
open cur;
# 开始循环
read_loop : loop
# 游标向下走一步
fetch cur into ex_id,app_refid;
if done then # 因为没有数据返回 所以done为true 从而进入if分支
leave read_loop; # 结束循环
close cur; # 关闭游标
end if;
update T_CENTER_TRAVELAPPLY set feedback=1,refid=ex_id where id=app_refid;
end loop;
select * from T_CENTER_TRAVELAPPLY;
end$$
delimiter ;
call update_refid_feedback();