为 MySQL 中查询返回的每一行调用一个存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14326775/
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
Call a stored procedure for each row returned by a query in MySQL
提问by Mr. Boy
I want a MySQL stored procedure which effectively does:
我想要一个有效的 MySQL 存储过程：
foreach id in (SELECT id FROM objects WHERE ... ) CALL testProc(id)
foreach id in (SELECT id FROM objects WHERE ... ) CALL testProc(id)
I think I simply want the MySQL answer to this question but I don't understand cursors well: How do I execute a stored procedure once for each row returned by query?
我想我只是想要 MySQL 对这个问题的回答，但我不太了解游标：如何为查询返回的每一行执行一次存储过程？
回答by eggyal
Concepts such as “loops” (for-each, while, etc) and “branching” (if-else, call, etc) are proceduraland do not exist in declarativelanguages like SQL. Usually one can express one's desired result in a declarative way, which would be the correct way to solve this problem.
“循环”（for-each、while 等）和“分支”（if-else、call 等）等概念是过程性的，在SQL 等声明性语言中不存在。通常人们可以用声明的方式表达自己想要的结果，这将是解决这个问题的正确方法。
For example, if the testProcprocedure that is to be called uses the given idas a lookup key into another table, then you could (and should) instead simply JOINyour tables together—for example:
例如，如果testProc要调用的过程使用给定的id作为查找另一个表的键，那么您可以（并且应该）JOIN将您的表简单地放在一起 - 例如：
SELECT ...
FROM objects JOIN other USING (id)
WHERE ...
Only in the extremely rare situations where your problem cannot be expressed declaratively should you then resort to solving it procedurally instead. Stored proceduresare the only way to execute procedural code in MySQL. So you either need to modify your existing sproc so that it performs its current logic within a loop, or else create a new sproc that calls your existing one from within a loop:
只有在您的问题无法以声明方式表达的极少数情况下，您才应该求助于程序性解决问题。存储过程是在 MySQL 中执行过程代码的唯一方法。因此，您要么需要修改现有的 sproc，使其在循环中执行其当前逻辑，要么创建一个新的 sproc，从循环中调用现有的 sproc：
CREATE PROCEDURE foo() BEGIN
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE _id BIGINT UNSIGNED;
DECLARE cur CURSOR FOR SELECT id FROM objects WHERE ...;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;
OPEN cur;
testLoop: LOOP
FETCH cur INTO _id;
IF done THEN
LEAVE testLoop;
END IF;
CALL testProc(_id);
END LOOP testLoop;
CLOSE cur;
END
