对于condition_value和 signal_information_item，定义和规则与 for RESIGNAL相同 SIGNAL。例如， condition_value可以是一个 SQLSTATE值，该值可以指示错误、警告或“未找到”。”有关其他信息，请参阅第 13.6.7.5 节，“SIGNAL 语句”。

该RESIGNAL语句采用 condition_value和 SET子句，两者都是可选的。这导致了几种可能的用途：

These use cases all cause changes to the diagnostics and condition areas:

There is a stack of diagnostics areas. When a handler takes control, it pushes a diagnostics area to the top of the stack, so there are two diagnostics areas during handler execution:

The maximum number of condition areas in a diagnostics area is determined by the value of the max_error_count system variable. See Diagnostics Area-Related System Variables.

A simple RESIGNAL alone means “pass on the error with no change.” It restores the last diagnostics area and makes it the current diagnostics area. That is, it “pops” the diagnostics area stack.

Within a condition handler that catches a condition, one use for RESIGNAL alone is to perform some other actions, and then pass on without change the original condition information (the information that existed before entry into the handler).

Example:

DROP TABLE IF EXISTS xx;
delimiter //
CREATE PROCEDURE p ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET @error_count = @error_count + 1;
    IF @a = 0 THEN RESIGNAL; END IF;
  END;
  DROP TABLE xx;
END//
delimiter ;
SET @error_count = 0;
SET @a = 0;
CALL p();

Suppose that the DROP TABLE xx statement fails. The diagnostics area stack looks like this:

DA 1. ERROR 1051 (42S02): Unknown table 'xx'

Then execution enters the EXIT handler. It starts by pushing a diagnostics area to the top of the stack, which now looks like this:

DA 1. ERROR 1051 (42S02): Unknown table 'xx'
DA 2. ERROR 1051 (42S02): Unknown table 'xx'

At this point, the contents of the first (current) and second (stacked) diagnostics areas are the same. The first diagnostics area may be modified by statements executing subsequently within the handler.

Usually a procedure statement clears the first diagnostics area. BEGIN is an exception, it does not clear, it does nothing. SET is not an exception, it clears, performs the operation, and produces a result of “success.” The diagnostics area stack now looks like this:

DA 1. ERROR 0000 (00000): Successful operation
DA 2. ERROR 1051 (42S02): Unknown table 'xx'

At this point, if @a = 0, RESIGNAL pops the diagnostics area stack, which now looks like this:

DA 1. ERROR 1051 (42S02): Unknown table 'xx'

And that is what the caller sees.

If @a is not 0, the handler simply ends, which means that there is no more use for the current diagnostics area (it has been “handled”), so it can be thrown away, causing the stacked diagnostics area to become the current diagnostics area again. The diagnostics area stack looks like this:

DA 1. ERROR 0000 (00000): Successful operation

The details make it look complex, but the end result is quite useful: Handlers can execute without destroying information about the condition that caused activation of the handler.

RESIGNAL with a SET clause provides new signal information, so the statement means “pass on the error with changes”:

RESIGNAL SET signal_information_item [, signal_information_item] ...;

As with RESIGNAL alone, the idea is to pop the diagnostics area stack so that the original information goes out. Unlike RESIGNAL alone, anything specified in the SET clause changes.

Example:

DROP TABLE IF EXISTS xx;
delimiter //
CREATE PROCEDURE p ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET @error_count = @error_count + 1;
    IF @a = 0 THEN RESIGNAL SET MYSQL_ERRNO = 5; END IF;
  END;
  DROP TABLE xx;
END//
delimiter ;
SET @error_count = 0;
SET @a = 0;
CALL p();

Remember from the previous discussion that RESIGNAL alone results in a diagnostics area stack like this:

DA 1. ERROR 1051 (42S02): Unknown table 'xx'

The RESIGNAL SET MYSQL_ERRNO = 5 statement results in this stack instead, which is what the caller sees:

DA 1. ERROR 5 (42S02): Unknown table 'xx'

In other words, it changes the error number, and nothing else.

The RESIGNAL statement can change any or all of the signal information items, making the first condition area of the diagnostics area look quite different.

RESIGNAL with a condition value means “push a condition into the current diagnostics area.” If the SET clause is present, it also changes the error information.

RESIGNAL condition_value
    [SET signal_information_item [, signal_information_item] ...];

This form of RESIGNAL restores the last diagnostics area and makes it the current diagnostics area. That is, it “pops” the diagnostics area stack, which is the same as what a simple RESIGNAL alone would do. However, it also changes the diagnostics area depending on the condition value or signal information.

Example:

DROP TABLE IF EXISTS xx;
delimiter //
CREATE PROCEDURE p ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET @error_count = @error_count + 1;
    IF @a = 0 THEN RESIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=5; END IF;
  END;
  DROP TABLE xx;
END//
delimiter ;
SET @error_count = 0;
SET @a = 0;
SET @@max_error_count = 2;
CALL p();
SHOW ERRORS;

This is similar to the previous example, and the effects are the same, except that if RESIGNAL happens, the current condition area looks different at the end. (The reason the condition adds to rather than replaces the existing condition is the use of a condition value.)

The RESIGNAL statement includes a condition value (SQLSTATE '45000'), so it adds a new condition area, resulting in a diagnostics area stack that looks like this:

DA 1. (condition 2) ERROR 1051 (42S02): Unknown table 'xx'
      (condition 1) ERROR 5 (45000) Unknown table 'xx'

The result of CALL p() and SHOW ERRORS for this example is:

mysql> CALL p();
ERROR 5 (45000): Unknown table 'xx'
mysql> SHOW ERRORS;
+-------+------+----------------------------------+
| Level | Code | Message                          |
+-------+------+----------------------------------+
| Error | 1051 | Unknown table 'xx'               |
| Error |    5 | Unknown table 'xx'               |
+-------+------+----------------------------------+

All forms of RESIGNAL require that the current context be a condition handler. Otherwise, RESIGNAL is illegal and a RESIGNAL when handler not active error occurs. For example:

mysql> CREATE PROCEDURE p () RESIGNAL;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL p();
ERROR 1645 (0K000): RESIGNAL when handler not active

Here is a more difficult example:

delimiter //
CREATE FUNCTION f () RETURNS INT
BEGIN
  RESIGNAL;
  RETURN 5;
END//
CREATE PROCEDURE p ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @a=f();
  SIGNAL SQLSTATE '55555';
END//
delimiter ;
CALL p();

RESIGNAL发生在存储函数f()中。虽然 f()它本身是在处理程序的上下文中调用的，但是其中的EXIT执行 f()有它自己的上下文，而不是处理程序上下文。因此，RESIGNALwithin f()会导致“处理程序未激活”错误。