存储过程中的 MySQL 错误处理

摘要：在本教程中，您将学习如何使用 MySQL 处理程序来处理存储过程中遇到的错误。

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

MySQL 提供了一种简单的方法来定义处理程序，这些处理程序可以处理从一般情况（例如警告或异常）到特定情况（例如特定错误代码）的处理。

声明一个处理程序

要声明处理程序，请使用以下 DECLARE HANDLER 语句：

DECLARE action HANDLER FOR condition_value statement;

如果值与匹配的条件 condition_value ，MySQL 将执行 statement 并基于继续或退出当前代码块 action 。

所述 action 接受下列值之一：

CONTINUE : 封闭代码块 ( BEGIN … END ) 的执行继续。
EXIT ：声明处理程序的封闭代码块的执行终止。

在 condition_value 指定特定条件或类的激活处理程序的条件。所述 condition_value 接受下列值之一：

MySQL 错误代码。
一个标准 SQLSTATE 值。或者它可以是一个 SQLWARNING ， NOTFOUND 或 SQLEXCEPTION 条件，这是 SQLSTATE 值类的简写。该 NOTFOUND 条件是用于光标或 SELECT INTO variable_list 声明。
与 MySQL 错误代码或 SQLSTATE 值关联的命名条件。

The statement 可以是一个简单的语句，也可以是一个由 BEGIN and 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 handler的效果：

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 中它是最具体的。
An SQLSTATE 可能映射到许多 MySQL 错误代码，因此，它不太具体。
an SQLEXCPETION or an 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 处理程序来处理存储过程中发生的异常或错误。

声明一个处理程序

MySQL 错误处理实例

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

MySQL 处理程序优先级

使用命名错误条件

本页目录