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

  • Hi!
    I’d say that your procedure contains an error. The “not found” error is triggered by the FETCH statement. So the “IF done…” block should be after FETCH, not before.
    Bye

    • nordy

      Are you sure? Actually, I used to do it like you said, then I kept getting ‘Error 1329: No data – zero rows fetched, selected’ Then I did like his example, and it worked well. Wonder who is correct now. You or he…

      • Federico Razzoli

        Hi, I dont know your specific case, so I modified the code in this page to show the names of all databases. As you can see, this procedure works:

        DROP PROCEDURE IF EXISTS `test`.`sp_test`;
        DELIMITER ||
        CREATE PROCEDURE `test`.`sp_test`()
        BEGIN
        DECLARE `done` BOOLEAN DEFAULT FALSE;
        DECLARE `txt`, `out` TEXT DEFAULT ”;
        DECLARE `cur1` CURSOR FOR
        SELECT `SCHEMA_NAME` FROM `information_schema`.`SCHEMATA`;
        DECLARE CONTINUE HANDLER
        FOR 1329
        BEGIN
        SET `done` = TRUE;
        END;

        OPEN `cur1`;
        `read_loop`: LOOP
        FETCH `cur1` INTO `txt`;

        IF `done` IS TRUE THEN
        LEAVE `read_loop`;
        END IF;

        SET `out` = CONCAT(`txt`, ‘n’, `out`);
        END LOOP;
        CLOSE `cur1`;

        SELECT `out`;
        END;
        ||
        DELIMITER ;

        Output:

        MariaDB [(none)]> CALL `test`.`sp_test`();
        +——————————————————————————-
        ——————————————————————————–
        ——————————————————————————–
        —————–+
        | `out`
        |
        +——————————————————————————-
        ——————————————————————————–
        ——————————————————————————–
        —————–+
        | wordpress
        test_x
        (…)
        performance_schema
        mysql
        meta_schema
        information_schema
        _
        |
        +——————————————————————————-
        ——————————————————————————–
        ——————————————————————————–
        —————–+
        1 row in set (0.00 sec)
        Query OK, 0 rows affected (4.92 sec)
        MariaDB [(none)]>

        (note that I catch the specific error and not the generic NOT FOUND class, because your code may contain other statements which produce a NOT FOUND error)