Scope of Condition Handlers - MySQL

The scope of a handler determines which statements within the stored program are covered by the handler. In essence, the scope of a handler is the same as for a stored program variable: the handler applies to all statements in the block in which it is defined, including any statements in nested blocks. Furthermore, handlers in a stored program also cover statements that execute in any stored program that might be called by the first program, unless that program declares its own handler.

For instance, in Example 6-10 the handler will be invoked when theINSERTstatement executes (because it violates aNOT NULLconstraint). The handler fires because theINSERTstatement is contained within the same block as the handler—even though theINSERTstatement is in a nested block.

Example 6-10. Handler scope includes statements within BEGIN-END blocks

DECLARE CONTINUE HANDLER FOR 1048 SELECT 'Attempt to insert a null value';
BEGIN
    INSERT INTO departments (department_name,manager_id,location)
    VALUES (NULL,1,'Wouldn''t you like to know?');
END;

However, in Example 6-11 the handler will not be invoked—the scope of the handler is limited to the nested block, and theINSERTstatement occurs outside that block.

Condition Handlers

Example 6-11. Handlers within a nested block do not cover statements in enclosing blocks

BEGIN
    BEGIN
        DECLARE CONTINUE HANDLER FOR 1216 select
                  'Foreign key constraint violated';
    END;
    INSERT INTO departments (department_name,manager_id,location)
         VALUES ('Elbonian HR','Catbert','Catbertia');
END;

Handler scope extends to any stored procedures or functions that are invoked within the handler scope. This means that if one stored program calls another, a handler in the calling program can trap errors that occur in the program that has been called. So, for instance, in Example 6-12, the handler incalling_procedure()traps the null value exception that occurs insub_procedure().

Example 6-12. A handler can catch conditions raised in called procedures

CREATE PROCEDURE calling_procedure()
BEGIN
  DECLARE EXIT HANDLER FOR 1048 SELECT 'Attempt to insert a null value';
  CALL sub_procedure();
END;

Query OK, 0 rows affected (0.00 sec)

--------------
CREATE PROCEDURE sub_procedure()
BEGIN
  INSERT INTO departments (department_name,manager_id,location)
  VALUES (NULL,1,'Wouldn''t you like to know');
  SELECT 'Row inserted';

END;

Query OK, 0 rows affected (0.00 sec)

CALL calling_procedure();

+--------------------------------+
| Attempt to insert a null value |
+--------------------------------+
| Attempt to insert a null value |
+--------------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Of course, a handler in a procedure will override the scope of a hander that exists in a calling procedure. Only one handler can ever be activated in response to a specific error condition.