为 MySQL 中查询返回的每一行调用一个存储过程

2021-12-20 00:00:00 mysql stored-procedures

我想要一个有效的 MySQL 存储过程:

I want a MySQL stored procedure which effectively does:

foreach id in (SELECT id FROM objects WHERE ... ) CALL testProc(id)

我想我只是想要 MySQL 回答这个问题，但我不太了解游标:如何为查询返回的每一行执行一次存储过程?

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?

推荐答案

诸如循环"(for-each、while 等)和分支"(if-else、call 等)等概念是过程性的 并且不存在于像 SQL 这样的声明性语言中.通常可以用声明的方式表达自己想要的结果，这才是解决这个问题的正确方法.

Concepts such as "loops" (for-each, while, etc) and "branching" (if-else, call, etc) are procedural and do not exist in declarative languages like SQL. Usually one can express one’s desired result in a declarative way, which would be the correct way to solve this problem.

例如，如果要调用的 testProc 过程使用给定的 id 作为另一个表的查找键，那么您可以(并且应该)简单地JOIN 将你的表放在一起——例如:

For example, if the testProc procedure that is to be called uses the given id as a lookup key into another table, then you could (and should) instead simply JOIN your tables together—for example:

SELECT ...
FROM   objects JOIN other USING (id)
WHERE  ...

只有在您的问题无法以声明方式表达的极少数情况下，您才应该求助于程序性解决问题.存储过程是在 MySQL 中执行过程代码的唯一方法.因此，您要么需要修改现有的 sproc，使其在循环中执行当前的逻辑，要么创建一个新的 sproc，从循环中调用现有的 sproc:

Only in the extremely rare situations where your problem cannot be expressed declaratively should you then resort to solving it procedurally instead. Stored procedures are 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:

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

相关文章