Scope of procedures

Stored procedures can include handlers that are called when certain conditions occur within the procedure. The applicability of each handler depends on its position within the procedure definition and the one or more conditions it handles.

Scope and rules for handlers

The following rules apply to the scope of handlers in OceanBase Database:

• Handlers declared in a BEGIN ... END block are applicable only to SQL statements after the handler declaration. If a handler itself raises a condition, it cannot handle that condition and no other handlers can be declared in the block. For example, in the following code, handlers h1 and h2 are applicable to conditions raised by statements st1 and st2, respectively, but not to conditions raised within the bodies of h1 or h2.

  BEGIN -- outer block
    DECLARE EXIT HANDLER FOR ...;  -- handler h1
    DECLARE EXIT HANDLER FOR ...;  -- handler h2
    st1;
    st2;
  END;
  

• A handler is only applicable within the block in which it is declared and cannot be activated by conditions outside of that block. For example, in the following code, handler h1 is applicable only within the scope of st1 in the inner block, but not within the scope of st2 in the outer block.

  BEGIN -- outer block
    BEGIN -- inner block
      DECLARE EXIT HANDLER FOR ...;  -- handler h1
      st1;
    END;
    st2;
  END;
  

• A handler can be specific or generic. Specific handlers are used for error codes, SQLSTATE values, or condition names. Generic handlers are used for conditions in the SQLWARNING, SQLEXCEPTION, or NOT FOUND categories. Condition specificity is related to condition priority.

  Multiple handlers with different characteristics can be declared in different scopes. For example, an outer block might have a specific error code handler, while an inner block might have a generic SQLWARNING handler. Alternatively, a single block might have both specific error code and generic SQLWARNING handlers.

The activation of a handler depends not only on its scope and condition value but also on other handlers. When a condition occurs in a stored program, the server searches for applicable handlers in the current scope (the current BEGIN ... END block). If no applicable handler is found, it continues searching in the next outer scope (block). When the server finds one or more applicable handlers in the specified scope, it selects based on condition priority. The condition priorities are as follows:

• Specific error code handlers have higher priority than SQLSTATE value handlers.

• SQLSTATE value handlers have higher priority than generic SQLWARNING, SQLEXCEPTION, or NOT FOUND handlers.

• SQLEXCEPTION handlers have higher priority than SQLWARNING handlers.

• Multiple applicable handlers with the same priority can exist. For example, a statement can generate multiple warnings with different error codes, each with a specific handler. In such cases, the server's choice of which handler to activate is indeterminate and may vary based on the situation.

If multiple applicable handlers exist in different scopes, the handler in the local scope has higher priority than those in outer scopes, even if the outer handlers have higher priority than the specified condition.

If no applicable handler exists when a condition occurs, the following actions are taken based on the condition's category:

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

• For SQLWARNING conditions, the program continues execution, similar to using a CONTINUE handler.

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

Examples

Examples 1, 2, 3, and 4 show how to apply the handler selection rules.

The following example 1 shows a procedure that contains two handlers. One handler is for the specified SQLSTATE value ('42S02') that occurs when an attempt is made to drop a non-existent table. The other handler belongs to the general SQLEXCEPTION category.

-- 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 the case of example 1, both handlers are declared in the same block and have the same scope. However, the SQLSTATE handler takes precedence over the SQLEXCEPTION handler. Therefore, if the table tbl1 does not exist, the DROP TABLE statement will trigger the condition and activate the SQLSTATE handler. As shown below:

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

In example 2, the procedure contains the same two handlers as in example 1. However, the DROP TABLE statement and the SQLEXCEPTION handler are located in an inner block relative to the SQLSTATE handler.

-- Example 2
CREATE PROCEDURE proc2()
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.tbl1; -- Occurs within the inner block
  END;
END;

In the case of example 2, the handler that is closer to the location where the condition occurs takes precedence. Therefore, the SQLEXCEPTION handler is activated, even though it is more general than the SQLSTATE handler. As shown below:

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

In example 3, one handler is declared in a block within the scope of the DROP TABLE statement:

-- Example 3
CREATE PROCEDURE proc3()
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.tbl1; -- Occurs within the outer block
END;

In the case of example 3, the SQLEXCEPTION handler is activated because the SQLSTATE handler is not within the scope of the condition triggered by the DROP TABLE statement. As shown below:

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 -- 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.tbl1; -- Occurs within the outer block
END;

In the case of example 4, neither handler is applicable because they are not within the scope of the DROP TABLE statement. The condition triggered by the statement is not handled and results in an error that terminates the procedure. As shown below:

obclient> CALL proc4();

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