在写一个存储过程的时候遇到了一个问题:
countinue handler for not found处理的sql异常不但能捕获游标边界的异常,还能捕获到select id into XXX类似语句的异常?
结果我的存储过程中还用了不少select xx into xxx的语句,每次游标还在执行第一行就结束了。
不知道mysql里面怎么解决这样的问题?上一下demo的代码BEGIN
DECLARE notdone INT DEFAULT 1;
DECLARE aid,abackup,b VARCHAR(255);
DECLARE count int DEFAULT 0;
DECLARE cur CURSOR FOR
SELECT id,`backup`
from advspace where `backup`=1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET notdone = 0; open cur;
fetch cur into aid,abackup;
WHILE notdone DO
set count = count +1;
IF abackup=1 THEN
SELECT id into b from advspace where `backup` = 2;
update advspace set pathNumber = 2;
END if;
fetch cur into aid,abackup;
select aid;
END WHILE;
CLOSE cur;
ENDMySQL
countinue handler for not found处理的sql异常不但能捕获游标边界的异常,还能捕获到select id into XXX类似语句的异常?
结果我的存储过程中还用了不少select xx into xxx的语句,每次游标还在执行第一行就结束了。
不知道mysql里面怎么解决这样的问题?上一下demo的代码BEGIN
DECLARE notdone INT DEFAULT 1;
DECLARE aid,abackup,b VARCHAR(255);
DECLARE count int DEFAULT 0;
DECLARE cur CURSOR FOR
SELECT id,`backup`
from advspace where `backup`=1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET notdone = 0; open cur;
fetch cur into aid,abackup;
WHILE notdone DO
set count = count +1;
IF abackup=1 THEN
SELECT id into b from advspace where `backup` = 2;
update advspace set pathNumber = 2;
END if;
fetch cur into aid,abackup;
select aid;
END WHILE;
CLOSE cur;
ENDMySQL
其中有 消息:不正确的FETCH变量数目。· 错误:1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)
消息:FETCH无数据。· 错误:1330 SQLSTATE: 42000 (ER_SP_DUP_PARAM)
有谁知道这两个有什么区别么?
DECLARE notdone INT DEFAULT 1;
DECLARE aid,abackup,b VARCHAR(255);
DECLARE count int DEFAULT 0;
DECLARE cur CURSOR FOR
SELECT id,`backup`
from advspace where `backup`=1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET notdone = 0;
open cur;
fetch cur into aid,abackup;
WHILE notdone DO
set count = count +1;
IF abackup=1 THEN
SELECT id into b from advspace where `backup` = 2;
update advspace set pathNumber = 2;
END if;
fetch cur into aid,abackup;
select aid;
END WHILE;
CLOSE cur;
END