В этой статье мы расскажем вам, как использовать обработчик MySQL для обработки исключений или ошибок, возникающих в хранимых процедурах.

При возникновении ошибки внутри хранимой процедуры, важно исправить ее соответствующим образом, например, продолжая или останавливая исполнение операции и выдавая сообщение об ошибке.

MySQL предоставляет простой способ определить обработчики, которые обрабатывают ошибки, исходя из общих условий, таких как предупреждения или исключения из условий, например, конкретные коды ошибок.

Объявление обработчика

Чтобы объявить обработчик мы используем оператор DECLARE HANDLER:

DECLARE action HANDLER FOR condition_value statement;

Если значение условия совпадает со значением condition_value, MySQL выполнит операторstatement и продолжит или завершит текущий блок кода, исходя из значения action.

action может принимать следующие значения:

• CONTINUE: исполнение блокированного кода (BEGIN … END) продолжается;
• EXIT: выполнение блокированного кода, в котором был объявлен обработчик, завершается.

condition_value задает конкретное условие или класс условия, которые активируют обработчик.

condition_value может принимать одно из следующих значений:

• код ошибки MySQL;
• стандартное значение SQLSTATE. Или это может быть условие SQLWARNING, NOTFOUND илиSQLEXCEPTION, которое является сокращением для класса значений SQLSTATE. УсловиеNOTFOUND используется для курсора или оператора SELECT INTO variable_list;
• название условия, связанного либо с кодом ошибки MySQL, либо со значением SQLSTATE.

В качестве statement может использоваться простой оператор или составной оператор, вшитый с помощью ключевых слов BEGIN и END.

Примеры обработки ошибок MySQL

Давайте рассмотрим несколько примеров объявления обработчиков.

Обработчик, приведенный ниже, означает: когда происходит ошибка, устанавливается значение переменной has_error 1 и выполнение продолжается:

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1;

Ниже приводится другой обработчик, который означает, что в случае возникновении любой ошибки, производится откат предыдущей операции, выдается сообщение об ошибке и осуществляется выход из текущего блока кода.

Если вы объявляете его внутри блока BEGIN END хранимой процедуры, он немедленно завершает хранимую процедуру:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated';
END;

Если строк для вывода больше нет, для вариантов cursor или оператора SELECT INTO, значение переменной no_row_found устанавливается равным 1 и продолжается исполнение:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_row_found = 1;

При возникновении ошибки дублирования ключа, выдается ошибка MySQL 1062. Следующий обработчик выдает сообщение об ошибке и продолжает выполнение:

DECLARE CONTINUE HANDLER FOR 1062
SELECT 'Error, duplicate key occurred';

Пример обработчика MySQL в хранимых процедурах

Во-первых, для демонстрации мы создаем новую таблицу с именем article_tags:

CREATE TABLE article_tags(
 article_id INT,
 tag_id INT,
 PRIMARY KEY(article_id,tag_id)
);

В таблице article_tags хранятся связи между статьями и тегами. К каждой статье может относиться несколько тегов и наоборот.

Для простоты, мы не будем создавать таблицы articles и tags, а также внешние ключи в таблицеarticle_tags.

Во-вторых, мы создаем хранимую процедуру, которая вставляет пару идентификаторов статьи и тега в таблицу article_tags:
 

DELIMITER $$
 
CREATE PROCEDURE insert_article_tags(IN article_id INT, IN tag_id INT)
BEGIN
 
 DECLARE CONTINUE HANDLER FOR 1062
 SELECT CONCAT('duplicate keys (',article_id,',',tag_id,') found') AS msg;
 
 -- insert a new record into article_tags
 INSERT INTO article_tags(article_id,tag_id)
 VALUES(article_id,tag_id);
 
 -- return tag count for the article
 SELECT COUNT(*) FROM article_tags;
END

В-третьих, для статьи 1 мы добавляем идентификаторы тега 1, 2 и 3, с помощью вызова хранимой процедуры insert_article_tags:

CALL insert_article_tags(1,1);
CALL insert_article_tags(1,2);
CALL insert_article_tags(1,3);

Четвертое. Давайте попробуем вставить дубликат ключа, чтобы увидеть, действительно ли вызывается обработчик:

