Placement of the ON EXCEPTION statement

The ON EXCEPTION statement is a declarative statement, not an executable statement. For this reason, ON EXCEPTION must follow immediately after any DEFINE statements, and must precede any executable statement within the same SPL statement block.

Because the body of the SPL routine is a statement block, the ON EXCEPTION statement often appears at the top of the routine, and applies to all of the code in the routine.

The following example positions an ON EXCEPTION statement so that a FOREACH statement can continue processing rows after an error occurs. Procedure X( ) reads customer numbers from table A and inserts them into table B. Because the INSERT statement is in scope of the ON EXCEPTION statement, any error during an INSERT operation causes control of execution to move to the next row of the FOREACH cursor, without terminating the FOREACH loop.

CREATE PROCEDURE X()

    DEFINE v_cust_num CHAR(20);

    FOREACH cs_insert FOR SELECT cust_num INTO v_cust_num FROM A
        BEGIN
            ON EXCEPTION
            END EXCEPTION WITH RESUME;
            INSERT INTO B(cust_num) VALUES(v_cust_num);
        END
    END FOREACH

END PROCEDURE

In the next example, 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 is passed 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