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 the INSERT statement executes (because it violates a NOT NULL constraint). The handler fires because the INSERT statement is contained within the same block as the handler—even though the INSERT statement 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 the INSERT statement 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 in calling_procedure() traps the null value exception that occurs in sub_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.