Oracle 5.0, Reference Manual

Page: 1168 / 3232

Condition Handling
1148
-> INSERT INTO test.t VALUES (1);
-> SET @x = 3;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> CALL handlerdemo()//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x//
+------+
| @x |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
Notice that
@x
is
3
after the procedure executes, which shows that execution continued to the end
of the procedure after the error occurred. If the
DECLARE ... HANDLER
statement had not been
present, MySQL would have taken the default action (
EXIT
) after the second
INSERT
failed due to the
PRIMARY KEY
constraint, and
SELECT @x
would have returned
2
.
To ignore a condition, declare a
CONTINUE
handler for it and associate it with an empty block. For
example:
DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;
The scope of a block label does not include the code for handlers declared within the block. Therefore,
the statement associated with a handler cannot use
ITERATE
or
LEAVE
to refer to labels for blocks that
enclose the handler declaration. Consider the following example, where the
REPEAT
block has a label
of
retry
:
CREATE PROCEDURE p ()
BEGIN
DECLARE i INT DEFAULT 3;
retry:
REPEAT
BEGIN
DECLARE CONTINUE HANDLER FOR SQLWARNING
BEGIN
ITERATE retry; # illegal
END;
IF i < 0 THEN
LEAVE retry; # legal
END IF;
SET i = i - 1;
END;
UNTIL FALSE END REPEAT;
END;
The
retry
label is in scope for the
IF
statement within the block. It is not in scope for the
CONTINUE
handler, so the reference there is invalid and results in an error:
ERROR 1308 (42000): LEAVE with no matching label: retry
To avoid references to outer labels in handlers, use one of these strategies:
• To leave the block, use an
EXIT
handler. If no block cleanup is required, the
BEGIN ... END
handler body can be empty:
DECLARE EXIT HANDLER FOR SQLWARNING BEGIN END;
Otherwise, put the cleanup statements in the handler body:
DECLARE EXIT HANDLER FOR SQLWARNING
BEGIN
block cleanup statements
END;