MySQL Tutorial/Procedure Function/Condition HANDLER
Содержание
- 1 Checking Exit flag
- 2 Checking flag
- 3 Condition and Handler in action
- 4 DECLARE CONTINUE HANDLER FOR NOT FOUND SET
- 5 DECLARE EXIT HANDLER FOR
- 6 Declaring a Condition and Handler
- 7 The example associates a handler with SQLSTATE 23000, which occurs for a duplicate-key error
- 8 To ignore a condition
- 9 Using the IF statement to verify "CONTINUE HANDLER"
- 10 Using while to check the condition
Checking Exit flag
mysql>
mysql>
mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date DATE,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.08 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc
-> (in_first_name VARCHAR(30),
-> in_city VARCHAR(30),
-> in_id INT)
-> MODIFIES SQL DATA
-> BEGIN
-> DECLARE duplicate_key INT DEFAULT 0;
-> BEGIN
-> DECLARE EXIT HANDLER FOR 1062 /* Duplicate key*/ SET duplicate_key=1;
->
-> INSERT INTO employee (first_name,city,id)
-> VALUES(in_first_name,in_city,in_id);
->
-> SELECT CONCAT("Employee ",in_first_name," created") as "Result";
-> END;
->
-> IF duplicate_key=1 THEN
-> SELECT CONCAT("Failed to insert ",in_first_name,
-> ": duplicate key") as "Result";
-> END IF;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql> call myProc("new FirstName","New City",999 );
+--------------------------------+
| Result |
+--------------------------------+
| Employee new FirstName created |
+--------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> select * from Employee;
+------+---------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+---------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
| 999 | new FirstName | NULL | NULL | NULL | NULL | New City | NULL |
+------+---------------+-----------+------------+------------+---------+-----------+-------------+
9 rows in set (0.00 sec)
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.01 sec)
mysql>
Checking flag
mysql>
mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date DATE,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> delimiter $$
mysql> CREATE PROCEDURE myProc (in_customer_id INT)
-> BEGIN
->
-> DECLARE l_first_name VARCHAR(30);
-> DECLARE l_id INT;
-> DECLARE l_city VARCHAR(30);
-> DECLARE no_more_departments INT;
->
-> DECLARE dept_csr CURSOR FOR
-> SELECT id,first_name, city
-> FROM employee;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;
->
->
-> OPEN dept_csr;
-> dept_loop1:LOOP
-> FETCH dept_csr INTO l_id,l_first_name,l_city;
->
-> select l_id, l_first_name, l_city;
-> IF no_more_departments=1 THEN
-> LEAVE dept_loop1;
-> END IF;
-> END LOOP;
-> CLOSE dept_csr;
-> SET no_more_departments=0;
->
->
->
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call myProc(1);
+------+--------------+---------+
| l_id | l_first_name | l_city |
+------+--------------+---------+
| 1 | Jason | Toronto |
+------+--------------+---------+
1 row in set (0.00 sec)
+------+--------------+-----------+
| l_id | l_first_name | l_city |
+------+--------------+-----------+
| 2 | Alison | Vancouver |
+------+--------------+-----------+
1 row in set (0.01 sec)
+------+--------------+-----------+
| l_id | l_first_name | l_city |
+------+--------------+-----------+
| 3 | James | Vancouver |
+------+--------------+-----------+
1 row in set (0.01 sec)
+------+--------------+-----------+
| l_id | l_first_name | l_city |
+------+--------------+-----------+
| 4 | Celia | Vancouver |
+------+--------------+-----------+
1 row in set (0.01 sec)
+------+--------------+-----------+
| l_id | l_first_name | l_city |
+------+--------------+-----------+
| 5 | Robert | Vancouver |
+------+--------------+-----------+
1 row in set (0.01 sec)
+------+--------------+----------+
| l_id | l_first_name | l_city |
+------+--------------+----------+
| 6 | Linda | New York |
+------+--------------+----------+
1 row in set (0.01 sec)
+------+--------------+----------+
| l_id | l_first_name | l_city |
+------+--------------+----------+
| 7 | David | New York |
+------+--------------+----------+
1 row in set (0.01 sec)
+------+--------------+-----------+
| l_id | l_first_name | l_city |
+------+--------------+-----------+
| 8 | James | Vancouver |
+------+--------------+-----------+
1 row in set (0.01 sec)
+------+--------------+-----------+
| l_id | l_first_name | l_city |
+------+--------------+-----------+
| 8 | James | Vancouver |
+------+--------------+-----------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.48 sec)
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
Condition and Handler in action
By declaring conditions and handlers, you can catch certain MySQL errors or SQLSTATE conditions.
mysql>
mysql>
mysql> DELIMITER //
mysql>
mysql> CREATE FUNCTION perform_logic (some_input INT(10)) returns INT(10)
-> BEGIN
-> DECLARE problem CONDITION FOR 1265;
-> DECLARE EXIT HANDLER FOR problem
-> RETURN NULL;
->
-> # do some logic, if the problem condition is met
-> # the function will exit, returning a NULL
->
-> RETURN 1;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql>
mysql> drop function perform_logic;
Query OK, 0 rows affected (0.00 sec)
mysql>
DECLARE CONTINUE HANDLER FOR NOT FOUND SET
mysql>
mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date DATE,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> delimiter $$
mysql> CREATE PROCEDURE myProc (in_customer_id INT)
-> BEGIN
->
-> DECLARE l_first_name VARCHAR(30);
-> DECLARE l_id INT;
-> DECLARE l_city VARCHAR(30);
-> DECLARE no_more_departments INT;
->
-> DECLARE dept_csr CURSOR FOR
-> SELECT id,first_name, city
-> FROM employee;
->
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;
->
-> SET no_more_departments=0;
-> OPEN dept_csr;
-> REPEAT
-> FETCH dept_csr INTO l_id,l_first_name,l_city;
->
-> select l_id;
-> select l_first_name;
-> select l_city;
->
-> UNTIL no_more_departments
-> END REPEAT;
-> CLOSE dept_csr;
-> SET no_more_departments=0;
->
->
->
->
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql>
mysql> call myProc(1);
+------+
| l_id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
+--------------+
| l_first_name |
+--------------+
| Jason |
+--------------+
1 row in set (0.00 sec)
+---------+
| l_city |
+---------+
| Toronto |
+---------+
1 row in set (0.00 sec)
+------+
| l_id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
+--------------+
| l_first_name |
+--------------+
| Alison |
+--------------+
1 row in set (0.00 sec)
+-----------+
| l_city |
+-----------+
| Vancouver |
+-----------+
1 row in set (0.00 sec)
+------+
| l_id |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
+--------------+
| l_first_name |
+--------------+
| James |
+--------------+
1 row in set (0.02 sec)
+-----------+
| l_city |
+-----------+
| Vancouver |
+-----------+
1 row in set (0.02 sec)
+------+
| l_id |
+------+
| 4 |
+------+
1 row in set (0.02 sec)
+--------------+
| l_first_name |
+--------------+
| Celia |
+--------------+
1 row in set (0.02 sec)
+-----------+
| l_city |
+-----------+
| Vancouver |
+-----------+
1 row in set (0.02 sec)
+------+
| l_id |
+------+
| 5 |
+------+
1 row in set (0.02 sec)
+--------------+
| l_first_name |
+--------------+
| Robert |
+--------------+
1 row in set (0.02 sec)
+-----------+
| l_city |
+-----------+
| Vancouver |
+-----------+
1 row in set (0.02 sec)
+------+
| l_id |
+------+
| 6 |
+------+
1 row in set (0.02 sec)
+--------------+
| l_first_name |
+--------------+
| Linda |
+--------------+
1 row in set (0.25 sec)
+----------+
| l_city |
+----------+
| New York |
+----------+
1 row in set (0.27 sec)
+------+
| l_id |
+------+
| 7 |
+------+
1 row in set (0.27 sec)
+--------------+
| l_first_name |
+--------------+
| David |
+--------------+
1 row in set (0.27 sec)
+----------+
| l_city |
+----------+
| New York |
+----------+
1 row in set (0.27 sec)
+------+
| l_id |
+------+
| 8 |
+------+
1 row in set (0.27 sec)
+--------------+
| l_first_name |
+--------------+
| James |
+--------------+
1 row in set (0.27 sec)
+-----------+
| l_city |
+-----------+
| Vancouver |
+-----------+
1 row in set (0.27 sec)
+------+
| l_id |
+------+
| 8 |
+------+
1 row in set (0.27 sec)
+--------------+
| l_first_name |
+--------------+
| James |
+--------------+
1 row in set (0.27 sec)
+-----------+
| l_city |
+-----------+
| Vancouver |
+-----------+
1 row in set (0.27 sec)
Query OK, 0 rows affected (0.27 sec)
mysql>
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
DECLARE EXIT HANDLER FOR
mysql>
mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date DATE,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc
-> (in_first_name VARCHAR(30),
-> in_city VARCHAR(30),
-> in_id INT)
-> MODIFIES SQL DATA
-> BEGIN
-> DECLARE duplicate_key INT DEFAULT 0;
-> BEGIN
-> DECLARE EXIT HANDLER FOR 1062 /* Duplicate key*/ SET duplicate_key=1;
->
-> INSERT INTO employee (first_name,city,id)
-> VALUES(in_first_name,in_city,in_id);
->
-> SELECT CONCAT("Employee ",in_first_name," created") as "Result";
-> END;
->
-> IF duplicate_key=1 THEN
-> SELECT CONCAT("Failed to insert ",in_first_name,
-> ": duplicate key") as "Result";
-> END IF;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql> call myProc("new FirstName","New City",999 );
+--------------------------------+
| Result |
+--------------------------------+
| Employee new FirstName created |
+--------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> select * from Employee;
+------+---------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+---------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
| 999 | new FirstName | NULL | NULL | NULL | NULL | New City | NULL |
+------+---------------+-----------+------------+------------+---------+-----------+-------------+
9 rows in set (0.00 sec)
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
Declaring a Condition and Handler
Handlers are designed to detect certain errors or warnings and allow you to take action.
For example, a truncation issues a warning and returns an error, and also sets the SQLSTATE.
For the details of each error number and its meaning, see http://dev.mysql.ru/doc/mysql/en/Error-handling.html.
A handler is declared with a handler type, condition, and statement:
DECLARE <handler type> HANDLER FOR <condition> <statement>;
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>
To ignore a condition
If you want to ignore a condition, you can declare a CONTINUE handler for it and associate it with an empty block.
mysql>
mysql> CREATE TABLE t (s1 int,primary key (s1));
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> delimiter //
mysql>
mysql> CREATE PROCEDURE handlerdemo ()
-> BEGIN
-> DECLARE CONTINUE HANDLER FOR SQLSTATE "23000" SET @x2 = 1;
-> DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;
-> 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.00 sec)
mysql>
mysql> drop table t;
Query OK, 0 rows affected (0.02 sec)
mysql>
Using the IF statement to verify "CONTINUE HANDLER"
mysql>
mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date DATE,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc
-> (in_first_name VARCHAR(30),
-> in_city VARCHAR(30),
-> in_id INT)
-> MODIFIES SQL DATA
-> BEGIN
-> DECLARE duplicate_key INT DEFAULT 0;
->
-> DECLARE CONTINUE HANDLER FOR 1062 /* Duplicate key*/
-> SET duplicate_key=1;
->
-> INSERT INTO employee (first_name,city,id)
-> VALUES(in_first_name,in_city,in_id);
->
-> IF duplicate_key=1 THEN
-> SELECT CONCAT("Failed to insert ",in_first_name,
-> ": duplicate key") as "Result";
-> ELSE
-> SELECT CONCAT("Employee ",in_first_name," created") as "Result";
-> END IF;
-> END$$
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql> call myProc("new FirstName","new City",999);
+--------------------------------+
| Result |
+--------------------------------+
| Employee new FirstName created |
+--------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> select * from Employee;
+------+---------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+---------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
| 999 | new FirstName | NULL | NULL | NULL | NULL | new City | NULL |
+------+---------------+-----------+------------+------------+---------+-----------+-------------+
9 rows in set (0.00 sec)
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql>
Using while to check the condition
mysql>
mysql>
mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date DATE,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.01 sec)
mysql>
mysql>
mysql>
mysql> delimiter $$
mysql> CREATE PROCEDURE myProc (in_customer_id INT)
-> BEGIN
->
-> DECLARE l_first_name VARCHAR(30);
-> DECLARE l_id INT;
-> DECLARE l_city VARCHAR(30);
-> DECLARE l_department_count INT;
-> DECLARE no_more_departments INT;
->
-> DECLARE dept_csr CURSOR FOR
-> SELECT id,first_name, city
-> FROM employee;
->
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;
->
-> SET no_more_departments=0;
-> OPEN dept_csr;
-> dept_loop:WHILE(no_more_departments=0) DO
-> FETCH dept_csr INTO l_id,l_first_name,l_city;
-> IF no_more_departments=1 THEN
-> LEAVE dept_loop;
-> END IF;
-> SET l_department_count=l_department_count+1;
-> select l_id,l_first_name,l_city;
-> END WHILE dept_loop;
-> CLOSE dept_csr;
-> SET no_more_departments=0;
->
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql>
mysql> call myProc(1);
+------+--------------+---------+
| l_id | l_first_name | l_city |
+------+--------------+---------+
| 1 | Jason | Toronto |
+------+--------------+---------+
1 row in set (0.02 sec)
+------+--------------+-----------+
| l_id | l_first_name | l_city |
+------+--------------+-----------+
| 2 | Alison | Vancouver |
+------+--------------+-----------+
1 row in set (0.02 sec)
+------+--------------+-----------+
| l_id | l_first_name | l_city |
+------+--------------+-----------+
| 3 | James | Vancouver |
+------+--------------+-----------+
1 row in set (0.02 sec)
+------+--------------+-----------+
| l_id | l_first_name | l_city |
+------+--------------+-----------+
| 4 | Celia | Vancouver |
+------+--------------+-----------+
1 row in set (0.02 sec)
+------+--------------+-----------+
| l_id | l_first_name | l_city |
+------+--------------+-----------+
| 5 | Robert | Vancouver |
+------+--------------+-----------+
1 row in set (0.02 sec)
+------+--------------+----------+
| l_id | l_first_name | l_city |
+------+--------------+----------+
| 6 | Linda | New York |
+------+--------------+----------+
1 row in set (0.02 sec)
+------+--------------+----------+
| l_id | l_first_name | l_city |
+------+--------------+----------+
| 7 | David | New York |
+------+--------------+----------+
1 row in set (0.02 sec)
+------+--------------+-----------+
| l_id | l_first_name | l_city |
+------+--------------+-----------+
| 8 | James | Vancouver |
+------+--------------+-----------+
1 row in set (0.42 sec)
Query OK, 0 rows affected (0.42 sec)
mysql>
mysql>
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
mysql>