Rechercher dans le manuel MySQL
13.6.7.6 Scope Rules for Handlers
A stored program may include handlers to be invoked when certain conditions occur within the program. The applicability of each handler depends on its location within the program definition and on the condition or conditions that it handles:
A handler declared in a
BEGIN ... END
block is in scope only for the SQL statements following the handler declarations in the block. If the handler itself raises a condition, it cannot handle that condition, nor can any other handlers declared in the block. In the following example, handlersH1
andH2
are in scope for conditions raised by statementsstmt1
andstmt2
. But neitherH1
norH2
are in scope for conditions raised in the body ofH1
orH2
.A handler is in scope only for the block in which it is declared, and cannot be activated for conditions occurring outside that block. In the following example, handler
H1
is in scope forstmt1
in the inner block, but not forstmt2
in the outer block:A handler can be specific or general. A specific handler is for a MySQL error code,
SQLSTATE
value, or condition name. A general handler is for a condition in theSQLWARNING
,SQLEXCEPTION
, orNOT FOUND
class. Condition specificity is related to condition precedence, as described later.
Multiple handlers can be declared in different scopes and with
different specificities. For example, there might be a specific
MySQL error code handler in an outer block, and a general
SQLWARNING
handler in an inner block. Or
there might be handlers for a specific MySQL error code and the
general SQLWARNING
class in the same block.
Whether a handler is activated depends not only on its own scope
and condition value, but on what other handlers are present.
When a condition occurs in a stored program, the server searches
for applicable handlers in the current scope (current
BEGIN ...
END
block). If there are no applicable handlers, the
search continues outward with the handlers in each successive
containing scope (block). When the server finds one or more
applicable handlers at a given scope, it chooses among them
based on condition precedence:
A MySQL error code handler takes precedence over an
SQLSTATE
value handler.An
SQLSTATE
value handler takes precedence over generalSQLWARNING
,SQLEXCEPTION
, orNOT FOUND
handlers.An
SQLEXCEPTION
handler takes precedence over anSQLWARNING
handler.It is possible to have several applicable handlers with the same precedence. For example, a statement could generate multiple warnings with different error codes, for each of which an error-specific handler exists. In this case, the choice of which handler the server activates is nondeterministic, and may change depending on the circumstances under which the condition occurs.
One implication of the handler selection rules is that if multiple applicable handlers occur in different scopes, handlers with the most local scope take precedence over handlers in outer scopes, even over those for more specific conditions.
If there is no appropriate handler when a condition occurs, the action taken depends on the class of the condition:
For
SQLEXCEPTION
conditions, the stored program terminates at the statement that raised the condition, as if there were anEXIT
handler. If the program was called by another stored program, the calling program handles the condition using the handler selection rules applied to its own handlers.For
SQLWARNING
conditions, the program continues executing, as if there were aCONTINUE
handler.For
NOT FOUND
conditions, if the condition was raised normally, the action isCONTINUE
. If it was raised bySIGNAL
orRESIGNAL
, the action isEXIT
.
The following examples demonstrate how MySQL applies the handler selection rules.
This procedure contains two handlers, one for the specific
SQLSTATE
value ('42S02'
)
that occurs for attempts to drop a nonexistent table, and one
for the general SQLEXCEPTION
class:
- END;
Both handlers are declared in the same block and have the same
scope. However, SQLSTATE
handlers take
precedence over SQLEXCEPTION
handlers, so if
the table t
is nonexistent, the
DROP TABLE
statement raises a
condition that activates the SQLSTATE
handler:
- +--------------------------------+
- | msg |
- +--------------------------------+
- +--------------------------------+
This procedure contains the same two handlers. But this time,
the DROP TABLE
statement and
SQLEXCEPTION
handler are in an inner block
relative to the SQLSTATE
handler:
In this case, the handler that is more local to where the
condition occurs takes precedence. The
SQLEXCEPTION
handler activates, even though
it is more general than the SQLSTATE
handler:
- +------------------------------------+
- | msg |
- +------------------------------------+
- +------------------------------------+
In this procedure, one of the handlers is declared in a block
inner to the scope of the DROP
TABLE
statement:
Only the SQLEXCEPTION
handler applies because
the other one is not in scope for the condition raised by the
DROP TABLE
:
- +------------------------------------+
- | msg |
- +------------------------------------+
- +------------------------------------+
In this procedure, both handlers are declared in a block inner
to the scope of the DROP TABLE
statement:
Neither handler applies because they are not in scope for the
DROP TABLE
. The condition raised
by the statement goes unhandled and terminates the procedure
with an 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-handler-scope.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.