Rechercher dans le manuel MySQL

13.6.7.6 Scope Rules for Handlers

Inhoudsopgave

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, handlers H1 and H2 are in scope for conditions raised by statements stmt1 and stmt2. But neither H1 nor H2 are in scope for conditions raised in the body of H1 or H2.

    1. BEGIN -- outer block
    2.   DECLARE EXIT HANDLER FOR ...;  -- handler H1
    3.   DECLARE EXIT HANDLER FOR ...;  -- handler H2
    4.   stmt1;
    5.   stmt2;
    6. END;

  • 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 for stmt1 in the inner block, but not for stmt2 in the outer block:

    1. BEGIN -- outer block
    2.   BEGIN -- inner block
    3.     DECLARE EXIT HANDLER FOR ...;  -- handler H1
    4.     stmt1;
    5.   END;
    6.   stmt2;
    7. END;

  • 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 the SQLWARNING, SQLEXCEPTION, or NOT 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 general SQLWARNING, SQLEXCEPTION, or NOT FOUND handlers.

  • An SQLEXCEPTION handler takes precedence over an SQLWARNING 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 an EXIT 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 a CONTINUE handler.

  • For NOT FOUND conditions, if the condition was raised normally, the action is CONTINUE. If it was raised by SIGNAL or RESIGNAL, the action is EXIT.

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:

  1. CREATE PROCEDURE p1()
  2. BEGIN
  3.   DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
  4.     SELECT 'SQLSTATE handler was activated' AS msg;
  5.   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  6.     SELECT 'SQLEXCEPTION handler was activated' AS msg;
  7.  
  8.   DROP TABLE test.t;
  9. 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:

  1. mysql> CALL p1();
  2. +--------------------------------+
  3. | msg                            |
  4. +--------------------------------+
  5. | SQLSTATE handler was activated |
  6. +--------------------------------+

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:

  1. CREATE PROCEDURE p2()
  2. BEGIN -- outer block
  3.     DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
  4.       SELECT 'SQLSTATE handler was activated' AS msg;
  5.   BEGIN -- inner block
  6.     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  7.       SELECT 'SQLEXCEPTION handler was activated' AS msg;
  8.  
  9.     DROP TABLE test.t; -- occurs within inner block
  10.   END;
  11. END;

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:

  1. mysql> CALL p2();
  2. +------------------------------------+
  3. | msg                                |
  4. +------------------------------------+
  5. | SQLEXCEPTION handler was activated |
  6. +------------------------------------+

In this procedure, one of the handlers is declared in a block inner to the scope of the DROP TABLE statement:

  1. CREATE PROCEDURE p3()
  2. BEGIN -- outer block
  3.   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  4.     SELECT 'SQLEXCEPTION handler was activated' AS msg;
  5.   BEGIN -- inner block
  6.     DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
  7.       SELECT 'SQLSTATE handler was activated' AS msg;
  8.   END;
  9.  
  10.   DROP TABLE test.t; -- occurs within outer block
  11. END;

Only the SQLEXCEPTION handler applies because the other one is not in scope for the condition raised by the DROP TABLE:

  1. mysql> CALL p3();
  2. +------------------------------------+
  3. | msg                                |
  4. +------------------------------------+
  5. | SQLEXCEPTION handler was activated |
  6. +------------------------------------+

In this procedure, both handlers are declared in a block inner to the scope of the DROP TABLE statement:

  1. CREATE PROCEDURE p4()
  2. BEGIN -- outer block
  3.   BEGIN -- inner block
  4.     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  5.       SELECT 'SQLEXCEPTION handler was activated' AS msg;
  6.     DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
  7.       SELECT 'SQLSTATE handler was activated' AS msg;
  8.   END;
  9.  
  10.   DROP TABLE test.t; -- occurs within outer block
  11. END;

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:

  1. mysql> CALL p4();
  2. ERROR 1051 (42S02): Unknown table 'test.t'

Zoek in de MySQL-handleiding

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-handler-scope.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

  1. Bekijk - html-document Taal van het document:en Manuel MySQL : https://dev.mysql.com/

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.

Inhoudsopgave Haut