为MySQL中查询返回的每一行调用一个存储过程 [英] Call a stored procedure for each row returned by a query in MySQL

问题描述

我想要一个有效的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等)的概念是 procedural ，并且在像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

这篇关于为MySQL中查询返回的每一行调用一个存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！