The code following a statement that causes an error executes only if an ON EXCEPTION RESUME clause appears in a procedure definition.
You can use nested compound statements to give you more control over which statements execute following an error and which do not.
The following example illustrates how nested compound statements can be used to control flow.
DROP PROCEDURE OuterProc; DROP PROCEDURE InnerProc; CREATE PROCEDURE InnerProc() BEGIN BEGIN DECLARE column_not_found EXCEPTION FOR SQLSTATE VALUE '52003'; MESSAGE 'Hello from InnerProc' TO CLIENT; SIGNAL column_not_found; MESSAGE 'Line following SIGNAL' TO CLIENT EXCEPTION WHEN column_not_found THEN MESSAGE 'Column not found handling' TO CLIENT; WHEN OTHERS THEN RESIGNAL; END; MESSAGE 'Outer compound statement' TO CLIENT; END; CALL InnerProc();
The Interactive SQL Messages tab then displays the following:
Hello from InnerProc Column not found handling Outer compound statement
When the SIGNAL statement that causes the error is encountered, control passes to the exception handler for the compound statement, and the Column not found handling message prints. Control then passes back to the outer compound statement and the Outer compound statement message prints.
If an error other than Column not found (SQLSTATE) is encountered in the inner compound statement, the exception handler executes the RESIGNAL statement. The RESIGNAL statement passes control directly back to the calling environment, and the remainder of the outer compound statement is not executed.
This example shows the output of the sa_error_stack_trace system procedure with RESIGNAL:
CREATE PROCEDURE DBA.error_reporting_procedure() BEGIN SELECT * FROM sa_error_stack_trace(); END; CREATE PROCEDURE DBA.proc1() BEGIN TRY BEGIN TRY DECLARE v INTEGER = 0; SET v = 1 / v; END TRY BEGIN CATCH CALL DBA.proc2(); END CATCH END TRY BEGIN CATCH CALL DBA.error_reporting_procedure(); END CATCH; CREATE PROCEDURE DBA.proc2() BEGIN CALL DBA.proc3(); END; CREATE PROCEDURE DBA.proc3() BEGIN RESIGNAL; END;
When the procedure above is invoked using CALL proc1(), the following result set is produced:
StackLevel | UserName | ProcName | LineNumber | IsResignal |
---|---|---|---|---|
1 | DBA | proc1 | 8 | 0 |
2 | DBA | proc2 | 3 | 0 |
3 | DBA | proc3 | 3 | 1 |
4 | DBA | proc1 | 5 | 0 |
This example shows the output of the sa_error_stack_trace system procedure with RESIGNAL and the BEGIN statement:
CREATE PROCEDURE DBA.error_reporting_procedure() BEGIN SELECT * FROM sa_error_stack_trace(); END; CREATE PROCEDURE DBA.proc1() BEGIN BEGIN DECLARE v INTEGER = 0; SET v = 1 / v; EXCEPTION WHEN OTHERS THEN CALL DBA.proc2(); END END EXCEPTION WHEN OTHERS THEN CALL DBA.error_reporting_procedure(); END; CREATE PROCEDURE DBA.proc2() BEGIN CALL DBA.proc3(); END; CREATE PROCEDURE DBA.proc3() BEGIN RESIGNAL; END;
When the procedure above is invoked using CALL proc1(), the following result set is produced:
StackLevel | UserName | ProcName | LineNumber | IsResignal |
---|---|---|---|---|
1 | DBA | proc1 | 8 | 0 |
2 | DBA | proc2 | 3 | 0 |
3 | DBA | proc3 | 3 | 1 |
4 | DBA | proc1 | 5 | 0 |