SQL Statements › CREATE PROCEDURE › CREATE PROCEDURE Syntax and Description › Condition Handler

Condition Handler

A condition handler is a user-written routine embedded in a user-witten procedure that detects and acts to recover from errors, warnings, and any user-defined condition that requires action or remediation. When an end-user merges the functionalities provided by condition handlers and the Diagnostics Area Stack, basic error recovery can be provided, with complete sequences of errors available for retrieval, and business-rule related functions can be performed automatically. The condition declaration and condition handler are found in the syntax for the compound statement (see Compound Statement).

A procedure that, for example, takes customer orders from a phone operator, checks inventory, and reserves warehouse stock to cover them should also, if every item is in stock, ship the orders immediately. A customer, however, could in addition request that individual items be shipped as they become available. The logic of a condition handler could therefore include that situation as follows (for details about condition handling, see Condition Handler. To implement the procedure just described, do the following:

1. Declare names for the conditions to be handled:

    DECLARE ALL_ITEMS_IN_STOCK CONDITION;
    DECLARE SHIP_AS_AVAILABLE CONDITION;
   

   Lists of SQLSTATEs can also be named as conditions using a FOR clause that is not included in this example, but can be seen in the example starting in Sample Procedure 2. General conditions of SQLEXCEPTION, SQLWARNING, and NOT FOUND are automatically defined (see the description of cond-declarations).

2. Define the condition handler (see the two examples that follow).

   This example sends a message to initiate action by warehouse personnel. In this example, a compound statement is supplied that includes SQL variable references and some logic. A condition handler, however, could also consist of only one SQL statement, as shown in the example that follows this one.

   DECLARE CONTINUE HANDLER FOR ALL_ITEMS_IN_STOCK, SHIP_AS_AVAILABLE
   ship_it: BEGIN ATOMIC
              DECLARE messageLocal VARCHAR(80);
              IF numberItemsAvailable = numberItemsOrdered THEN
                 SET messageLocal = 'ORDER IS COMPLETE.  SHIP NOW.';
              ELSE
                  SET messageLocal = 'SHIP ALL AVAILABLE ITEMS NOW.';
              END IF;
              INSERT INTO warehouseToDo (orderId, instructions)
                           VALUES  (orderIdParameter, messageLocal);
            END end_ship_it;
   

   The following example performs essentially the same function as the example just shown. It presents, however, an alternative to the more complicated logic of the previous example by using an INSERT statement coded into the warehouseToDo table.

   DECLARE CONTINUE HANDLER FOR ALL_ITEMS_IN_STOCK, SHIP_AS_AVAILABLE
     INSERT INTO warehouseToDo (orderId, instructions)
                  VALUES (orderIdParameter, 'SHIP ALL AVAILABLE ITEMS NOW');
   

3. When you detect the defined condition, signal this fact to the condition handler. If, for example, the procedure determines that some items are not in-stock, but the client has requested that items ship as they become available, the following statement could be executed to trigger the handler to execute.

   SIGNAL SHIP_AS_AVAILABLE;
   

   Note: Condition handlers triggered by particular SQLSTATEs execute automatically and therefore have no need for explicit SIGNAL statements.

For examples of error handlers embedded in procedures, see the example procedure starting in Sample Procedure 2