Home » MySQL Stored Procedures » MySQL Error Handling in Stored Procedures

MySQL Error Handling in Stored Procedures

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

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

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

声明一个处理程序

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

DECLARE action HANDLER FOR condition_value statement;Code language: SQL (Structured Query Language) (sql)

如果条件的值与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值关联的命名条件。

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

MySQL 错误处理示例

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

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

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
SET hasError = 1;Code language: SQL (Structured Query Language) (sql)

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

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated';
END;Code language: SQL (Structured Query Language) (sql)

以下处理程序将RowNotFound变量的值设置为 1，如果在游标或SELECT INTO语句的情况下没有更多行可供提取，则继续执行：

DECLARE CONTINUE HANDLER FOR NOT FOUND 
SET RowNotFound = 1;Code language: SQL (Structured Query Language) (sql)

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

DECLARE CONTINUE HANDLER FOR 1062
SELECT 'Error, duplicate key occurred';Code language: SQL (Structured Query Language) (sql)

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

首先，创建一个名为SupplierProducts的新表用于演示：

CREATE TABLE SupplierProducts (
    supplierId INT,
    productId INT,
    PRIMARY KEY (supplierId , productId)
);Code language: SQL (Structured Query Language) (sql)

表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 ;Code language: SQL (Structured Query Language) (sql)

怎么运行的。

只要出现重复键（代码为 1062），以下退出处理程序就会终止存储过程。此外，它还会返回一条错误消息。

DECLARE EXIT HANDLER FOR 1062
BEGIN
    SELECT CONCAT('Duplicate key (',supplierId,',',productId,') occurred') AS message;
END;Code language: SQL (Structured Query Language) (sql)

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

INSERT INTO SupplierProducts(supplierId,productId) 
VALUES(supplierId,productId);Code language: SQL (Structured Query Language) (sql)

第三，调用InsertSupplierProduct()将一些行插入到SupplierProducts表中：

CALL InsertSupplierProduct(1,1);
CALL InsertSupplierProduct(1,2);
CALL InsertSupplierProduct(1,3);Code language: SQL (Structured Query Language) (sql)

第四，尝试插入其值已存在于SupplierProducts表中的行：

CALL InsertSupplierProduct(1,3);Code language: SQL (Structured Query Language) (sql)

这是错误消息：

+------------------------------+
| message                      |
+------------------------------+
| Duplicate key (1,3) occurred |
+------------------------------+
1 row in set (0.01 sec)Code language: JavaScript (javascript)

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

SELECT COUNT(*) 
FROM SupplierProducts
WHERE supplierId = inSupplierId;Code language: SQL (Structured Query Language) (sql)

如果将处理程序声明中的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 ;Code language: SQL (Structured Query Language) (sql)

最后，再次调用存储过程来查看CONTINUE处理程序的效果：

CALL InsertSupplierProduct(1,3);Code language: SQL (Structured Query Language) (sql)

这是输出：

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

Query OK, 0 rows affected (0.02 sec)Code language: SQL (Structured Query Language) (sql)

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 ;Code language: SQL (Structured Query Language) (sql)

调用存储过程插入重复键：

CALL InsertSupplierProduct(1,3);Code language: SQL (Structured Query Language) (sql)

这是输出：

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

Query OK, 0 rows affected (0.01 sec)Code language: JavaScript (javascript)

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

使用命名的错误条件

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

DELIMITER $$

CREATE PROCEDURE TestProc()
BEGIN

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

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

数字1146到底意味着什么？想象一下，您的存储过程到处都被这些数字污染了；代码将很难理解和维护。

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

以下是DECLARE CONDITION语句的语法：

DECLARE condition_name CONDITION FOR condition_value;Code language: SQL (Structured Query Language) (sql)

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 ;Code language: SQL (Structured Query Language) (sql)

正如您所看到的，该代码比之前的代码更加明显和可读。请注意，条件声明必须出现在处理程序或游标声明之前。

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

本教程有帮助吗？
MySQL Cursor
Previously
MySQL Cursor
Up Next
Raising Error Conditions with MySQL SIGNAL / RESIGNAL Statements
Raising Error Conditions with MySQL SIGNAL / RESIGNAL Statements