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

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回答这个问题，但我不理解游标：如何为查询返回的每一行执行一次存储过程？

1 个解决方案

#1

You need a stored procedure!

你需要一个存储过程！

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