How to Handle SQL Exceptions
When trying to determine the source of an SQL error and SQLSTATE indicates an exception condition by holding a value other than 00000, 00001, or 00002, you may want to handle the situation in one of the following ways:
Return control to the parent procedure that called the subprocedure that raised the exception.
Use a WHENEVER clause to branch to an exception-handling routine or perform some other action.
Handle the exception on the spot with a compound SQL statement. A compound SQL statement consists of one or more simple SQL statements, sandwiched between BEGIN and END keywords.
The following is an example of a compound-statement exception handler:
BEGIN DECLARE ValueOutOfRange EXCEPTION FOR SQLSTATE'73003' ; INSERT INTO FOODS (Calories) VALUES (:cal) ; SIGNAL ValueOutOfRange ; MESSAGE 'Process a new calorie value.' EXCEPTION WHEN ValueOutOfRange THEN MESSAGE 'Handling the calorie range error' ; WHEN OTHERS THEN RESIGNAL ; END
With one or more DECLARE statements, you can give names to specific SQLSTATE values that you suspect may arise. The INSERT statement is the one that might cause an exception to occur. If the value of :cal exceeds the maximum value for a SMALLINT data item, SQLSTATE is set to “73003”. The SIGNAL statement signals an exception condition. It clears the top diagnostics area.
It sets the RETURNED_SQLSTATE field of the diagnostics area to the SQLSTATE for the named exception. If no exception has occurred, the series of statements represented by the MESSAGE ‘Process a new calorie value’ statement is executed. However, if an exception has occurred, that series of statements is skipped, and the EXCEPTION statement is executed.
If the exception was a ValueOutOfRange exception, then a series of statements represented by the MESSAGE ‘Handling the calorie range error’ statement is executed. The RESIGNAL statement is executed if the exception isn’t a ValueOutOfRange exception.
RESIGNAL merely passes control of execution to the calling parent procedure. That procedure may have additional error-handling code to deal with exceptions other than the expected value-out-of-range error.