1.14. Рекурсивные хранимые процедуры

Хранимую процедуру, которая вызывает сама себя, называют рекурсивной хранимой процедурой. Основная область применения рекурсивных хранимых процедур – деревья. Данные, которые имеют древовидную структуру, проще всего обрабатывать именно при помощи рекурсивных процедур.

Пусть имеется каталог бесконечной степени вложенности, хранящийся в таблице catalogs (см. пример ниже).

Таблица catalogs состоит из трех полей:

id_catalog – первичный ключ таблицы, снабженный атрибутом auto_increment;

name – название элемента каталога;

id_parent – вторичный ключ, указывающий на первичный ключ родительской записи, для корневых каталогов значение данного поля равно 0.

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

Компьютеры

Материнские платы

Процессоры

Intel

AMD

Видеокарты

NVidia

ATI

Оперативная память

Жесткие диски

Оргтехника

Принтеры

Сканеры

Копировальные аппараты

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

Замечание! СУБД MySQL не поддерживает рекурсивных функций.

Однако вызов процедуры delcat() на неподготовленной машине, скорее всего, закончится неудачно с выводом сообщения об ошибке: “ERROR 1456 (HY000): Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine delcat” (“Количество рекурсивных спусков функции delcat ограничено нулем, установите системную переменную max_sp_recursion_depth”). Значение системной переменной max_sp_recursion_depth по умолчанию равно 0, поэтому, чтобы функция могла осуществлять рекурсивный спуск, необходимо установить значение этой переменной, отличной от нуля (максимум 255). Так как максимальная глубина каталога не превышает 4, установим значение переменной, равное 4 (см. пример ниже).

Теперь можно воспользоваться процедурой delcat(). В приведенном ниже примере удаляется элемент каталога “Компьютеры” со всеми подчиненными элементами, для этого в качестве аргумента функции передается первичный ключ каталога – 1.

2. Триггеры

Триггер – это специальная хранимая процедура, привязанная к событию изменения содержимого таблицы. Существуют три события изменения таблицы, к которым можно привязать триггер: это изменение содержимого при помощи операторов insert, delete и update.

Замечание! Поддержка триггеров введена в СУБД MySQL, начиная с версии 5.0.2.

2.1. Создание триггера

Создание триггера осуществляется при помощи оператора create trigger, который имеет следующий синтаксис:

CREATE TRIGGER trigger_name trigger_time trigger_event

ON tbl_name FOR EACH ROW trigger_stmt

Оператор create trigger создает trigger_name, привязанный к таблице tbl_name. Таблица должна существовать физически, т. е. не допускается привязка триггера к временной таблице или представлению.

Конструкция trigger_time указывает момент выполнения триггера и может принимать два значения:

• before – действия триггера производятся до выполнения операции изменения таблицы;

• after - действия триггера производятся после выполнения операции изменения таблицы.

Конструкция trigger_event показывает, на какое из событий должен реагировать триггер, и может принимать три значения:

• insert – триггер привязан к событию вставки новой записи в таблицу;

• update – триггер привязан к событию обновления записи таблицы;

• delete – триггер привязан к событию удаления записей таблицы.

Замечание! Для таблицы tbl_name может быть создан только одни триггер для каждого из событий trigger_event и момента trigger_time. No есть для каждой из таблиц может быть создано всего шесть триггеров.

Конструкция trigger_stmt представляет тело триггера, т. е. оператора, который необходимо выполнить при возникновении события trigger_event в таблице tbl_name. Если требуется выполнить несколько операторов, то следует прибегать к составному оператору begin…end, в котором размещаются все требуемые запросы.

Синтаксис и допустимые операторы совпадают с тело хранимых процедур. Внутри составного оператора begin…end допускаются все специфичные для хранимых процедур операторы и конструкции:

• другие составные операторы begin…end;

• операторы управления потоком (if, case, while, loop, repeat, leave, iterate);

• объявление локальных переменных при помощи оператора declare и присвоение им значений при помощи оператора set;

• именованные условия и обработчики ошибок.

Замечание! В СУБД MySQL триггеры нельзя привязывать к каскадному обновлению и удалению записей из таблицы типа InnoDB по связи “первичный ключ/внешний ключ”.

Замечание! Для создания триггера при помощи оператора create trigger до версии MySQL 5.1.6 требуется наличие привилегии super, после версии 5.1.6 требуется специальная привилегия trigger.

Триггеры очень сложно использовать, не имея доступа к новым записям, которые вставляются в таблицу, или старым записям, которые обновляются или удаляются. Для доступа к новым и старым записям используются префиксы new и old соответственно. То есть если в таблице обновляется поле total, то получить доступ к старому значению можно по имени old.total, а к новому – new.total.

Создадим простейший триггер, который при оформлении нового заказа (добавление новой записи в таблицу catalogs) будет присваивать значение 1 пользовательской переменной @tot (см. пример ниже).

Как видно из приведенного выше примера, в результате добавления новой записи в таблицу catalogs пользовательской переменной присваивается значение 1. Отредактируем триггер sub_count таким образом, чтобы к пользовательской переменной @tot прибавлялось каждый раз число заказанных товарных позиций number (см. пример ниже).

Замечание! Для корректной работы триггера необходимо, чтобы пользовательская переменная @tot имела значение, отличное от null, т. к. операции сложения с null приводят к null.

Замечание! При создании триггера для таблицы в каталоге данных формируется файл, название которого совпадает с именем таблицы. Файл является текстовым и имеет расширение trg.

Предыдущие два примера демонстрировали работу триггеров после добавления записи в таблицу (after) без вмешательства в запрос. Рассмотрим триггер, который будет включаться до (before) вставки новых записей в таблицу catalogs. Основная задача триггера заключается в ограничении числа заказанных товаров.

Как видно из предыдущего примера, несмотря на то, что заказ оформляется на две товарные позиции, триггер restrict_count изменил значение поля number на 1.

Часто при обновлении одних полей таблицы операторы баз данных забывают обновить связанные поля таблицы или производится попытка добавления некорректных значений. Пусть при добавлении нового покупателя необходимо преобразовать имена и отчества покупателей в инициалы.

Для начала создадим таблицу users, а затем триггер restrict_user для описанной выше задачи.

Как видно из примера, имя и отчество любого нового посетителя урезается до одной буквы. Для того чтобы имя и отчество не были отредактированы при помощи оператора update, следует также создать триггер, привязанный к событию update.