Mysql 简明教程

MySQL - SIGNAL Statement

在使用 MySQL 存储过程时，管理异常对于防止过程意外终止和提供有意义的错误信息非常重要。这通过使用 MySQL SIGNAL 语句和 DECLARE …​ HANDLER 语句实现。

When working with MySQL stored procedures, managing exceptions is important to prevent abrupt termination of procedures and to provide meaningful error information. This is achieved using the MySQL SIGNAL statement and the DECLARE …​ HANDLER statement.

The MySQL SIGNAL Statement

MySQL SIGNAL 语句用于传递存储过程中的错误信息。它确保适当处理异常，防止突然终止过程。

The MySQL SIGNAL statement is used to convey error information in stored procedures. It ensures that exceptions are properly handled, preventing sudden procedure termination.

Exception Handling with DECLARE …​ HANDLER

你可以使用 DECLARE …​ HANDLER 语句有效管理 MySQL 中的异常。它允许你指定如何在存储过程中处理不同类型的异常。通过将此语句与 SIGNAL 结合使用，你可以对错误处理进行精确控制。

You can use the DECLARE …​ HANDLER statement to effectively manage exceptions in MySQL. It allows you to specify how different types of exceptions should be handled within a stored procedure. By using this statement in conjunction with SIGNAL, you can enable precise control over error handling.

Customizing Error Messages

SIGNAL 语句允许使用 SET MESSAGE_TEXT 命令定制错误消息。这对于修改错误消息以向处理程序、应用程序或客户端提供更有意义的反馈很有帮助。

The SIGNAL statement allows for the customization of error messages using the SET MESSAGE_TEXT command. This is helpful for modifying error messages to provide more meaningful feedback to handlers, applications, or clients.

Syntax

以下是 MySQL SIGNAL 语句的语法 −

Following is the syntax of the MySQL SIGNAL Statement −

SIGNAL condition_value [SET signal_information_item]

其中，

Where,

  1. condition_value represents the error value to be returned, which can be either a "sqlstate_value" or a "condition_name".

  2. 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

在此示例中，我们创建一个接受度数简写并返回其全写的过程。如果我们提供无效的度数，即 B-Tech、M-Tech、BSC 和 MSC 以外的值，则会使用 SIGNAL 语句生成错误信息 −

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 B-Tech, M-Tech, BSC and MSC, an error message is generated using the SIGNAL statement −

DELIMITER //
CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form Varchar(50))
BEGIN
IF degree='B-Tech' THEN SET full_form = 'Bachelor of Technology';
ELSEIF degree='M-Tech' THEN SET full_form = 'Master of Technology';
ELSEIF degree='BSC' THEN SET full_form = 'Bachelor of Science';
ELSEIF degree='MSC' THEN SET full_form = 'Master of Science';
ELSE
SIGNAL SQLSTATE '01000'
SET MESSAGE_TEXT = 'Choose from the existing values', MYSQL_ERRNO = 12121;
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Given degree is not valid', MYSQL_ERRNO = 1001;
END IF;
END //
DELIMITER ;

可以按如下所示调用上述过程来检索结果：

You can call the above procedure to retrieve the result as shown below −

CALL example('BSC', @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 shown below −

CALL example ('BBC', @fullform);

获得的输出如下 −

The output obtained is as follows −

ERROR 1001 (45000): Given degree is not valid

Example

以下是演示使用 SIGNAL 语句进行异常处理的另一个示例。在此，我们声明一个条件并使用 SIGNAL 根据某些条件触发异常 −

Following is another example demonstrating exception handling with the SIGNAL statement. Here, we declare a condition and use SIGNAL to trigger exceptions based on certain conditions −

DELIMITER //
CREATE PROCEDURE example (num INT)
BEGIN
DECLARE testCondition CONDITION FOR SQLSTATE '45000';
IF num < 0 THEN
SIGNAL SQLSTATE '01000';
ELSEIF num > 150 THEN
SIGNAL SQLSTATE '45000';
END IF;
END //
DELIMITER ;

您可以通过传递两个值调用上述过程，如下所示 −

You can call the above procedure by passing two values as shown below −

CALL example(15);

以下是所获得的输出 −

Following is the output obtained −

Query OK, 0 rows affected (0.00 sec)

通过传递第二个值调用过程 −

Calling the procedure by passing the second value −

CALL example(160);

生成的结果如下所示 −

The result produced is as shown below −

ERROR 1644 (45000): Unhandled user-defined exception condition

Example

您可以使用 SET MESSAGE_TEXT 和 SIGNAL 语句自定义错误消息，如下例所示 −

You can customize error messages using SET MESSAGE_TEXT with the SIGNAL statement as shown in this modified example −

DELIMITER //
CREATE PROCEDURE example (num INT)
BEGIN
DECLARE testCondition CONDITION FOR SQLSTATE '45000';
IF num < 0 THEN
SIGNAL SQLSTATE '01000';
ELSEIF num > 150 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Number higher than the limit set';
END IF;
END //
DELIMITER ;

我们获得以下输出 −

We get the following output −

Query OK, 0 rows affected (0.01 sec)

您可以通过传递两个值调用上述过程，如下所示 −

You can call the above procedure by passing two values as shown below −

CALL example(20);

以下是所获得的输出 −

Following is the output obtained −

Query OK, 0 rows affected (0.00 sec)

通过传递第二个值调用过程 −

Calling the procedure by passing the second value −

CALL example(160);

您可以在以下输出中观察到，显示的错误消息已根据用户进行自定义 −

You can observe in the output below, the error message displayed is customized according to the user −

ERROR 1644 (45000): Number higher than the limit set