Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.