MySQL cursors in stored procedures

I’ve wanted to write a few stored procedures in MySQL for a while now, but I found it very fiddly and was unable to come up with one very quickly. Tonight I was determined to read up on MySQL documentation and get one going.

For my reference only, this stored procedure will loop through a recordset and update a single row in a table upon a single row lookup in another table.

DELIMITER // 

DROP PROCEDURE IF EXISTS sp_test //

CREATE PROCEDURE sp_test ()
BEGIN
	DECLARE done INT DEFAULT FALSE;
	DECLARE myid INT;
	DECLARE cur1 CURSOR FOR SELECT id from table1 WHERE firstname IS NULL;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

	OPEN cur1;

	read_loop: LOOP
		IF done THEN
			LEAVE read_loop;
		END IF;

		FETCH cur1 INTO myid;
		UPDATE table1 SET firstname = (SELECT firstname from table2 WHERE id = myid)
		WHERE id = myid;
	END LOOP;

	close cur1;
END;
//

Once I got the procedure to compile, executing it was as simple as:

call sp_test

Marko