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

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

Пусть имеется каталог бесконечной степени вложенности, хранящийся в таблице 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.

Дата добавления: 2015-05-09