CALL insert_article_tags(1,3);

Мы получили сообщение об ошибке. Однако, поскольку мы объявили тип обработчика CONTINUE, хранимая процедура продолжает исполняться.

В результате, мы все равно получили список тегов для статьи:

Если мы в объявлении обработчика изменим команду CONTINUE на EXIT, мы получим только сообщение об ошибке:

DELIMITER $$
 
CREATE PROCEDURE insert_article_tags_2(IN article_id INT, IN tag_id INT)
BEGIN
 
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 SELECT 'SQLException invoked';
 
 DECLARE EXIT HANDLER FOR 1062 
 SELECT 'MySQL error code 1062 invoked';
 
 DECLARE EXIT HANDLER FOR SQLSTATE '23000'
 SELECT 'SQLSTATE 23000 invoked';
 
 -- insert a new record into article_tags
 INSERT INTO article_tags(article_id,tag_id)
 VALUES(article_id,tag_id);
 
 -- return tag count for the article
 SELECT COUNT(*) FROM article_tags;
END

Теперь, мы можем попробовать добавить дубликат ключа, чтобы увидеть результат:

CALL insert_article_tags_2(1,3);

Приоритет обработчиков MySQL

В случае если у вас есть несколько обработчиков, которые имеют право обрабатывать ошибку,MySQL для обработки ошибки будет вызывать наиболее подходящий обработчик.

Ошибка всегда обозначается одним из кодов ошибки MySQL, так что MySQL в этом плане имеет возможность четко их идентифицировать.

Обозначения SQLSTATE для многих кодов ошибок MySQL менее специфичны. SQLEXCPETION илиSQLWARNING представляют собой сокращения класса значений SQLSTATES, поэтому они имеют общий характер.

На основании правил приоритета обработчиков обработчик кода ошибки MySQL, обработчикSQLSTATE и обработчик SQLEXCEPTION имеют приоритеты один, два и три соответственно.

Предположим, что в хранимой процедуре insert_article_tags_3 мы объявляем три обработчика:

DELIMITER $$
 
CREATE PROCEDURE insert_article_tags_3(IN article_id INT, IN tag_id INT)
BEGIN
 
 DECLARE EXIT HANDLER FOR 1062 SELECT 'Duplicate keys error encountered';
 DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLException encountered';
 DECLARE EXIT HANDLER FOR SQLSTATE '23000' SELECT 'SQLSTATE 23000';
 
 -- insert a new record into article_tags
 INSERT INTO article_tags(article_id,tag_id)
 VALUES(article_id,tag_id);
 
 -- return tag count for the article
 SELECT COUNT(*) FROM article_tags;
END

Теперь мы пробуем добавить в таблицу article_tags дубликат ключа через вызов хранимой процедуры:

CALL insert_article_tags_3(1,3);

Как видите, вызывается обработчик кода ошибки MySQL:

Использование проименованных условий ошибки

Начинаем с объявления обработчика ошибки:

DECLARE EXIT HANDLER FOR 1051 SELECT 'Please create table abc first';
SELECT * FROM abc;

Что означает код 1051? Представьте, что у вас есть большая хранимая процедура, по всему коду которой разбросаны некорректные значения. Настоящий кошмар для разработчиков обслуживания.

К счастью, MySQL предоставляет нам оператор DECLARE CONDITION, который объявляет проименованное условие ошибки, связанное с условием.

Синтаксис оператора DECLARE CONDITION выглядит следующим образом:

DECLARE condition_name CONDITION FOR condition_value;

condition_value может представлять собой код ошибки MySQL, например 1015, или значениеSQLSTATE. condition_value представляется с помощью condition_name.

После объявления вы можете обращаться к condition_name вместо condition_value.

Таким образом, мы можем переписать код, приведенный выше, следующим образом:
 

DECLARE table_not_found CONDITION for 1051;
DECLARE EXIT HANDLER FOR table_not_found SELECT 'Please create table abc first';
SELECT * FROM abc;

Этот код, очевидно, более удобен для чтения, нежели предыдущий. Отметим, что объявление условия должно размещаться перед объявлением обработчика или объявлением курсора.