Stored programs can include handlers that are called when certain conditions occur within the program. The applicability of each handler depends on its position in the program definition and the condition or conditions it processes.
Scope of application and rules
The following rules describe the effective scope of OceanBase AI Database handlers:
A handler declared in a
BEGIN ... ENDblock applies only to SQL statements that follow the handler declaration within the block. If a handler itself triggers a condition, it cannot handle that condition, nor can other handlers be declared within the block. In the following example, handlersh1andh2are within the effective range of the conditions triggered by statementsst1andst2, but not within the effective range of the conditions triggered within their own bodies.BEGIN -- Outer block DECLARE EXIT HANDLER FOR ...; -- Handler h1 DECLARE EXIT HANDLER FOR ...; -- Handler h2 st1; st2; END;A handler is only valid within the scope of the block in which it is declared and cannot be activated by conditions that occur outside that block. In the following example, the handler
h1is valid within the scope ofst1in the inner block, but invalid forst2in the outer block:BEGIN -- Outer block BEGIN -- Inner block DECLARE EXIT HANDLER FOR ...; -- Handler h1 st1; END; st2; END;handlers can be specific or generic. Specific handlers are used for error codes,
SQLSTATEvalues, or condition names. Generic handlers are used by conditions in theSQLWARNING,SQLEXCEPTION, orNOT FOUNDcategory. Condition specificity is related to condition precedence.You can declare multiple handlers with different characteristics in different scopes. For example, an external block may have a handler for specific error codes, while an internal block may have a general
SQLWARNINGhandler. Alternatively, the same block may have handlers for specific error codes and the generalSQLWARNINGcategory.
Whether a handler is activated depends not only on its own scope and condition value but also on other handlers. When a condition appears in a stored program, the server searches for an applicable handler within the current scope (the current BEGIN ... END block). If no applicable handler is found, the search continues to the next scope (block). When the server finds one or more applicable handlers within the specified scope, it selects one based on the condition priority. The condition priorities are as follows:
Error code handlers take precedence over
SQLSTATEvalue handlers.The
SQLSTATEvalue handler takes precedence over the generalSQLWARNING,SQLEXCEPTION, orNOT FOUNDhandlers.The
SQLEXCEPTIONhandler takes precedence over theSQLWARNINGhandler.There can be multiple applicable handlers with the same priority. For example, a statement may generate multiple warnings with different error codes, each warning having a handler specific to that error. In this case, which handler the server activates is uncertain and may vary depending on the circumstances that occur.
If multiple applicable handlers exist in different scopes, a handler in a local scope takes precedence over one in an outer scope, and even over a handler with specified conditions.
If no appropriate handler is available when a condition occurs, the following actions are taken based on the condition type:
For an
SQLEXCEPTIONcondition, the stored program terminates at the statement that raised the condition, similar to using anEXIThandler. If this program is called by another stored program, the calling program will handle the condition according to its own selection rules.For an
SQLWARNINGcondition, the program continues execution, similar to using aCONTINUEhandler.For the
NOT FOUNDcondition, if it is raised normally, the action isCONTINUE. If it is raised by aSIGNALorRESIGNAL, the action isEXIT.
Examples
Examples 1, 2, 3, and 4 demonstrate how to apply the handler selection rules.
The process in Example 1 below contains two handlers: one for the specified SQLSTATE value ('42S02') that occurs when attempting to drop a non-existent table, and another for 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 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 below, the procedure contains the same two handlers as in Example 1. However, compared to the SQLSTATE handler, the DROP TABLE statement and SQLEXCEPTION handler are placed inside a nested block.
-- 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 an internal block
END;
END;
In the case of Example 2, if a handler is located closer to where the condition occurs, it will be activated first. Therefore, the SQLEXCEPTION handler is activated, even though it is more general than the SQLSTATE handler. This is illustrated below:
obclient> CALL proc2();
+------------------------------------+
| msg |
+------------------------------------+
| SQLEXCEPTION handler was activated |
+------------------------------------+
1 row in set
In Example 3 below, a handler in the procedure is declared within a block delimited by 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 an external block
END;
In the case of Example 3, the SQLEXCEPTION handler is activated because the SQLSTATE is not within the range of conditions raised by DROP TABLE. As shown below:
obclient> CALL proc3();
+------------------------------------+
| msg |
+------------------------------------+
| SQLEXCEPTION handler was activated |
+------------------------------------+
1 row in set
In Example 4 below, both handlers are declared within the block enclosed by 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 an external block
END;
In the case of Example 4, neither of these handlers applies because they are not within the scope of the DROP TABLE statement. The condition triggered by this statement is not handled and terminates the process with an error. As shown below:
obclient> CALL proc4();
ERROR 1051 (42S02): Unknown table 'test.tbl1'