[+/-]
RESIGNAL [condition_value
] [SETsignal_information
[,signal_information
] ...];condition_value
: SQLSTATE [VALUE]sqlstate_value
|condition_name
signal_information
:condition_information_item
=simple_value_specification
condition_information_item
: { CLASS_ORIGIN | SUBCLASS_ORIGIN | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CATALOG_NAME | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | CURSOR_NAME | MESSAGE_TEXT | MYSQL_ERRNO }simple_value_specification
: (see following discussion)
RESIGNAL
passes on the error
condition information that is available during execution of a
condition handler within a compound statement inside a stored
procedure or function, trigger, or event.
RESIGNAL
may change some or all
information before passing it on.
RESIGNAL
makes it possible to
both handle an error and return the error information.
Otherwise, by executing an SQL statement within the handler,
information that caused the handler's activation is destroyed.
RESIGNAL
also can make some
procedures shorter if a given handler could handle part of a
situation, then pass the condition “up the line” to
another handler.
No special privileges are required to execute the
RESIGNAL
statement.
Unless otherwise indicated, the definitions and rules for
condition_value
and
signal_information
are the same for
the RESIGNAL
statement as for
SIGNAL
(see
Section 12.7.8.1, “SIGNAL
Syntax”).
The RESIGNAL
statement takes
condition_value
and
SET
clauses, both of which are optional. This
leads to several possible uses:
These use cases all cause changes to the diagnostic and condition areas:
A diagnostics area contains one or more condition areas.
A condition area contains condition information items, such
as the SQLSTATE
value,
MYSQL_ERRNO
, or
MESSAGE_TEXT
.
There is a stack of diagnostics areas. When a handler takes control, it pushes the top of the stack, so there are two diagnostics areas during handler execution:
The current diagnostics area, which starts as a copy of the last diagnostics area, but will be overwritten by the first procedure statement in the handler.
The last diagnostics area, which has the condition areas that were set up before the handler took control.
The maximum number of condition areas in a diagnostics area is
determined by the value of the
max_error_count
system
variable.
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();
The DROP TABLE xx
statement fails. The
diagnostics area stack looks like this:
1. ERROR 1051 (42S02): Unknown table 'xx'
Then execution enters the EXIT
handler. It
starts by pushing the top of the diagnostics area stack, which
now looks like this:
1. ERROR 1051 (42S02): Unknown table 'xx' 2. ERROR 1051 (42S02): Unknown table 'xx'
Usually a procedure statement clears the first diagnostics
area (also called the “current” diagnostics
area). BEGIN
is an exception, it does not
clear, it does nothing. SET
is not an
exception, it clears, performs the operation, and then
produces a result of “success.” The diagnostics
area stack now looks like this:
1. ERROR 0000 (00000): Successful operation 2. ERROR 1051 (42S02): Unknown table 'xx'
At this point, if @a = 0
,
RESIGNAL
pops the diagnostics
area stack, which now looks like this:
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 last diagnostics
area (it has been “handled”), so it can be thrown
away. The diagnostics area stack looks like this:
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 SETsignal_information
[,signal_information
] ...;
As with RESIGNAL
alone, the
idea is to pop the diagnostics area stack so that the original
information will go 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:
1. ERROR 1051 (42S02): Unknown table 'xx'
The RESIGNAL SET MYSQL_ERRNO = 5
statement
results in this stack instead:
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 diagnostic
stack area.” If the SET
clause is
present, it also changes the error information.
RESIGNALcondition_value
[SETsignal_information
[,signal_information
] ...];
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 is added rather than replaced is the use of a
condition value.)
The RESIGNAL
statement includes
a condition value (SQLSTATE '45000'
), so it
“pushes” a new condition area, resulting in a
diagnostics area stack that looks like this:
1. (condition 1) ERROR 5 (45000) Unknown table 'xx' (condition 2) ERROR 1051 (42S02): 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 | 5 | Unknown table 'xx' | | Error | 1051 | Unknown table 'xx' | +-------+------+----------------------------------+
All forms of RESIGNAL
require
that a handler be active when it executes. If no handler is
active, 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 1739 (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();
At the time the RESIGNAL
executes, there is a handler, even though the
RESIGNAL
is not defined inside
the handler.
A statement such as the one following may appear bizarre
because RESIGNAL
apparently is
not in a handler:
CREATE TRIGGER t_bi BEFORE INSERT ON t FOR EACH ROW RESIGNAL;
But it does not matter.
RESIGNAL
does not have to be
technically “in” (that is, contained in), a
handler declaration. The requirement is that a handler must be
active.
User Comments
Add your own comment.