Placement of the ON EXCEPTION Statement

The ON EXCEPTION statement is a declarative statement, not an executable statement. For this reason, ON EXCEPTION must precede any executable statement and must follow any DEFINE statement within an SPL routine.

The following example shows the correct placement of an ON EXCEPTION statement. Function add_salesperson( ) inserts a set of values into a table. If the table does not exist, it is created, and the values are inserted. The function also returns the total number of rows in the table after the insert occurs:

CREATE FUNCTION add_salesperson(last CHAR(15), first CHAR(15))
   RETURNING INT;
   DEFINE x INT;
   ON EXCEPTION IN (-206) -- If no table was found, create one
      CREATE TABLE emp_list          
          (lname CHAR(15),fname CHAR(15), tele CHAR(12));
      INSERT INTO emp_list VALUES -- and insert values
          (last, first, '800-555-1234');
   END EXCEPTION WITH RESUME;
   INSERT INTO emp_list VALUES (last, first, '800-555-1234');
   SELECT count(*) INTO x FROM emp_list;
   RETURN x;
END FUNCTION;

When an error occurs, the database server searches for the last ON EXCEPTION statement that traps the error code. If the database server finds no pertinent ON EXCEPTION statement, the error code passes back to the calling context (the SPL routine, application, or interactive user), and execution terminates.

In the previous example, the minus sign ( - ) is required in the IN clause that specifies error -206; most error codes are negative integers.

The next example uses two ON EXCEPTION statements with the same error number so that error code 691 can be trapped in two levels of nesting. All of the DELETE statements except the one that is marked { 6 } are within the scope of the first ON EXCEPTION statement. The DELETE statements that are marked { 1 } and { 2 } are within the scope of the inner ON EXCEPTION statement:

CREATE PROCEDURE delete_cust (cnum INT)
   ON EXCEPTION  IN (-691)    -- children exist
      BEGIN -- Begin-end so no other DELETEs get caught in here.
         ON EXCEPTION IN (-691)
            DELETE FROM another_child WHERE num = cnum;   { 1 }
            DELETE FROM orders WHERE customer_num = cnum; { 2 }
         END EXCEPTION -- for error -691
         DELETE FROM orders WHERE customer_num = cnum;    { 3 }
      END
      DELETE FROM cust_calls WHERE customer_num = cnum;   { 4 }
      DELETE FROM customer WHERE customer_num = cnum;     { 5 }
   END EXCEPTION
   DELETE FROM customer WHERE customer_num = cnum;        { 6 }
END PROCEDURE

Using the IN Clause to Trap Specific Exceptions

CREATE PROCEDURE ex_test()
   DEFINE error_num INT;
   ...
   ON EXCEPTION SET error_num
   -- action C
   END EXCEPTION
   ON EXCEPTION IN (-300)
   -- action B
   END EXCEPTION
   ON EXCEPTION IN (-210, -211, -212) SET error_num
   -- action A
   END EXCEPTION

Receiving Error Information in the SET Clause

Forcing Continuation of the Routine