Home arrow MySQL arrow Page 3 - Error Handling Examples

Scope of Condition Handlers - MySQL

In this second article in a three-part series, you will learn more about errors and exception handlers. It is excerpted from chapter six of the book MySQL Stored Procedure Programming, written by Guy Harrison and Steven Feuerstein (O'Reilly; ISBN: 0596100892). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

  1. Error Handling Examples
  2. Handler Precedence
  3. Scope of Condition Handlers
  4. Named Conditions
By: O'Reilly Media
Rating: starstarstarstarstar / 18
September 06, 2007

print this article



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';
INSERT INTO departments (department_name,manager_id,location)
VALUES (NULL,1,'Wouldn''t you like to know?');

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

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

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()
  DECLARE EXIT HANDLER FOR 1048 SELECT 'Attempt to insert a null value';
  CALL sub_procedure();

Query OK, 0 rows affected (0.00 sec)

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


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.

>>> More MySQL Articles          >>> More By O'Reilly Media

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: