Defining condition handlers that execute more than one statement

A condition handler defines the action that an SQL procedure takes when a particular condition occurs. You must specify the action as a single SQL procedure statement.

Procedure

To define a condition handler that executes more than one statement when the specified condition occurs, specify a compound statement within the declaration of that handler.

Examples

Example

The following example shows a condition handler that captures the SQLSTATE value and sets a local flag to TRUE.

BEGIN
   DECLARE SQLSTATE CHAR(5);
   DECLARE PrvSQLState CHAR(5) DEFAULT '00000';
   DECLARE ExceptState INT;
   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
      BEGIN
           SET PrvSQLState = SQLSTATE;
           SET ExceptState = TRUE;
      END;
   ...
END

Example

The following example declares a condition handler for SQLSTATE 72822. The subsequent SIGNAL statement is within the scope of this condition handler and thus activates this handler. The condition handler tests the value of the SQL variable VAR with an IF statement. Depending on the value of VAR, the SQLSTATE is changed and the message text is set.

DECLARE EXIT HANDLER FOR SQLSTATE '72822'
       IF ( VAR = 'OK' ) THEN
         RESIGNAL SQLSTATE '72623'
                  SET MESSAGE_TEXT = 'Got SQLSTATE 72822';
       ELSE
         RESIGNAL SQLSTATE '72319'
                  SET MESSAGE_TEXT = VAR;
       END IF;

SIGNAL SQLSTATE '72822';