create procedure pro_cursor_account_record()
BEGIN
DECLARE no int default 0;
declare counter int ;
DECLARE p_id int(11);
DECLARE cur_1 cursor for select id from ecuser_account_record where username = 'happy'; #缓存数据到游标
declare CONTINUE HANDLER for not found set no = 1; #如果读取完,设置no为1
set counter = 0;# 计数,循环了几次
open cur_1; # 打开游标
while no <> 1 do # 循环读取游标数据
set counter = counter +1;
fetch cur_1 into p_id;# 获取一行数据
# 如果不加判断,会多循环一次。最后一次读取游标时,no已经是1,但是进入循环之前是0,故可以进入循环,会多循环一次;
if no<>1 then
select concat(p_id,'-',counter,'-',no) ;
end if;
end while;
close cur_1; # 关闭游标
END;
更新:20191230
CREATE PROCEDURE `qh`(
OUT `s` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
COMMENT ''
BEGIN
declare a int ;
declare b int;
declare found boolean default true;
declare yb cursor for SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6 UNION
SELECT 7 UNION
SELECT 8 UNION
SELECT 9
;
declare continue handler for not found set found=false;
set a=0 ;
set b=0 ;
open yb;
while found do
fetch yb into a;
IF found
then
set b=a+b;
END IF;
end while;
close yb;
set s=b;
select b;
END
CALL `qh`(@num)
SELECT @num
原因:假设查询出来了3条数据,设置到游标中。最后游标读取完毕的时候, 也就是读到第三行时,no还是0,进入循环,这时fetch游标数据,已经抓取不到数据了,把no设置为1,但是 下面的 select concat(p_id,'-',counter,'-',no) ; 还是会执行一次,故多循环了一次。
解决办法:加上 if no<>1 then 判断。控制范围为需要循环一次处理的所有代码
---------------------
作者:快乐柠檬
来源:CSDN
原文:https://blog.csdn.net/limenghua9112/article/details/49078585
版权声明:本文为博主原创文章,转载请附上博文链接!