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.
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-resignal.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.
Referenties
Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.