Exception Handling in Stored Procedures

 

The following describes how to judge SQL statement errors in the stored procedure.ProgramDevelopers follow this method to write SQL stored procedures, so as to avoid the problem of unclear application status during program joint debugging.

See the followingCodeYou can define the exit parameters of the execution status in the SQL stored procedure, and return the sqlcode reported by the system as much as possible, instead of defined by the individual. This will help you better identify the errors, you can also return the error description. Some developers Define the return code as 79700 somehow. If the code was developed by referring to the DB2 instructions, the meaning of the description may be misunderstood, this problem has led to misleading judgment during the joint debugging process and the cause of the problem cannot be determined. I hope you will pay attention to it;

If you need to ignore not found, you can declare continue handler for not found. If you need it, you can set returncode to zero so that this not found can be ignored when you determine returncode in the program;

You can define declare exit handler for sqlexception. When an SQL error occurs, interrupt the Program Execution Process and exit the stored procedure. You can also define declare continue handler for sqlexception and determine the sqlcode value, this method is used to ignore the error code you want to ignore. This method is listed at the end of the program:

Create procedure out_language (..., out returncode integer, out returnmsg char (32 ))

-- Returncode: returns the error code in execution.
-- Returnmsg returns the error description in execution
Specific SQL _out_language
Language SQL
Begin
Declare sqlcode integer default 0;
Declare sqlstate char (5) default '20140901 ';
Declare errorlabel char (32) default '';
......

-- In case of no data found
Declare continue handler for not found
Begin
Set returncode = sqlcode;
-- Set returncode = 0, because this declare is used to ignore the not found execution result.
End;

-- In case of SQL Error
Declare exit handler for sqlexception
Begin
Set returncode = sqlcode;
Set returnmsg = errorlabel;
End;

......

Sqlexception can also be defined as this to ignore some specific error code, but the returncode value must be determined in the program:
-- In case of SQL Error
Declare continue handler for sqlexception
Begin
If (sqlcode =-454) then
Set returncode = 0;
-- The created database object already exists or the inserted records are repeated on the unique key value.
Else
Set returncode = sqlcode;
Set returnmsg = errorlabel;
End if;
End;
......

If (returncode = 0) then
......
End if;

If (returncode = 0) then
...
End if;
End
-- End of Stored Procedure

Dwd_china reply to: 09:20:16
Add such exception processing after the stored procedure.
Exception
---- Error handling
When dup_val_on_index then
---- Primary key conflict
Rollback;

When value_error then
---- Length truncation error
Rollback;

When program_error then
---- Internal PL/SQL errors
Rollback;

When timeout_on_resource then
---- System wait timeout
Rollback;

When others then
---- Other exception errors
Rollback;

Trackback: http://www.kpwang.com/ibm_db2/110642311549.htm