存储过程中的MySQL错误处理

时间:2019-11-20 08:52:19  来源:igfitidea点击:

简介:在本教程中,您将学习如何使用MySQL处理程序来处理存储过程中遇到的错误。

当存储过程内部发生错误时,适当地处理它很重要,例如继续或退出当前代码块的执行,以及发出有意义的错误消息。

MySQL提供了一种简单的方法来定义处理程序,这些处理程序可以处理从警告或异常等一般条件到特定条件(例如特定错误代码)的处理。

声明处理程序

要声明处理程序,请使用DECLARE HANDLER语句,如下所示:

DECLARE action HANDLER FOR condition_value statement;

如果条件的值与condition_value相匹配,则MySQL将执行该语句并根据该操作继续或退出当前代码块。

该操作接受以下值之一:

  • CONTINUE:继续执行封闭代码块(BEGIN…END)。

  • EXIT:终止声明处理程序的封闭代码块的执行。

condition_value指定用于激活处理程序的特定条件或一类条件。
condition_value接受以下值之一:

  • MySQL错误代码。

  • 标准SQLSTATE值。
    也可以是SQLWARNING,NOTFOUND或SQLEXCEPTION条件,这是SQLSTATE值类的简写。
    NOTFOUND条件用于游标或SELECT INTO variable_list语句。

  • 与MySQL错误代码或SQLSTATE值关联的命名条件。

该语句可以是简单语句,也可以是用BEGIN和END关键字括起来的复合语句。

MySQL错误处理示例

让我们举一些声明处理程序的示例。

以下处理程序将hasError变量的值设置为1并在发生SQLEXCEPTION时继续执行

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
SET hasError = 1;

以下处理程序将回滚之前的操作,发出错误消息,并在发生错误的情况下退出当前代码块。
如果在存储过程的BEGIN END块中声明它,它将立即终止该存储过程。

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated';
END;

以下处理程序将RowNotFound变量的值设置为1并在没有游标或SELECT INTO语句的情况下如果没有更多行要继续执行,则继续执行:

DECLARE CONTINUE HANDLER FOR NOT FOUND 
SET RowNotFound = 1;

如果发生重复的键错误,则以下处理程序将发出错误消息并继续执行。

DECLARE CONTINUE HANDLER FOR 1062
SELECT 'Error, duplicate key occurred';

存储过程中的MySQL处理程序示例

首先,为演示创建一个名为SupplierProducts的新表:

CREATE TABLE SupplierProducts (
    supplierId INT,
    productId INT,
    PRIMARY KEY (supplierId , productId)
);

表格SupplierProducts存储表格供应商和产品之间的关系。
每个供应商可以提供许多产品,并且每个产品可以由许多供应商提供。
为简单起见,我们不创建Products和Suppliers表,也不创建SupplierProducts表中的外键。

其次,创建一个存储过程,将产品ID和供应商ID插入SupplierProducts表中:

CREATE PROCEDURE InsertSupplierProduct(
    IN inSupplierId INT, 
    IN inProductId INT
)
BEGIN
    -- exit if the duplicate key occurs
    DECLARE EXIT HANDLER FOR 1062
    BEGIN
 	SELECT CONCAT('Duplicate key (',inSupplierId,',',inProductId,') occurred') AS message;
    END;
    
    -- insert a new row into the SupplierProducts
    INSERT INTO SupplierProducts(supplierId,productId)
    VALUES(inSupplierId,inProductId);
    
    -- return the products supplied by the supplier id
    SELECT COUNT(*) 
    FROM SupplierProducts
    WHERE supplierId = inSupplierId;
    
END$$

DELIMITER ;

这个怎么运作。

每当出现重复的密钥(代码为1062)时,以下退出处理程序都会终止存储过程。
此外,它还返回一条错误消息。

DECLARE EXIT HANDLER FOR 1062
BEGIN
    SELECT CONCAT('Duplicate key (',supplierId,',',productId,') occurred') AS message;
END;

该语句在SupplierProducts表中插入一行。
如果出现重复的密钥,则将执行处理程序部分中的代码。

INSERT INTO SupplierProducts(supplierId,productId) 
VALUES(supplierId,productId);

第三,调用InsertSupplierProduct()将一些行插入SupplierProducts表中:

CALL InsertSupplierProduct(1,1);
CALL InsertSupplierProduct(1,2);
CALL InsertSupplierProduct(1,3);

第四,尝试插入其值已经在SupplierProducts表中存在的行:

CALL InsertSupplierProduct(1,3);

这是错误消息:

+------------------------------+
| message                      |
+------------------------------+
| Duplicate key (1,3) occurred |
+------------------------------+
1 row in set (0.01 sec)

