Example of the RESIGNAL statement in a handler

Example: Using RESIGNAL to set an SQLSTATE valu

Suppose that you create an SQL procedure, named divide2, that computes the result of the division of two integers. You include SIGNAL to invoke the handler with an overflow condition that is caused by a zero divisor, and you include RESIGNAL to set a different SQLSTATE value for that overflow condition:

CREATE PROCEDURE divide2 
  (IN numerator INTEGER, IN denominator INTEGER,
   OUT divide_result INTEGER)
  LANGUAGE SQL
  BEGIN
    DECLARE overflow CONDITION FOR SQLSTATE '22003';
    DECLARE CONTINUE HANDLER FOR overflow
      RESIGNAL SQLSTATE '22375';
    IF denominator = 0 THEN
      SIGNAL overflow;
    ELSE
      SET divide_result = numerator / denominator;
    END IF;
  END

Example: RESIGNAL in a nested compound statement

If the following SQL procedure is invoked with argument values 1, 0, and 0, the procedure returns a value of 2 for RC and sets the oparm1 parameter to 650.

CREATE PROCEDURE resig4                                           
    (IN iparm1 INTEGER, INOUT oparm1 INTEGER, INOUT rc INTEGER)   
 LANGUAGE SQL                                                     
  A1: BEGIN                                                       
       DECLARE c1 INT DEFAULT 1;                                  
       DECLARE CONTINUE HANDLER FOR  SQLSTATE VALUE '01ABX'       
         BEGIN                                                    
          .... some other statements                              
          SET RC = 3;                                      6     
         END;                                                 
                                                              
       A2: SET oparm1 = 5;                                 1 
                                                              
       A3: BEGIN                                              
             DECLARE c1 INT DEFAULT 1;                        
             DECLARE CONTINUE HANDLER                         
                 FOR SQLSTATE VALUE '01ABC'                   
               BEGIN                                          
                 SET RC = 1;                               4 
                 RESIGNAL SQLSTATE VALUE '01ABX'           5 
                   SET MESSAGE_TEXT = 'get out of here';      
                 SET RC = 2;                                7 
               END;                                           
                                                              
             A7: SET oparm1 = oparm1 + 110;                2  
             SIGNAL SQLSTATE VALUE '01ABC'                 3 
                SET MESSAGE_TEXT = 'yikes';                   
             SET oparm1 = oparm1 + 215;                     8 
           END;                                                 
        SET oparm1 = oparm1 + 320;                          9   
  END                                                                                                               

The following notes refer to the preceding example:

1. oparm1 is initially set to 5.
2. oparm1 is incremented by 110. The value of oparm1 is now 115.
3. The SIGNAL statement causes the condition handler that is contained in the A3 compound statement to be activated.
4. In this condition handler, RC is set to 1.
5. The RESIGNAL statement changes the SQLSTATE to 01ABX. This value causes the continue handler in the A1 compound statement to be activated.
6. RC is set to 3 in this condition handler. Because this condition handler is a continue handler, when the handler action completes, control returns to the SET statement after the RESIGNAL statement.
7. RC is set to 2 in this condition handler. Because this condition handler is a continue handler, control returns to the SET statement that follows the SIGNAL statement that caused the condition handler to be activated.
8. oparm1 is incremented by 215. The value of oparm is now 330.
9. oparm1 is incremented by 320. The value of oparm is now 650.