- You can declare condition handlers for exception conditions and completion conditions other than successful completion. No condition handler can be defined for successful completion (SQLSTATE = ‘00000’).
- You can declare condition handlers only within a compound statement.
No handlers can be declared in stored procedures that do not contain a compound statement.
- You cannot repeat the same SQLSTATE code in a DECLARE HANDLER statement.
- You cannot declare the same SQLSTATE code for multiple condition handlers in the same compound statement.
However, the same SQLSTATE code can be reused for condition handlers in other nested or non-nested compound statements within a stored procedure.
- You can specify SQLEXCEPTION, SQLWARNING, NOT FOUND, or any combination of these generic conditions in a handler declaration.
- You can declare each generic condition handler at most once in a compound statement.
The same generic condition can be reused in other compound statements within a stored procedure.
- You cannot declare a specific SQLSTATE value and one or more generic conditions within the same DECLARE HANDLER statement.
- When you specify multiple statements for handler action, all the statements must be contained within a BEGIN END compound statement.
You can submit nested compound statements for handler action.
- The scope of a condition handler is the compound statement in which it is declared, including all nested compound statements.
The following additional rules apply when declaring a handler which specifies a condition name.
- You can specify more than one condition name in a handler declaration if the condition names are not identical. The handler action is associated with every condition name in the DECLARE HANDLER statement.
- You cannot repeat the same condition name within a handler declaration. Otherwise, error SPL1052 is reported during stored procedure compilation, and the stored procedure is not created.
- You cannot specify a condition name and a generic condition in the same handler declaration. Otherwise, error SPL1082 is reported during stored procedure compilation, and the stored procedure is not created.
- You cannot specify a condition name and the SQLSTATE value associated with the condition name in the same handler declaration. Otherwise, error SPL1054 is reported during stored procedure compilation, and the stored procedure is not created.
- You cannot declare multiple handler declarations which specify the same condition name within the same compound statement. Otherwise, error SPL1052 is reported during stored procedure compilation, and the stored procedure is not created.
- If you declare a handler for a condition name, you cannot declare another handler in the same compound statement to handle the SQLSTATE value associated with that condition name. Otherwise, error SPL1054 is reported during stored procedure compilation, and the stored procedure is not created.
- If you declare a handler for a condition name that has an SQLSTATE value associated with it, the same handler is also used for handling conditions with that SQLSTATE value.