How to Use the DB2 conditional processor to handle SQL errors

Introduction: DB2Conditional processors play an irreplaceable role in stored procedures. In DB2,SQLThe stored procedure can use the DB2 Condition processor (Condition Handler) to processSQL Error(SQLERROR), SQL warning (SQLWARNING), no data (NOT FOUND) three common conditions and your own defined trigger conditions, you can use include EXIT, CONTINUE) three condition processors, including UNDO and UNDO. The following describes the detailed analysis of SQL errors processed by the DB2 conditional processor.

If SQL error, SQLWARNING, and not found occur during SQL stored procedures, the SQL stored procedure will automatically store the SQLCODE and SQLSTATE after executing the SQL statement in the variables SQLCODE and SQLSTATE you have defined in advance, and trigger the conditional processor you have defined in the stored procedure.

To handle errors in SQL stored procedures, you need to perform the following two steps: declare SQLCODE and SQLSTATE variables, and define a condition processor. In SQL stored procedures, you declare the SQLCODE and SQLSTATE variables using the following statements:

Declare sqlcode integer default 0;

Declare sqlstate char (5) DEFAULT '20140901 ';

When the stored procedure is executed, DB2 automatically pays the return code of the SQL statement to the two variables. you can insert these two values into the debugging table during program debugging, or use the condition processor to return these two values to the caller. This facilitates debugging of SQL stored procedures. Note: When you access SQLCODE and SQLSTATE during SQL storage, DB2 automatically sets SQLCODE and SQLSTATE to zero.

You can use the following statements to define the DB2 conditional processor:

DECLARE handler-type handler for condition

SQL-procedure-statement

Handler-type can be:

CONTINUE: After the SQL statement in the condition processor is executed, the SQL statement following the SQL statement with an error continues to be executed.

EXIT: After the SQL statement in the condition processor is executed, the SQL stored procedure is exited.

UNDO: This conditional processor is limited to ATOMIC action (ATOMIC) composite SQL statements. The SQL stored procedure rolls back the composite SQL statements containing the conditional processor, after executing the SQL statement in the condition processor, continue to execute the SQL statement following the ATOMIC action (ATOMIC) composite SQL statement.

The conditions include the following three common cases:

SQLEXCEPTION: returns any negative value during SQL Execution.

SQLWARNING: a warning occurs during SQL Execution (SQLWARN0 is 'W'), or any positive SQL return values that are not + 100. The corresponding SQLSTATE starts with '01.

Not found: the SQL return value is + 100 or the SQLSTATE starts with '02.

Of course, you can also use the DECLARE statement to define your own conditions for a specific SQLSATE.
 

Through the above study, I believe that you have a good understanding of how to use the DB2 conditional processor to handle SQL errors, and you will be able to easily solve similar problems.