因为该处理程序是EXIT处理程序,所以最后一条语句不会执行:

SELECT COUNT(*) 
FROM SupplierProducts
WHERE supplierId = inSupplierId;

如果将处理程序声明中的EXIT更改为CONTINUE,还将获得供应商提供的产品数量:

DROP PROCEDURE IF EXISTS InsertSupplierProduct;

DELIMITER $$

CREATE PROCEDURE InsertSupplierProduct(
    IN inSupplierId INT, 
    IN inProductId INT
)
BEGIN
    -- exit if the duplicate key occurs
    DECLARE CONTINUE HANDLER FOR 1062
    BEGIN
	SELECT CONCAT('Duplicate key (',inSupplierId,',',inProductId,') occurred') AS message;
    END;
    
    -- insert a new row into the SupplierProducts
    INSERT INTO SupplierProducts(supplierId,productId)
    VALUES(inSupplierId,inProductId);
    
    -- return the products supplied by the supplier id
    SELECT COUNT(*) 
    FROM SupplierProducts
    WHERE supplierId = inSupplierId;
    
END$$

DELIMITER ;

最后,再次调用存储过程以查看CONTINUE处理程序的效果:

CALL InsertSupplierProduct(1,3);

这是输出:

+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.02 sec)

MySQL处理程序优先级

如果您有多个处理同一错误的处理程序,MySQL将根据以下规则首先调用最特定的处理程序以处理该错误:

  • 错误总是映射到MySQL错误代码,因为在MySQL中,错误代码是最具体的。

  • SQLSTATE可能映射到许多MySQL错误代码,因此它的含义不太明确。

  • SQLEXCPETION或SQLWARNING是一类SQLSTATES值的简写,因此它是最通用的。

根据处理程序优先级规则,MySQL错误代码处理程序,SQLSTATE处理程序和SQLEXCEPTION具有第一,第二和第三优先级。

假设我们在存储过程insert_article_tags_3的处理程序中有三个处理程序:

DROP PROCEDURE IF EXISTS InsertSupplierProduct;

DELIMITER $$

CREATE PROCEDURE InsertSupplierProduct(
    IN inSupplierId INT, 
    IN inProductId INT
)
BEGIN
    -- exit if the duplicate key occurs
    DECLARE EXIT HANDLER FOR 1062 SELECT 'Duplicate keys error encountered' Message; 
    DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLException encountered' Message; 
    DECLARE EXIT HANDLER FOR SQLSTATE '23000' SELECT 'SQLSTATE 23000' ErrorCode;
    
    -- insert a new row into the SupplierProducts
    INSERT INTO SupplierProducts(supplierId,productId)
    VALUES(inSupplierId,inProductId);
    
    -- return the products supplied by the supplier id
    SELECT COUNT(*) 
    FROM SupplierProducts
    WHERE supplierId = inSupplierId;
    
END$$

DELIMITER ;

调用存储过程以插入重复键:

CALL InsertSupplierProduct(1,3);

这是输出:

+----------------------------------+
| Message                          |
+----------------------------------+
| Duplicate keys error encountered |
+----------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

如您所见,调用了MySQL错误代码处理程序。

使用命名错误条件

让我们从错误处理程序声明开始。

DELIMITER $$

CREATE PROCEDURE TestProc()
BEGIN

    DECLARE EXIT HANDLER FOR 1146 
    SELECT 'Please create table abc first' Message; 
        
    SELECT * FROM abc;
END$$

DELIMITER ;

数字1146真正意味着什么?想象一下,您的存储过程到处都被这些数字污染了;很难理解和维护代码。

幸运的是,MySQL为您提供了DECLARE CONDITION语句,该语句声明了一个命名错误条件,该条件与条件相关联。

这是DECLARE CONDITION语句的语法:

DECLARE condition_name CONDITION FOR condition_value;

condition_value可以是MySQL错误代码,例如1146或SQLSTATE值。
condition_value由condition_name表示。

声明后,您可以引用condition_name而不是condition_value。

因此,您可以按以下方式重写上面的代码:

DROP PROCEDURE IF EXISTS TestProc;

DELIMITER $$

CREATE PROCEDURE TestProc()
BEGIN
    DECLARE TableNotFound CONDITION for 1146 ; 

    DECLARE EXIT HANDLER FOR TableNotFound 
	SELECT 'Please create table abc first' Message; 
    SELECT * FROM abc;
END$$

DELIMITER ;

如您所见,该代码比前一个代码更加明显和可读。
注意,条件声明必须出现在处理程序或游标声明之前。

在本教程中,您学习了如何使用MySQL处理程序来处理存储过程中发生的异常或错误。