The example associates a handler with SQLSTATE 23000, which occurs for a duplicate-key error : Condition HANDLER « Procedure Function « SQL / MySQL






  1. SQL / MySQL
  2. Procedure Function
  3. Condition HANDLER

The example associates a handler with SQLSTATE 23000, which occurs for a duplicate-key error

 
mysql>
mysql> CREATE TABLE t (s1 int,primary key (s1));
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql>
mysql> delimiter //
mysql>
mysql> CREATE PROCEDURE handlerdemo ()
    -> BEGIN
    ->   DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
    ->   SET @x = 1;
    ->   INSERT INTO test.t VALUES (1);
    ->   SET @x = 2;
    ->   INSERT INTO test.t VALUES (1);
    ->   SET @x = 3;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> Delimiter ;
mysql>
mysql> CALL handlerdemo();
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> SELECT @x;
+------+
| @x   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> drop procedure handlerdemo;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> drop table t;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql>
mysql>








Related examples in the same category

1.Declaring a Condition and Handler
2.Declaring a Condition and Handler in action
3.DECLARE CONTINUE HANDLER FOR NOT FOUND SET
4.Checking flag
5.Using while to check the condition
6.DECLARE EXIT HANDLER FOR
7.Checking Exit flag
8.Using the IF statement to verify 'CONTINUE HANDLER'
9.To ignore a condition