Effective scope of an exception handler

A stored program can include a handler to be called when certain conditions occur. The applicability of each handler depends on its position in the program definition and the one or more conditions that it handles.

Effective scope of an exception handler and related rules

The effective scope of a handler in OceanBase Database follows the subsequent rules:

• A handler declared in a BEGIN ... END block applies only to SQL statements after the handler declaration in the block. A handler cannot process a condition that is raised by itself, nor can it declare other handlers in the block. In the following example, handlers h1 and h2 are within the effective scope of the conditions raised by statements st1 and st2, but are not within the effective scope of the condition raised in the body of h1 or h2.

  BEGIN -- External block
    DECLARE EXIT HANDLER FOR ...;  -- Handler h1
    DECLARE EXIT HANDLER FOR ...;  -- Handler h2
    st1;
    st2;
  END;
  

• A handler takes effect only within the scope of the block that declares it, and cannot be activated by conditions that occur outside the block. In the following example, handler h1 is valid within the scope of the st1 statement in the internal block, but is invalid for the st2 statement in the external block.

  BEGIN -- External block
    BEGIN -- Internal block
      DECLARE EXIT HANDLER FOR ...;  -- Handler h1
      st1;
    END;
    st2;
  END;
  

• A handler can either be specific or generic. Specific handlers can handle error codes, SQLSTATE values, and condition names. Generic handlers can handle conditions of the SQLWARNING, SQLEXCEPTION, or NOT FOUND class. Condition specificity is associated with condition precedence.

  You can declare multiple handlers with different characteristics in different scopes. For example, the external block may have a specific error code handler, while the internal block may have a generic SQLWARNING handler. Alternatively, one block may have both handlers for specific error codes and handlers for the generic SQLWARNING class.

The activation of a handler depends not only on its own scope and condition values, but also on other handlers. When a condition in the stored program occurs, the server searches for applicable handlers in the current scope, which is the current BEGIN ... END block. If no applicable handler is available, the server continues to search for the handler in the next outer scope (block). When the server finds one or more applicable handlers within the specified scope, it selects a handler based on the condition precedence. The condition precedence is as follows:

• Error code handlers take precedence over SQLSTATE value handlers.

• SQLSTATE value handlers take precedence over SQLWARNING, SQLEXCEPTION, and NOT FOUND handlers.

• SQLEXCEPTION handlers take precedence over SQLWARNING handlers.

• A stored program can have multiple applicable handlers with the same priority. For example, a statement can generate multiple warnings with different error codes, each with an error-specific handler. In this case, the handler that the server selects to activate is uncertain and may change depending on the situation.

If multiple applicable handlers are in different scopes, the handler in the local scope takes precedence over handlers in external scopes, even over those with a priority higher than the specified condition.

If no suitable handler is available when a condition occurs, the server takes the following actions according to the class of the condition:

• For an SQLEXCEPTION condition, the stored program terminates at the statement that raised the condition, which is similar to an EXIT handler. If the program is called by another stored program, the calling program follows its own rules to handle the condition.

• For an SQLWARNING condition, the program continues the procedure, which is similar to a CONTINUE handler.

• For a NOT FOUND condition, if the condition is raised normally, the program takes the CONTINUE action. If the condition is raised by a SIGNAL or RESIGNAL statement, the program takes the EXIT action.

Examples

Examples 1 to 4 show how to apply handler selection rules.

The procedure in Example 1 contains two handlers. One is for specifying the SQLSTATE value (‘42S02’) when attempting to delete a non-existent table, and the other is for the generic SQLEXCEPTION class.

-- Example 1
CREATE PROCEDURE proc1()
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.tbl1;
END;

In Example 1, both handlers declare in the same block and have the same scope. However, the SQLSTATE handler takes precedence over the SQLEXCEPTION handler. Therefore, if the tbl1 table does not exist, the DROP TABLE statement raises a condition and thereby activates the SQLSTATE handler. A sample statement is as follows:

obclient> CALL proc1();
+--------------------------------+
| msg                            |
+--------------------------------+
| SQLSTATE handler was activated |
+--------------------------------+
1 row in set

In Example 2, the procedure contains two handlers the same as Example 1. In comparison with the SQLSTATE handler, the DROP TABLE statement and the SQLEXCEPTION handler are located in the internal block.

-- Example 2
CREATE PROCEDURE proc2()
BEGIN -- External block
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
      SELECT 'SQLSTATE handler was activated' AS msg;
  BEGIN -- Internal block
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
      SELECT 'SQLEXCEPTION handler was activated' AS msg;

    DROP TABLE test.tbl1; -- Occurs within the internal block
  END;
END;

In Example 2, the handler closer to the condition occurrence location is activated with priority. Therefore, the SQLEXCEPTION handler is activated, even if it is more generic than the SQLSTATE handler. A sample statement is as follows:

obclient> CALL proc2();
+------------------------------------+
| msg                                |
+------------------------------------+
| SQLEXCEPTION handler was activated |
+------------------------------------+
1 row in set

In Example 3, a handler of the procedure is declared in a block within the scope of the DROP TABLE statement.

-- Example 3
CREATE PROCEDURE proc3()
BEGIN -- External block
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    SELECT 'SQLEXCEPTION handler was activated' AS msg;
  BEGIN -- Internal block
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
      SELECT 'SQLSTATE handler was activated' AS msg;
  END;

  DROP TABLE test.tbl1; -- Occurs within the external block
END;

In Example 3, the SQLEXCEPTION handler is activated because the SQLSTATE handler is beyond the scope of the condition raised by the DROP TABLE statement. A sample statement is as follows:

obclient> CALL proc3();
+------------------------------------+
| msg                                |
+------------------------------------+
| SQLEXCEPTION handler was activated |
+------------------------------------+
1 row in set

In Example 4, both handlers are declared in a block within the scope of the DROP TABLE statement.

-- Example 4
CREATE PROCEDURE proc4()
BEGIN -- External block
  BEGIN -- Internal 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.tbl1; -- Occurs within the external block
END;

In Example 4, neither handler is applicable, because they are not within the scope of the DROP TABLE statement. The condition raised by the statement is not handled and the procedure is terminated with an error. A sample statement is as follows:

obclient> CALL proc4();

ERROR 1051 (42S02): Unknown table 'test.tbl1'