Exception Handling in Database Procedures
Runtime errors or warnings that occur in a database procedure can be handled by raising exceptions. Exception handling allows the program to continue operating when specific errors are encountered.
Here is a simple example of a divide-by-zero exception handler:
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 a_general_error_procedure(what='divzero');
END;
var1 = 0;
CALLPROC something();
var2 = CAST( 10/var1 AS VARCHAR(10) ); -- zero divide
END
The syntax elements pertaining to exception handling are:
• Condition names (DECLARE cond_name CONDITION FOR)
• Handler declarations inside a BEGIN-END block
• Nested BEGIN-END blocks
• SIGNAL and RESIGNAL statements
The DECLARE statement defines a condition. The condition can be an error condition that is identified by an SQLSTATE code, a DBMSERROR number, or a predefined exception name. The condition can also be a user declared condition not associated with any error; user conditions are raised using the SIGNAL and RESIGNAL statements.
When the condition occurs, it fires an exception handler. The two types of exception handlers are EXIT handlers and CONTINUE handlers. Each behaves differently after a condition is raised and the handler code runs. An EXIT handler exits the BEGIN-END block that contains the handler. A CONTINUE handler terminates the executing statement (including any rules that the executing statement might have fired) and continues with the following statement. Any inserts, updates, or deletes that the statement causing the error did are rolled back; this includes any database changes done by rules fired by the error statement. Changes to local DB procedure variables are not rolled back.
Exception Handler Syntax
BEGIN
DECLARE CONTINUE | EXIT HANDLER FOR condition_value [, condition value] handler action
<statement-list>
END
where condition value is:
SQLSTATE [VALUE] string_literal
DBMSERROR [VALUE] integer_constant | string_literal
named condition
User-named condition name or predefined condition name
SQLEXCEPTION
Is handle-able error
SQLWARNING
Is a general SQLERROR, SQLWARNING
It can be defined as:
DECLARE EXIT | CONTINUE HANDLER FOR SQLERROR, SQLWARNING
BEGIN
<handler action>
END;
handler action
Is a BEGIN-END block
statement-list
Can contain SQL statements (including a SIGNAL and RESIGNAL) and BEGIN-END blocks
Handlers effectively have runtime scope. In any given procedure, if there is no handler for a condition, the procedure is terminated and control passes to the next outer scope. If procedure x() calls procedure y(), and a zero divide happens inside procedure y(), and if y() did not declare a suitable handler, y() is terminated and the condition is passed up to x(). In the previous example, the caller does have a zero-divide handler that handles the condition and (since it is an EXIT handler) exits the block and thus the pname() procedure.
The most appropriate handler for any handle-able condition is the most recently encountered handler for the condition; but if the condition occurs inside a handler, that handler cannot be the most appropriate handler for that condition.
BEGIN-END is allowed anywhere a statement is allowed. (Loop bodies are not implicit BEGIN-END blocks; if a handler is to have loop body scope, the loop body must be a BEGIN-END compound statement.) DECLARE is allowed inside and outside a BEGIN block. The inner DECLAREs can only declare handlers, not variables or condition names.
GOTO into or out of a handler is not permitted. (GOTO strictly within a handler body is permitted.)
Conditions can be raised explicitly by using SIGNAL and re-raised using RESIGNAL statements:
SIGNAL condition [SET signal info item list]
RESIGNAL [condition] [SET signal info item list]
SIGNAL must specify the condition to signal. The signal info item list lets you explicitly set various parts of the standard diagnostic information area. RESIGNAL must occur inside a handler action, and typically need not specify a condition. RESIGNAL is used as a “pass it along” mechanism.
The general condition value SQLEXCEPTION means “any error”; SQLWARNING means “any warning”, NOT FOUND means “no rows returned from a DML operation.” If another in-scope handler is defined that specifically names a condition value, that handler applies rather than a general handler.
SQLSTATE values are 5-character literals such as '22003' (numeric value out of range), or '22012' (integer divide by zero).
A Vector DBMSERROR as (E_VWXXX) value is either an integer literal (such as 4111, corresponding to E_US100F String cannot be converted), or a string literal 'E_US100F'. The integer value is what would be retrieved by the predefined variable IIERRORNUMBER.
If a condition handler causes an error or warning, it is as if an implicit RESIGNAL statement for that error or warning that was executed.
A more complex version of the divide-by-zero example could use a predefined condition name and a different handler, depending on context:
CREATE PROCEDURE pname(...) =
DECLARE var1 INTEGER; var2 VARCHAR(10);
BEGIN
DECLARE EXIT HANDLER FOR ZERO_DIVIDE
BEGIN
MESSAGE 'Zero divide error';
END;
var2 = 'Foobar snarb';
var1 = 10 / 0; -- causes message and exit
BEGIN
DECLARE CONTINUE HANDLER FOR ZERO_DIVIDE
BEGIN
var1 = 0;
END;
var1 = 10 / 0; -- causes var1 to be set to 0
var2 = VARCHAR(var1);
END; -- inner block
-- A zero divide here is handled by the EXIT handler,
-- as the inner CONTINUE handler is now out of scope.
-- Lets pretend MESSAGE can handle an expression (!)
MESSAGE 'var2 value is ' + var2;
-- the output would be 'var2 value is 0'
END