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, 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.
```
BEGIN -- outer block
 DECLARE EXIT HANDLER FOR ...; -- handler H1
 DECLARE EXIT HANDLER FOR ...; -- handler H2
 stmt1;
 stmt2;
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:
```
BEGIN -- outer block
 BEGIN -- inner block
 DECLARE EXIT HANDLER FOR ...; -- handler H1
 stmt1;
 END;
 stmt2;
END;
```
A handler can be specific or general. A specific handler is for a MariaDB 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 MariaDB error code handler in an outer block, and a general SQLWARNING handler in an inner block. Or there might be handlers for a specific MariaDB 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 MariaDB 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.
The precedence of NOT FOUND depends on how the condition is raised:
- Normally, a condition in the NOT FOUND class can be handled by an SQLWARNING or NOT FOUND handler, with the SQLWARNING handler taking precedence if both are present. Normal occurrence of NOT FOUND takes place when a cursor used to fetch a set of rows reaches the end of the data set, or for instances of SELECT ... INTO var_list such that the WHERE clause finds no rows.
- If a NOT FOUND condition is raised by a SIGNAL (or RESIGNAL) statement, the condition can be handled by a NOT FOUND handler but not 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 indeterminate, 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 MariaDB 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:

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

mysql> CALL p1();
+--------------------------------+
| msg |
+--------------------------------+
| SQLSTATE handler was activated |
+--------------------------------+

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:

CREATE PROCEDURE p2()
BEGIN -- outer block
 DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
 SELECT 'SQLSTATE handler was activated' AS msg;
 BEGIN -- inner block
 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
 SELECT 'SQLEXCEPTION handler was activated' AS msg;
 DROP TABLE test.t; -- occurs within inner block
 END;
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:

mysql> CALL p2();
+------------------------------------+
| msg |
+------------------------------------+
| SQLEXCEPTION handler was activated |
+------------------------------------+

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

CREATE PROCEDURE p3()
BEGIN -- outer block
 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
 SELECT 'SQLEXCEPTION handler was activated' AS msg;
 BEGIN -- inner block
 DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
 SELECT 'SQLSTATE handler was activated' AS msg;
 END;
 DROP TABLE test.t; -- occurs within outer block END;

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

mysql> CALL p3();
+------------------------------------+
| msg |
+------------------------------------+
| SQLEXCEPTION handler was activated |
+------------------------------------+

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

CREATE PROCEDURE p4()
BEGIN -- outer block
 BEGIN -- inner block
 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
 SELECT 'SQLEXCEPTION handler was activated' AS msg;
 DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
 SELECT 'SQLSTATE handler was activated' AS msg;
 END;
 DROP TABLE test.t; -- occurs within outer block 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:

Scope Rules for Handlers

Retornar