Exception Handling Examples
This simple example declares an EXIT handler for a zero_divide condition:
CREATE PROCEDURE pname(...) =
DECLARE var1 INTEGER;
DECLARE var2 VARCHAR(10);
DECLARE zero_divide CONDITION FOR SQLSTATE '22012';
BEGIN
DECLARE EXIT HANDLER FOR zero_divide
BEGIN -- this block is the <handler action>
MESSAGE 'Division by zero has occurred';
CALLPROC some_general_error_procedure(what='divzero');
END;
var1 = 0;
CALLPROC something();
var2 = CAST( 10/var1 AS VARCHAR(10) ); -- zero divide
END
When the condition occurs, the output is:
Division by zero has occurred
A BEGIN-END block is added to demonstrate where the exit handler control goes:
CREATE PROCEDURE pname(...) =
DECLARE var1 INTEGER;
DECLARE var2 VARCHAR(10);
DECLARE zero_divide CONDITION FOR SQLSTATE '22012';
BEGIN
MESSAGE 'this block has no handlers';
BEGIN
DECLARE EXIT HANDLER FOR zero_divide
BEGIN -- This block is the <handler action>
MESSAGE 'Division by zero has occurred';
END;
var1 = 0;
CALLPROC something();
var2 = CAST( 10/var1 AS VARCHAR(10) ); -- zero divide
MESSAGE 'Not reached';
END;
MESSAGE 'Back to the outer block.';
var2 = CAST(10/var1 AS VARCHAR(10)); -- zero divide
END
The EXIT handler exits the block it is declared in, so when the exception handler is fired, the inner block is exited.
When the condition occurs, the output is:
this block has no handlers
Division by zero has occurred
Back to the outer block.
E_US1069 Integer divide by zero detected in query.
(The last error is issued because when control passes to the outer BEGIN-END block, the handler is no longer within scope.)
If we change the EXIT handler to a CONTINUE handler:
CREATE PROCEDURE pname(...) =
DECLARE var1 INTEGER;
DECLARE var2 VARCHAR(10);
DECLARE zero_divide CONDITION FOR SQLSTATE '22012'; BEGIN
MESSAGE 'This block has no handlers';
BEGIN
DECLARE CONTINUE HANDLER FOR zero_divide
BEGIN -- this block is the <handler action>
MESSAGE 'Division by zero has occurred';
END;
var1 = 0;
CALLPROC something();
var2 = CAST( 10/var1 AS VARCHAR(10) ); -- zero divide
MESSAGE 'Not reached';
END;
MESSAGE 'Back to the outer block.';
var2 = CAST(10/var1 AS VARCHAR(10)); -- zero divide END
When the condition occurs, the output is:
This block has exit handlers
Division by zero has occurred
We do not see “Back to the outer block”, nor the second zero divide, because the exception condition ran the EXIT handler and it exited its declared block, which was the outer procedure block.
Handlers can be generic. In this example, the handler will fire for any error or warning condition:
CREATE PROCEDURE pname(...) =
DECLARE var1 INTEGER;
DECLARE var2 VARCHAR(10);
DECLARE msg VARCHAR(40);
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN -- This block is the <handler action>
msg = 'Something happened: ' + varchar(iih_errornumber) + ', sqlstate ' + iih_sqlstate;
MESSAGE :msg;
END;
var1 = 0;
CALLPROC something();
var2 = CAST( 10/var1 AS VARCHAR(10) ); -- zero divide END
The variables used in the handler must be declared in the outermost DECLARE section.
Three pre-defined procedure variables are available so that a generic handler can determine what condition invoked it. They are:
• iih_errornumber integer – the DBMSERROR value that caused the handler to run
• iih_sqlstate char(5) – the SQLSTATE that caused the handler to run
• iih_conditionname varchar(256) – the user-defined condition name signaled to the handler.
If an error caused the handler to be triggered, either or both of iih_errornumber and iih_sqlstate will be set, and iih_conditionname will be blank. If the handler was run via a SIGNAL or RESIGNAL of a user-defined condition name, iih_conditionname will be set, iih_errornumber will be zero, and iih_sqlstate will be blank. The iih_* variables are available to any handler.
If a declared error condition were to happen within the called procedure, it has to be handled within that procedure, or it will be handled by the system. In other words, the scope of a handler is the procedure; the scope does not extend to called procedures.