Mysql – Does MySQL close cursor if stored procedure encounters an exception

cursorsexceptionMySQLstored-procedures

I've seen two different approaches of cursor handling is MySQL stored procedures. Now I'm curious about their stability, due to potential MYSQLEXCEPTION risks in stored routines.

Assuming, that CURSOR is opened when DBMS raises MYSQLEXCEPTION and goes to EXIT HANDLER if it is declared…

Approach #1:

DELIMITER $$

CREATE PROCEDURE `test1`()
BEGIN
DECLARE `EOS` BOOLEAN DEFAULT FALSE;

DECLARE `buffer` INT UNSIGNED;

DECLARE `MyCursor` CURSOR FOR
SELECT
    `id`
FROM
    `MyTable`
LIMIT 50;

DECLARE EXIT HANDLER FOR MYSQLEXCEPTION
    BEGIN
    ROLLBACK;

    SIGNAL SQLSTATE 'ERR0R' SET MESSAGE_TEXT = 'Oops... FATAL ERROR!', MYSQL_ERRNO = 5656;
    END;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET `EOS` = TRUE;

START TRANSACTION;

OPEN `MyCursor`;

cycle: LOOP

    FETCH `MyCursor` INTO `buffer`;

    IF `EOS`
    THEN LEAVE cycle;
    END IF;

    -- INSERTs to another tables, UPDATEs of another tables and DELETEs from to another tables
    -- Section that might throw a MYSQLEXCEPTION

END LOOP cycle;

CLOSE `MyCursor`;

COMMIT;

END$$

DELIMITER ;

Note that if MYSQLEXCEPTION will be thrown in commented section, then MyCursor probably will not be closed. Or it will be closed and deallocated in this case?

Somewhere I've seen following…

Approach #2:

DELIMITER $$

CREATE PROCEDURE `test2`()
BEGIN
DECLARE `EOS` BOOLEAN DEFAULT FALSE;

DECLARE `buffer` INT UNSIGNED;

DECLARE `MyCursor` CURSOR FOR
SELECT
    `id`
FROM
    `MyTable`
LIMIT 50;

DECLARE EXIT HANDLER FOR MYSQLEXCEPTION
    BEGIN
    ROLLBACK;

    CLOSE `MyCursor`; --   <---- is this even needed here ?

    SIGNAL SQLSTATE 'ERR0R' SET MESSAGE_TEXT = 'Oops... FATAL ERROR!', MYSQL_ERRNO = 5858;
    END;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET `EOS` = TRUE;

START TRANSACTION;

OPEN `MyCursor`;

cycle: LOOP

    FETCH `MyCursor` INTO `buffer`;

    IF `EOS`
    THEN LEAVE cycle;
    END IF;

    -- INSERTs to another tables, UPDATEs of another tables and DELETEs from to another tables
    -- Section that might throw a MYSQLEXCEPTION

END LOOP cycle;

CLOSE `MyCursor`;

COMMIT;

END$$

DELIMITER ;

And now I'm a bit confused about CURSOR handling.

Question: Which of this approaches is more accurate and reliable? If I'm not closing CURSOR in EXIT HANDLER will it impact anything? And if it is, how "bad" might be consequences?

Best Answer

use Approach #1: according to http://www.brainbell.com/tutorials/MySQL/Working_With_Cursors.htm

If you do not explicitly close a cursor, MySQL will close it automatically when the END statement is reached.

Related Question