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

  • http://soqquadrissimo.wordpress.com Federico

    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)