MySQL 在存储过程的循环中使用游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3224248/
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
Using Cursor in a Loop of a stored procedure
提问by Novemberland
So as to use cursors dynamically using MySQL is it possible to declare a cursor in a loop of a stored procedure? I've tried and got an error:
为了使用 MySQL 动态使用游标，是否可以在存储过程的循环中声明游标？我试过，但出现错误：
increment: LOOP
DECLARE cur1 CURSOR FOR SELECT person_id, publication_id FROM p_publication WHERE person_id = new_count;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO pub_id, per_id;
IF NOT done THEN
INSERT INTO test.t2 VALUES (pub_id, per_id);
END IF;
SET new_count = new_count + 1;
UNTIL done END REPEAT;
CLOSE cur1;
IF !(new_count < old_count ) THEN LEAVE increment;
END IF;
END LOOP increment;
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE cur1 CURSOR FOR SELECT person_id, publication_id FROM person_has_public' at line 12
您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 12 行的“DECLARE cur1 CURSOR FOR SELECT person_id,publication_id FROM person_has_public”附近使用的正确语法
Thanks In advance
提前致谢
回答by Mike
You can do what you want by nesting the cursor in a BEGIN ... ENDblock. See Roland Bouman's "Nesting MySQL Cursor Loops"article for more details. It may be worth noting his comments about this technique often being unnecessary, as it is often possible to rewrite the query instead of having to perform a nested cursor.
您可以通过将光标嵌套在BEGIN ... END块中来执行所需的操作。有关更多详细信息，请参阅Roland Bouman 的“Nesting MySQL Cursor Loops”文章。值得注意的是，他对这种技术的评论通常是不必要的，因为通常可以重写查询，而不必执行嵌套游标。
Should you still need to nest your cursor in a loop, your code should look something like this:
如果您仍然需要将光标嵌套在循环中，您的代码应如下所示：
increment: LOOP
block_cursor: BEGIN
DECLARE cur1 CURSOR FOR SELECT person_id, publication_id FROM p_publication WHERE person_id = new_count;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO pub_id, per_id;
IF NOT done THEN
INSERT INTO test.t2 VALUES (pub_id, per_id);
END IF;
SET new_count = new_count + 1;
UNTIL done END REPEAT;
CLOSE cur1;
IF !(new_count < old_count ) THEN
LEAVE increment;
END IF;
END block_cursor;
END LOOP increment;
