MySQL Tutorial/Procedure Function/Condition HANDLER

Материал из SQL эксперт
Версия от 09:53, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

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>
Источник — «http://sqle.ru/index.php?title=MySQL_Tutorial/Procedure_Function/Condition_HANDLER&oldid=755»