Filter
Top Products
Errors and warnings in procedures
262
MESSAGE ’Column not found handling.’;
WHEN OTHERS THEN
RESIGNAL ;
END
The EXCEPTION statement declares the exception handler itself. The lines
following the EXCEPTION statement are not executed unless an error occurs.
Each WHEN clause specifies an exception name (declared with a DECLARE
statement) and the statement or statements to be executed in the event of that
exception. The WHEN OTHERS THEN clause specifies the statement(s) to be
executed when the exception that occurred is not in the preceding WHEN
clauses.
In this example, the statement RESIGNAL passes the exception on to a higher-
level exception handler. RESIGNAL is the default action if WHEN OTHERS
THEN is not specified in an exception handler.
The following statement executes the
OuterProc procedure:
CALL OuterProc();
The message window of the server then displays the following:
Hello from OuterProc.
Hello from InnerProc.
Column not found handling.
SQLSTATE set to 00000 in OuterProc.
Notes
• The lines following the SIGNAL statement in InnerProc are not executed;
instead, the EXCEPTION statements are executed.
• As the error encountered was a column not found error, the MESSAGE
statement included to handle the error is executed, and SQLSTATE is reset
to zero (indicating no errors).
• After the exception handling code is executed, control is passed back to
OuterProc, which proceeds as if no error was encountered.
• You should not use ON EXCEPTION RESUME together with explicit
exception handlers. The exception handler code is not executed if ON
EXCEPTION RESUME is included.
• You should use explicit exception handling code after each statement that
may potentially generate an exception whenever you use ON
EXCEPTION RESUME. You gain flexibility in handling errors, but the
cost is more code and a higher risk of bugs in your code.