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

Обработчик DECLARE HANDLER задает реакцию хранимой процедуры MySQL на ошибку. CONTINUE позволяет продолжить выполнение, EXIT завершает блок с объявленным обработчиком. Примеры с дубликатом ключа показывают разницу этих действий. Именованные условия помогают заменить числовые коды понятными названиями.

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

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

DECLARE action HANDLER FOR condition_value statement;

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

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

Параметр condition_value принимает код, SQLSTATE, класс или имя условия:

• Код ошибки MySQL. Например, 1062 обозначает дубликат ключа.
• Значение SQLSTATE. Например, SQLSTATE '23000' обозначает нарушение ограничений целостности.
• Класс условий. MySQL распознает SQLWARNING, NOT FOUND и SQLEXCEPTION.
• Имя условия. Оно связано с кодом ошибки или значением SQLSTATE.

SQLWARNING охватывает SQLSTATE, начинающиеся с 01. Класс NOT FOUND охватывает значения с началом 02. Он срабатывает при исчерпании курсора или отсутствии строк в SELECT … INTO.

Класс SQLEXCEPTION охватывает остальные SQLSTATE. Значения с началом 00, 01 и 02 в него не входят.

Вместо statement запишите один оператор или блок BEGIN … END.

Размещайте объявления в начале блока, перед исполняемыми операторами. Сначала объявляйте переменные и условия, затем курсоры, после них обработчики. Синтаксис описывает документация MySQL по DECLARE HANDLER.

Как обрабатывать ошибки MySQL

Обработчик может установить флаг, вернуть сообщение или вызвать ROLLBACK. Выбирайте действие по тому, допустимо ли продолжение после ошибки.

Этот обработчик устанавливает has_error = 1 при исключении. После этого процедура продолжает выполнение:

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1;

Объявите переменную перед обработчиком: DECLARE has_error INT DEFAULT 0;.

Обработчик с EXIT может откатить активную транзакцию и вернуть сообщение:

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

ROLLBACK отменяет незафиксированные изменения транзакционных таблиц, например InnoDB. Он не отменяет уже зафиксированные изменения. Этот фрагмент предполагает отдельный запуск транзакции через START TRANSACTION.

Обработчик завершит процедуру, если объявлен в ее внешнем блоке. Во вложенном блоке EXIT завершит только этот блок. Само действие EXIT не выполняет откат автоматически.

Обработчик NOT FOUND помогает отметить отсутствие следующей строки:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_row_found = 1;

Предварительно объявите флаг: DECLARE no_row_found INT DEFAULT 0;. При чтении курсора проверяйте этот флаг после FETCH.

Дубликат первичного или уникального ключа вызывает ошибку 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:

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 the total number of links
    SELECT COUNT(*) FROM article_tags;
END$$

DELIMITER ;

Команда DELIMITER меняет разделитель команд в консольном клиенте mysql. Завершение END$$ отправляет определение процедуры целиком. Команда DELIMITER ; возвращает обычный разделитель для последующих вызовов.

Добавьте статье 1 теги с идентификаторами 1, 2 и 3:

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

После вызовов таблица содержит 3 связи. Каждый вызов возвращает общее количество строк через COUNT(*).

Повторите вставку пары (1,3), чтобы вызвать ошибку дубликата ключа:

CALL insert_article_tags(1,3);

Обработчик вернет сообщение duplicate keys (1,3) found. Действие CONTINUE позволит выполнить следующий оператор SELECT COUNT(*). Количество связей останется равным 3: дубликат не добавится.

Создайте второй вариант процедуры с обработчиками 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 INTO article_tags(article_id,tag_id)
    VALUES(article_id,tag_id);

    SELECT COUNT(*) FROM article_tags;
END$$

DELIMITER ;

Вызовите процедуру с уже существующей парой (1,3):

CALL insert_article_tags_2(1,3);

MySQL выберет только обработчик кода ошибки 1062. Он вернет сообщение MySQL error code 1062 invoked. В одном блоке код ошибки приоритетнее SQLSTATE и класса SQLEXCEPTION. Порядок этих трех объявлений не меняет выбор.

Действие EXIT завершит внешний блок процедуры после сообщения. Оператор SELECT COUNT(*) уже не выполнится.

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

Для обработчиков одного блока MySQL применяет следующий порядок приоритетов:

1. Конкретный код ошибки MySQL, например 1062.
2. Значение SQLSTATE, например '23000'.
3. Класс условий, например SQLEXCEPTION.

Код 1062 точно обозначает дубликат ключа. Значение SQLSTATE '23000' охватывает разные нарушения ограничений целостности. Класс 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 INTO article_tags(article_id,tag_id)
    VALUES(article_id,tag_id);

    SELECT COUNT(*) FROM article_tags;
END$$

DELIMITER ;

Добавьте дубликат через вызов процедуры:

CALL insert_article_tags_3(1,3);

MySQL снова выберет обработчик 1062. Вызов вернет сообщение Duplicate keys error encountered. Подсчет строк после вставки не выполнится из-за действия EXIT.

Во вложенных блоках MySQL начинает поиск обработчика с ближайшего блока. Подходящий внутренний обработчик имеет преимущество перед внешним. Сравнивайте специфичность условий уже внутри выбранного блока.

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

Числовой код не всегда объясняет назначение обработчика. Например, отсутствие таблицы при SELECT вызывает ошибку 1146:

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

Оператор DECLARE CONDITION связывает понятное имя с условием ошибки:

DECLARE condition_name CONDITION FOR condition_value;

Параметр condition_value задает код MySQL или значение SQLSTATE. После объявления используйте condition_name вместо числового кода.

Для отсутствующей таблицы фрагмент с именованным условием выглядит так:

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

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

Имя table_not_found объясняет назначение обработчика прямо в коде. Оно не меняет приоритет или поведение связанного условия.

Совет от нашей редакции: проверяйте обработчик повторным вызовом с дубликатом ключа. Сравните сообщение и выполнение следующего оператора. Так видна разница между CONTINUE и EXIT.