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
<source lang="sql">
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></source>
Checking flag
<source lang="sql">
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></source>
Condition and Handler in action
By declaring conditions and handlers, you can catch certain MySQL errors or SQLSTATE conditions.
<source lang="sql">
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></source>
DECLARE CONTINUE HANDLER FOR NOT FOUND SET
<source lang="sql">
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)</source>
DECLARE EXIT HANDLER FOR
<source lang="sql">
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)</source>
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:
<source lang="sql">
DECLARE <handler type> HANDLER FOR <condition> <statement>;</source>
The example associates a handler with SQLSTATE 23000, which occurs for a duplicate-key error
<source lang="sql">
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></source>
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.
<source lang="sql">
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></source>
Using the IF statement to verify "CONTINUE HANDLER"
<source lang="sql">
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></source>
Using while to check the condition
<source lang="sql">
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></source>
