Rechercher dans le manuel MySQL
13.6.7.4 RESIGNAL Syntax
- RESIGNAL [condition_value]
- [SET signal_information_item
- [, signal_information_item] ...]
- condition_value: {
- | condition_name
- }
- signal_information_item:
- condition_information_item_name = simple_value_specification
- condition_information_item_name: {
- CLASS_ORIGIN
- | SUBCLASS_ORIGIN
- | MESSAGE_TEXT
- | MYSQL_ERRNO
- | CONSTRAINT_CATALOG
- | CONSTRAINT_SCHEMA
- | CONSTRAINT_NAME
- | CATALOG_NAME
- | SCHEMA_NAME
- | TABLE_NAME
- | COLUMN_NAME
- | CURSOR_NAME
- }
- condition_name, 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
is related to
SIGNAL
, but instead of
originating a condition as SIGNAL
does, RESIGNAL
relays existing
condition information, possibly after modifying it.
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 can handle part of a
situation, then pass the condition “up the line” to
another handler.
No privileges are required to execute the
RESIGNAL
statement.
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.
To retrieve information from the diagnostics area, use the
GET DIAGNOSTICS
statement (see
Section 13.6.7.3, “GET DIAGNOSTICS Syntax”). For information about the
diagnostics area, see Section 13.6.7.7, “The MySQL Diagnostics Area”.
RESIGNAL Overview
For condition_value
and
signal_information_item
, the
definitions and rules are the same for
RESIGNAL
as for
SIGNAL
. For example, the
condition_value
can be an
SQLSTATE
value, and the value can indicate
errors, warnings, or “not found.” For additional
information, see Section 13.6.7.5, “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 diagnostics 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
, orMESSAGE_TEXT
.
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 first (current) diagnostics area, which starts as a copy of the last diagnostics area, but will be overwritten by the first statement in the handler that changes the current diagnostics area.
The last (stacked) 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. 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:
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”:
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:
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
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:
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:
- +-------+------+----------------------------------+
- | Level | Code | Message |
- +-------+------+----------------------------------+
- +-------+------+----------------------------------+
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:
- Query OK, 0 rows affected (0.00 sec)
Here is a more difficult example:
RESIGNAL
occurs within the
stored function f()
. Although
f()
itself is invoked within the context of
the EXIT
handler, execution within
f()
has its own context, which is not
handler context. Thus, RESIGNAL
within
f()
results in a “handler not
active” error.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-resignal.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.