Effects of Errors in Database Procedures
When an error occurs in a database procedure, the behavior of the DBMS Server depends on whether the procedure was invoked by a rule or executed directly (using EXECUTE PROCEDURE).
If the procedure was invoked by a rule, an error has the following effects:
• The procedure is terminated.
• Those statements in the procedure which have been executed are rolled back.
• The statement that fired the rule is rolled back.
If the procedure was executed directly, an error has the following effects:
• All statements in the procedure up to the point of the error are rolled back.
• The procedure continues execution with the statement following the statement that caused the error.
• Parameters passed by reference are not updated.
In both instances, the error is returned to the application in SQLSTATE, SQLCODE, and ERRORNO. In the case of the directly executed procedure, an error number is also returned to iierrornumber, a built-in variable available only in database procedures for error handling.
Note: A fatal error, such as dividing by zero, cannot be trapped in a database procedure and will cause the procedure to fail. For example, executing the following database procedure, in which the divisor variable is 0, will result in program failure:
...
update a_test set an_integer=int4(an_integer/:divisor);
select iierrornumber, iirowcount into :enumber, :rowcount;
if enumber=0 then
if rowcount=0 then
...