Давайте рассмотрим пример хранимой процедуры, которая выбирает все товары определенного типа, начиная с определенного уровня вложенности.
Пусть у нас есть следующая постановка задачи: имеем справочник товаров с иерархической структурой такого вида:
-Товары
- Бытовая техника
- Холодильники
- Трехкамерные
- Двухкамерные
- Однокамерные
- Стиральные машины
- Вертикальные
- Фронтальные
- Классические
- Узкие
- Компьютерная техника
....
Эта структура справочника категорий товаров может иметь ветки различной глубины. а также нарастать со временем. Наша задача - обеспечить выборку всех конечных элементов из справочника с "разворачивание полного имени", начиная с любого узла. Например, если мы выбираем узел "Стиральные машины", то нам надо получить следующие категории:
Стиральные машины - Вертикальные
Стиральные машины - Фронтальные Классические
Стиральные машины - Фронтальные Узкие
Определим структуру таблиц для хранения информации справочника товаров. Используем упрощенную схему для организации дерева в одной таблице:
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 */