如果儲存過程遇到異常，MySQL 會關閉游標嗎？

我見過兩種不同的游標處理方法是 MySQL 儲存過程。MYSQLEXCEPTION由於儲存常式中的潛在風險，現在我對它們的穩定性很好奇。

假設，CURSOR當 DBMS 引發時打開它，如果它被聲明則MYSQLEXCEPTION轉到…EXIT HANDLER

方法#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 ;

請注意，如果MYSQLEXCEPTION將在評論部分拋出，則MyCursor可能不會關閉。或者在這種情況下它會被關閉並釋放？

我在某個地方看到過…

方法#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 ;

現在我對CURSOR處理有點困惑。

***問題：哪種方法更準確可靠？如果我不關閉CURSOR會EXIT HANDLER影響什麼嗎？如果是這樣，*後果可能有多“糟糕” ？

使用方法 #1：根據http://www.brainbell.com/tutorials/MySQL/Working_With_Cursors.htm

如果您沒有顯式關閉游標，MySQL 將在到達 END 語句時自動關閉它。

引用自：https://dba.stackexchange.com/questions/50536