33

I want a MySQL stored procedure which effectively does:

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?

Community
  • 1
  • 1
Mr. Boy
  • 60,845
  • 93
  • 320
  • 589

1 Answers1

48

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.

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  ...

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
eggyal
  • 122,705
  • 18
  • 212
  • 237
  • 2
    This pattern of manually setting a variable to terminate the 'loop', that's the normal way? It seems so clunky! – Mr. Boy Jan 15 '13 at 08:42
  • @John: Yes, it is. Although the pattern of calling a procedure for every record in a table is less normal (one might at least iterate over the cursor within `testProc`, but even that might be unnecessary: what does `testProc` do?). – eggyal Jan 15 '13 at 09:33
  • It's for a game, when the game finishes it has to update several tables related to each player... so in your example `foo()` is iterating over each player linked to a single game, and `testProc()` does a bunch of stuff for a single player. It's app-logic in the DB more than anything else. – Mr. Boy Jan 15 '13 at 10:17
  • 1
    remember if you have more selects out eg. a product price before and after you need to create more (_price, _newPrice) before you can FETCH INTO _price and _newPrice. Thanks a lot for this simple post. – ParisNakitaKejser Mar 05 '15 at 01:53
  • Syntax error: missing semicolumn after `DECLARE done BOOLEAN DEFAULT FALSE;` – Roel Jun 13 '16 at 12:36
  • It makes sense to explicitly `SET done := FALSE` immediately before `FETCH ...` (within the loop), so other queries won't affect the loop. – AntonK Oct 02 '18 at 11:52
  • @AntonK: What other queries? Local variables like `done` are scoped to their execution context, which is isolated to the session in which the sproc was invoked. Indeed, even if they were not, your proposal would merely narrow the race hazard—not eliminate it. – eggyal Oct 02 '18 at 11:55
  • @eggyal, if someone uses this code snippet as a template, and adds more code instead of `CALL testProc(_id)` (like other SQL queries), it could trigger the `CONTINUE HANDLER` which would `SET done = TRUE`, thus breaking the loop prematurely... – AntonK Oct 02 '18 at 17:41
  • @AntonK: The `CONTINUE HANDLER` will only be triggered on a `NOT FOUND` error, which itself will only occur on the `FETCH` statement. If someone substantially alters the code by adding additional `FETCH` statements, then those statements should be immediately followed by a test on whether `done` is true (in order to determine whether their `FETCH` was successful) and if so they can then reset `done` to false before control passes therefrom. There's no benefit to adding `SET done := FALSE` immediately before `FETCH ...`, and certainly not within the loop. – eggyal Oct 02 '18 at 17:46
  • @eggyal, according to MySQL documentation _The `NOT FOUND` condition also occurs for `SELECT ... INTO var_list` statements that retrieve no rows_ – AntonK Oct 02 '18 at 18:13