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

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

Хранимые процедуры InterBase могут быть рекурсивными. Это означает, что из хранимой процедуры можно вызвать саму себя. Допускается до 1000 уровней вложенности хранимых процедур, однако надо помнить о том, что свободные ресурсы на сервере могут закончиться раньше, чем будет достигнута максимальная вложенность ХП.

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

Давайте рассмотрим пример хранимой процедуры, которая выбирает все товары определенного типа, начиная с определенного уровня вложенности.

Пусть у нас есть следующая постановка задачи: имеем справочник товаров с иерархической структурой такого вида:

-Товары

- Бытовая техника

- Холодильники

- Трехкамерные

- Двухкамерные

- Однокамерные

- Стиральные машины

- Вертикальные

- Фронтальные

- Классические

- Узкие

- Компьютерная техника

....

Эта структура справочника категорий товаров может иметь ветки различной глубины. а также нарастать со временем. Наша задача - обеспечить выборку всех конечных элементов из справочника с "разворачивание полного имени", начиная с любого узла. Например, если мы выбираем узел "Стиральные машины", то нам надо получить следующие категории:

Стиральные машины - Вертикальные

Стиральные машины - Фронтальные Классические

Стиральные машины - Фронтальные Узкие

Определим структуру таблиц для хранения информации справочника товаров. Используем упрощенную схему для организации дерева в одной таблице:

CREATE TABLE GoodsTree

(ID_GOOD INTEGER NOT NULL,

ID_PARENT_GOOD INTEGER,

GOOD_NAME VARCHAR(80),

constraint pkGooci primary key (ID_GOOD) );

Создаем одну таблицу GoodsTree, в которой всего 3 поля: ID_GOOD - уникальный идентификатор категории, ID_PARENT_GOOD - идентификатор категории - родителя для данной категории и GOOD_NAME - наименование категории. Чтобы обеспечить целостность данных в этой таблице, наложим на эту таблицу ограничение внешнего ключа:

ALTER TABLE GoodsTree

ADD CONSTRAINT FK_goodstree

FOREIGN KEY (ID_PARENT_GOOD)

REFERENCES GOODSTPEE (ID__GOOD)

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

Давайте занесем в нашу таблицу следующие данные:

ID_GOOD

1

2

3

4

5

6

7

8

9

10

11

12

ID_PARENT_GOOD

0

1

1

2

2

4

4

4

5

5

10

10

GOOD_NAME

GOODS

Бытовая техника

Компьютеры и комплектующие

Холодильники

Стиральные машины

Трехкамерные

Двухкамерные

Однокамерные

Вертикальные

Фронтальные

Узкие

Классические

Теперь, когда у нас есть место для хранения данных, мы можем приступить к созданию хранимой процедуры, выполняющей вывод всех "окончательных" категорий товаров в "развернутом" виде - например, для категории "Трехкамерные" полное имя категории будет выглядеть как "Бытовая техника Холодильники Трехкамерные".

В хранимых процедурах, обрабатывающих древообразные структуры, сложилась своя терминология. Каждый элемент дерева называются узлом; а отношения между ссылающимися друг на друга узлами называется отношениями родитель-потомок. Узлы, находящиеся на самом конце дерева и не имеющие потомков, называются "листьями".

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

CREATE PROCEDURE GETFULLNAME (ID_GOOD2SHOW INTEGER)

RETURNS (FULL_GOODS_NAME VARCHAR(1000),

ID_CHILD_GOOD INTEGER)

AS

DECLARE VARIABLE CURR_CHILD_NAME VARCHAR(80);

BEGIN

/*0рганизуем внешний цикл FOR SELECT по непосредственным потомкам товара с ID_GOOD=ID_GOOD2SHOW */

FOR SELECT gtl.id_good, gtl.good_name

FROM GoodsTree gtl

WHERE gtl.id_parent_good=:ID_good2show

INTO:ID_CHILD_GOOD, :full_goods_name

DO

BEGIN

/"Проверка с помощью функции EXISTS, которая возвращает TRUE, если запрос в скобках вернет хотя бы одну строку. Если у найденного узла с ID_PARENT_GOOD = ID_CHILD_GOOD нет потомков, то он является "листом" дерева и попадает в результаты */

IF (NOT EXISTS(

SELECT * FROM GoodsTree

WHERE GoodsTree.id_parent_good=:id_child_good))

THEN

BEGIN

/* Передаем "лист" дерева в результаты */

SUSPEND;

END

ELSE

/* Для узлов, у которых есть потомки*/

BEGIN

/*сохраняем имя узла-родителя во временной переменной */

CURR_CHILD_NAME=full_goods_name;

/* рекурсивно запускаем эту процедуру */

FOR

SELECT ID_CHILD_GOOD, full_goods_name

FROM GETFULLNAME (:ID_CHILD_GOOD)

INTO:ID_CHILD_GOOD, :full_goods_name

DO BEGIN

/*добавляем имя узла-родителя к найденном., имени потомка с помощью операции конкатенации строк || */

full_goods_name=CURR_CHILD_NAME| | full_goods_name,-

SUSPEND; /* возвращаем полное имя товара*/

END

END

END

END

Если мы выполним данную процедуру с входным параметром ID_GOOD2SHOW= 1, то получим следующее:

FULL_GOODS__NAME

Бытовая техника Холодильники Трехкамерные

Бытовая техника Холодильники Двухкамерные

Бытовая техника Холодильники Однокамерные

Бытовая техника Стиральные машины Вертикальные

Бытовая техника Стиральные машины Фронтальные Узкие

Бытовая техника Стиральные машины Фронтальные Классические

Компьютеры и комплектующие

ID_CHILD_GOOD

6

7

8

9

11

12

3

Как видите, с помощью рекурсивной хранимой процедуры мы прошлись по всему дереву категорий и вывели полное наименование категорий - "листьев", которые находятся на самых кончиках ветвей.