Mysql 简明教程
MySQL - RESIGNAL Statement
在使用 MySQL 中的存储过程时，管理在执行过程中可能出现的异常非常重要。否则，这些异常可能会导致过程突然终止。
When working with stored procedures in MySQL, it is important to manage exceptions that may arise during their execution. These exceptions could otherwise lead to an abrupt termination of the procedure.
为了解决此问题，MySQL 提供了一种通过错误处理程序来处理异常的方法。可以使用 DECLARE…HANDLER 语句声明这些处理程序。
To address this issue, MySQL offers a way to handle exceptions through error handlers. These handlers can be declared using the DECLARE … HANDLER statement.
The MySQL RESIGNAL Statement
MySQL RESIGNAL 语句用于在存储过程中发生异常时向处理程序、应用程序或客户端提供错误信息。
The MySQL RESIGNAL statement is used to provide error information to handlers, applications, or clients when an exception occurs within a stored procedure.
Customizing Error Messages
RESIGNAL 语句允许您使用 SET MESSAGE_TEXT 命令来自定义错误消息，确保更流畅的程序执行。
The RESIGNAL statement allows you to customize error messages using the SET MESSAGE_TEXT command, ensuring smoother procedure execution.
Syntax
以下是 MySQL RESIGNAL 语句的语法：
Following is the syntax of the MySQL RESIGNAL Statement −
RESIGNAL condition_value [SET signal_information_item]其中，
Where,
-
condition_value represents the error value to be returned, which can be either a "sqlstate_value" or a "condition_name".
-
signal_information_item allows you to set additional information related to the error condition. You can specify various signal information items like CLASS_ORIGIN, SUBCLASS_ORIGIN, MESSAGE_TEXT, MYSQL_ERRNO, CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CATALOG_NAME, SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, or CURSOR_NAME.
Example
在此示例中，我们创建了一个接受缩写形式的学位并返回其全称的过程。如果我们提供无效学位（即除 BBA、BCA、MD 和 ITI 之外的值），则会使用 RESIGNAL 语句生成错误消息：
In this example, we create a procedure that accepts the short form of degrees and returns their full forms. If we provide an invalid degree i.e. value other than BBA, BCA, MD and ITI, an error message is generated using the RESIGNAL statement −
DELIMITER //
CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form VARCHAR(50))
BEGIN
DECLARE wrong_choice CONDITION FOR SQLSTATE '45000';
DECLARE EXIT HANDLER FOR wrong_choice
RESIGNAL SET MESSAGE_TEXT = 'Given degree is not valid', MYSQL_ERRNO = 1001;
IF degree='BBA' THEN SET full_form = 'Bachelor of Business Administration';
ELSEIF degree='BCA' THEN SET full_form = 'Bachelor of Computer Applications';
ELSEIF degree='MD' THEN SET full_form = 'Doctor of Medicine';
ELSEIF degree='ITI' THEN SET full_form = 'Industrial Training Institute';
ELSE
SIGNAL wrong_choice;
END IF;
END //
DELIMITER ;可以按如下所示调用上述过程来检索结果：
You can call the above procedure to retrieve the result as shown below −
CALL example('MD', @fullform);可以使用以下 SELECT 语句检索变量值：
You can retrieve the value of the variable using the following SELECT statement −
SELECT @fullform;以下是所获得的输出 −
Following is the output obtained −
如果您向过程传递无效值，它将生成如下错误消息：
If you pass an invalid value to the procedure, it will generate an error message as follows −
CALL example ('IIT', @fullform);获得的输出如下 −
The output obtained is as follows −
ERROR 1001 (45000): Given degree is not validHandling Warnings with RESIGNAL
我们来看另一个没有向 RESIGNAL 语句传递可选属性的示例：
Let us see another example where we do not pass optional attributes to the RESIGNAL statement −
DELIMITER //
CREATE PROCEDURE testexample (num INT)
BEGIN
DECLARE testCondition1 CONDITION FOR SQLSTATE '01000';
DECLARE EXIT HANDLER FOR testCondition1 RESIGNAL;
IF num < 0 THEN
SIGNAL testCondition1;
END IF;
END //
DELIMITER ;可以通过传递两个值来调用上述过程。但是，任何以“01”开头的 SQLSTATE 值都表示警告，因此查询将以警告执行，如下所示：
You can call the above procedure by passing two values. But, any SQLSTATE value that starts with '01' refers to a warning, so the query is executed with a warning as shown below −
CALL testexample(-15);获得的输出如下 −
The output obtained is as follows −
Query OK, 0 rows affected, 1 warning (0.00 sec)