Правильная ссылка на эту страницу
http://az-design.ru/Support/DataBase/DBTree2/2A00.shtml

Хранимые процедуры в БД для работы древовидными структурами

Архангельский Андрей

       Для полноценной работы с древовидными структурами необходимо иметь ряд хранимых процедур:
       1) Поиск всех потомков, включая вложенные, для данного узла
       2) Вычисление уровня текущего узла от корня дерева
       3) Поиск всех родителей для данного узла
       Для поиска всех потомков, одновременно вычисляя уровень узла, можно применить следующую хранимую процедуру:

create procedure PeopleGetChild (Prnt Integer, bLev SmallInt)
                  returns (Chld Integer, rLev SmallInt) as
begin
01 for select PID from People where  Parent=:Prnt into :Chld
02   do begin
03      rLev = bLev + 1;
04      suspend;
05      for select Chld,rLev from PeopleGetChild(:Chld,:bLev) into :Chld,:rLev
06          do begin
07             suspend;
08          end
09   end
end

       Первый запрос выбирает все идентификаторы строк, у которых родитель (Parent) равен значению переданному в процедуру (Prnt). Результат записывается в переменную Chld и одновременно увеличивается счетчик уровней (rLev).
       На языке Interbase SUSPEND означает, что возвращаемая по Returns информация должна заноситься в результирующий набор в виде очередной записи. Первый оператор SUSPEND (строка 04) возвращает значения первой записи запроса, определяющего непосредственных потомков Prnt. Следующий SUSPEND (строка 07) возвращает результат рекурсивного вызова процедуры выбора GetChild. До тех пор пока этот второй вызов находит записи (то есть до тех пор, пока у объекта находяться потомки), второй SUSPEND возвращает их исходной вызывающей процедуре. Когда объекты кончаются, вызывающий код продолжает свою работу и с помощью первого SUSPEND возвращает вторую запись исходного запроса.
       Подобные итерации идеально подходят для просмотра иерархических данных в обоих направлениях, потому что хранимые процедуры рекурсивны. Такую процедуру можно вызывать из нее самой, чтобы определить детей текущего объекта, затем получить их детей и т.д. Вместо того, чтобы сразу получить все записи одного поколения и переходить к следующему, при этой стратегии мы сначала определяем первого потомка объекта, затем — его первого потомка (т.е. первого внука исходного объекта) и т.д. до последнего потомка.
       Для вызова процедуры выборки Interbase следует пользоваться компонентом TQuery, а не TStoredProc. Синтаксис выглядит просто:

Query.Close;   Query.SQL.Clear;
Query.SQL.Add('Select * from PeopleGetChild('+IntToStr(CurrentNodeID)+',0)');
Query.Open;

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

Create table PeopleLev (
       PeopleID   AZInt32 not null references PEOPLE on update cascade 
                                                     on delete cascade,
       PIDParnt   AZInt32 not null references PEOPLE on update cascade 
                                                     on delete cascade,
       PGenNo     AZInt32  not null,
       PIDLevel   AZInt32,
Primary key (PeopleID,PGenNo));
Commit;

       И запрос несколько меняется:

Query.Close;   Query.SQL.Clear;
Query.SQL.Add('Insert into PeopleLev(PeopleID,PIDLevel)');
Query.SQL.Add('Select * from PeopleGetChild('+IntToStr(CurrentNodeID)+',0)');
Query.ExecSQL;

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

Query.Close;   Query.SQL.Clear;
Query.SQL.Add('Select * from People p, PeopleLev l');
Query.SQL.Add(' where p.PeopleID=l.PeopleID');
Query.SQL.Add(' order by l.PIDLevel');
Query.Open;

       Для практической работы часто требуется наряду с ID узла, иметь также и родителя этого узла. Для этого можно немного изменить процедуру:

create procedure PeopleGetChild2 (Prnt Integer, bLev SmallInt)
                 returns (rPnt Integer, Chld Integer, rLev SmallInt) as
begin
  for select PID from People where PID<>0 and Parent=:Prnt into :Chld
      do begin
         rPnt = Prnt;
         rLev = bLev + 1;
         suspend;
         for select rPnt,Chld,rLev from PeopleGetChild2(:Chld,:rLev)
                                   into :rPnt,:Chld,:rLev
             do begin
                suspend;
             end
      end
end!!

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

create procedure PeopleGetParent (Chld Integer, bLev SmallInt)
                   returns (Prnt Integer, rLev SmallInt) as
begin
   for select Parent from People where PID=:Chld into :Prnt
     do begin 
        If (Prnt<>0) then begin
           rLev = bLev - 1;
           suspend;
           for select Prnt,rLev from PeopleGetParent(:Prnt,:rLev)
                                into :Prnt,:rLev
             do begin
                suspend;
                end
           end
        end
end

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

create procedure PeopleGetParent2 (Chld Integer, bLev SmallInt)
                   returns (Prnt Integer, rChld Integer, rLev SmallInt) as
begin
   for select Parent from People where PID=:Chld into :Prnt
     do begin
        If (Prnt<>0) then begin
           rChld = Chld;
           rLev = bLev - 1;
           suspend;
           for select Prnt,rChld,rLev from PeopleGetParent2(:Prnt,:rLev)
                                      into :Prnt,:rChld,:rLev
             do begin
                suspend;
                end
           end
        end
end!!

       Порядок выходных переменных, такой же как в процедуре PeopleGetChild2.

© 01.08.2009, Архангельский А.Г.

<<Пред. Оглавление
Начало раздела
След.>>




Дата последнего изменения:
Thursday, 21-Aug-2014 09:10:44 MSK


Постоянный адрес статьи:
http://az-design.ru/Support/DataBase/DBTree2/2A00.shtml