主页 / user-28533

BlitZ's questions

Martin Hope
BlitZ
Asked: 2013-09-26 17:29:12 +0800 CST
  • 3

我见过两种不同的游标处理方法是 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影响什么吗？如果是这样，后果可能有多“糟糕” ？

mysql stored-procedures