Exception Handling

SQL exceptions are events that occur during the execution of an SQL statement contained in a stored procedure or function that corresponds to either an error condition of some kind or an unusual status condition, such as a data truncation, or a normal status condition such as not found.

As described earlier, exception handlers can be declared for a specific exception condition or a general exception condition. When an exception is raised by SQL during the execution of a statement in a stored routine, the system will store information about the exception in the diagnostics area and then transfer control to the declared handler in the nearest scope that is defined for that particular exception. The GET DIAGNOSTICS statement can be used in an exception handler to get information about the nature of the error that invoked the handler.

The CURRENT option returns the diagnostic information for the most recently executed statement. Inside a handler declaration this would return the information associated with the handler's BEGIN statement! Hence, you will always want to use the STACKED option to retrieve information about the statement which raised the condition.

Exception handlers can also be defined for user conditions. Control is transferred to an exception handler for a user condition only through execution of the SIGNAL statement.

Unhandled exceptions are returned to the program or stored procedure/function that called the procedure or function.

The example below uses user conditions to show the behavior of nested handlers.

 1 create proc nested_handler
 2 begin
 3     declare v1 int default 1;
 4     declare v2 int default 2;
 5     declare block char(5) default "outer";
 6     declare c1 condition;
 7     declare c2 condition;
 8     declare continue handler for c1 
 9         begin
10             set v1 = 10;
11             set v2 = 20;
12         end;
13     select block, v1, v2;
14     begin 
15         declare block char(5) default "inner";
16         declare continue handler for c1 set v1 = 100;
17         declare continue handler for c2 set v2 = 200;
18         select block, v1, v2;
19         signal c1;
20         select block, c1, v1, v2;
21         signal c2;
22         select block, c2, v1, v2;
23     end;
24     signal c1;
25     select block, c1, v1, v2;
26 end;

The first statement to execute it the SELECT at line 13 where block = "outer", v1 = 1, and v2 = 2; The next statement to execute is the SELECT at line 18 where v1 and v2 have not changed values but block = "inner". The SIGNAL statement at line 19 invokes the c1 handler declared at line 16 (not the one at line 8) setting v1 to 100. The SIGNAL statement at line 21 invokes the c2 handler at line 17 setting v2 to 200. The SIGNAL statement at line 24 invokes the c1 handler at line 8 setting v1 to 10 and v2 to 20. The results of a CALL to nested_handler is shown below.

rdm-sql: call bookshop.nested_handler;
BLOCK           V1          V2
outer            1           2

BLOCK           V1          V2
inner            1           2

BLOCK  C1           V1          V2
inner  c1          100           2

BLOCK  C2           V1          V2
inner  c2          100         200

BLOCK  C1           V1          V2
outer  c1           10          20