查询mysql中存储过程的调用记录,为MySQL中查询返回的每一行调用一个存储过程...

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?

解决方案

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