MySQL DECLARE CONTINUE HANDLER FOR NOT FOUND 不工作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7712110/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DECLARE CONTINUE HANDLER FOR NOT FOUND is not working
提问by siri
Hi i have the following stored procedure
嗨，我有以下存储过程
DROP PROCEDURE IF EXISTS `p25`$$
CREATE DEFINER=`root`@`%` PROCEDURE `p25`()
BEGIN
DECLARE b BOOLEAN;
DECLARE a VARCHAR(10);
DECLARE cur_1 CURSOR FOR SELECT t FROM sample_table;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = TRUE;
OPEN cur_1;
lbl:LOOP
IF b = TRUE THEN
LEAVE lbl;
END IF;
IF NOT b= TRUE THEN
FETCH cur_1 INTO a;
END IF;
END LOOP;
CLOSE cur_1;
END$$
the select statements actually returns 5 rows but the above procedure after one row is fetched it is not looping through other rows. and the b value is never set to true and it is going to infinite loop because of that. is there any problem with my code. please some one help me..
select 语句实际上返回 5 行，但在获取一行后上述过程不会循环遍历其他行。并且 b 值永远不会设置为 true，因此它将无限循环。我的代码有问题吗。请有人帮助我..
I got the solution for this, thanks for helping me. i think the way i am checking the values is wrong. So now, i want to fetch the values from some other table depending on the row value fetched. how can i print the result like ||current row value || the values fetched from other table|| as a result for all the rows fetched.
我得到了解决方案，谢谢你的帮助。我认为我检查值的方式是错误的。所以现在，我想根据获取的行值从其他表中获取值。我如何打印结果，如 ||current 行值 || 从其他表中获取的值|| 结果是所有获取的行。
采纳答案by Devart
Try to change -
尝试改变——
IF NOT b= TRUE THEN
FETCH cur_1 INTO a;
with -
和 -
IF b IS NULL THEN
FETCH cur_1 INTO a;
Try this code -
试试这个代码 -
DECLARE b INT DEFAULT 0;
DECLARE a VARCHAR(10);
DECLARE cur_1 CURSOR FOR SELECT t FROM sample_table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
IF NOT b THEN
-- do something:
...
...
...
END IF;
UNTIL b END REPEAT;
CLOSE cur_1;
回答by navneet kr verma
One of the reason could be that you might have used select into statements inside your cursor again, which sets your handler value to be 1 for those nested select statements inside the cursor loop. You can reset handler value to 0 after select statements used inside and move the handler condition just next to the FETCH command. Example:
原因之一可能是您可能再次在游标内使用了 select into 语句，这将游标循环内的那些嵌套 select 语句的处理程序值设置为 1。您可以在内部使用的 select 语句之后将处理程序值重置为 0，并将处理程序条件移动到 FETCH 命令旁边。例子：
...
BEGIN
DECLARE cursor_finished INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET cursor_finished = 1;
...
open table_cursor;
get_row: LOOP
FETCH table_cursor INTO v_variable1, v_variable3;
IF cursor_finished = 1 THEN
LEAVE get_row;
END IF;
....
Select id into v_id from sometable; -- this sets cursor_finished to 1.
SET cursor_finished = 0; -- hence reset this value for cursor.
....
END
