Filter
Top Products
CHAPTER 6 Using Procedures and Batches
263
• If the error handling code for the
column not found exception is simply
a RESIGNAL statement, control is passed back to the
OuterProc procedure
with SQLSTATE still set at the value 52003. This is just as if there were
no error handling code in
InnerProc. As there is no error handling code in
OuterProc, the procedure fails.
Exception handling
and atomic compound
statements
When an exception is handled inside a compound statement, the compound
statement completes without an active exception and the changes before the
exception are not undone. This is true even for atomic compound statements.
If an error occurs within an atomic compound statement and is explicitly
handled, some but not all of the statements in the atomic compound statement
are executed.
Nested compound statements and exception handlers
The code following a statement that causes an error is not executed unless an
ON EXCEPTION RESUME clause is included in a procedure definition.
You can use nested compound statements to give you more control over which
statements are and are not executed following an error.
Drop the procedures
Remember to drop both the InnerProc and OuterProc procedures before
continuing with the tutorial. You can do this by entering the following
commands in the command window:
DROP PROCEDURE OUTERPROC;
DROP PROCEDURE INNERPROC
The following demonstration procedure illustrates how nested compound
statements can be used to control flow. The procedure is based on that used as
an example in “Default error handling in procedures” on page 256
CREATE PROCEDURE InnerProc()
BEGIN
DECLARE column_not_found
EXCEPTION FOR SQLSTATE VALUE ’52003’;
MESSAGE ’Hello from InnerProc’;
BEGIN
SIGNAL column_not_found;
MESSAGE ’Line following SIGNAL’
EXCEPTION
WHEN column_not_found THEN
MESSAGE ’Column not found handling’;
WHEN OTHERS THEN
RESIGNAL;