Иерархический запрос

Функция

Определение иерархического запроса к таблице.

Спецификация
         
< иерархический запрос >::=
< начальный уровень >::=
< предыдущий уровень >::=
< последующий уровень >::=
Синтаксические правила
  1. Опция START WITH задает условия выбора начальной записи иерархической структуры данных.

  2. Опция CONNECT BY < условие связи > задает отношение между узлами иерархической структуры данных. Часть элементов < условия связи > должна быть использована в опции PRIOR для ссылки на предыдущий уровень иерархии.

  3. Опция PRIOR устанавливает предыдущий уровень текущей записи иерархии. PRIOR – унарный оператор и обрабатывается как обычные унарные + и - операторы. В неиерархических запросах при одной исходной таблице во всех < условиях связи > имена столбцов (не считая внешних ссылок) рассматриваются как ссылки к одной и той же записи исходной таблицы. Напротив, в иерархическом запросе < условие связи > должно содержать ссылки к двум записям – текущего и предыдущего уровня, которые оно и связывает между собой.

  4. В < условиях связи > < предыдущий уровень > и < последующий уровень > можно менять местами. < Условие связи > должно содержать ссылки к двум записям – текущего и предыдущего уровня, которые связывает между собой. Но так как в иерархическом запросе может участвовать только одна таблица, то различие между обращениями к записям текущего и предыдущего уровня делается следующим образом: если перед аргументом предиката стоит ключевое слово PRIOR, то все обращения к столбцам в этом аргументе предиката относятся к записи предыдущего уровня, если же PRIOR не стоит – то к записи текущего уровня.

    Эти конструкции эквивалентны:

    select *
      FROM "Служащие"
     START WITH "Должность" = 'РУКОВОДИТЕЛЬ'
    CONNECT BY PRIOR "Таб_Номер"="Таб_Номер_Руковод";
    select *
      FROM "Служащие"
     START WITH "Должность" = 'РУКОВОДИТЕЛЬ'
    CONNECT BY "Таб_Номер_Руковод" = PRIOR "Таб_Номер";
  5. < Предыдущий уровень > и < последующий уровень > должны ссылаться на < значимые выражения >, содержащие разные столбцы таблицы.

  6. Типы данных < предыдущего уровня > и < последующего уровня > должны быть совместимыми.

  7. Условия WHERE, START и CONNECT в иерархических запросах можно задавать в произвольном порядке.

  8. Столбец LEVEL внутри иерархического запроса используется для обозначения уровня иерархии, в остальных запросах – как идентификатор (имя столбца).

    create table tst(s int, f int);
    insert into tst values (1,2), (2,3);
    
    SELECT level, COUNT(*)
      FROM (SELECT level FROM TST START WITH s=1 CONNECT BY S=PRIOR f) a
     GROUP BY level;
    или
      WITH t1 as (SELECT level FROM TST START WITH s=1 CONNECT BY S=PRIOR f)
    SELECT level, COUNT(*)
      FROM t1 GROUP BY level;
    
     LEVEL
     -----
    |          1|          1|
    |          2|          1|

    Примечание

    Столбец LEVEL в опции CONNECT BY обрабатывается корректно только для простых условий типа LEVEL < значение > (для сложных условий результат может быть не верным).

  9. Опция SIBLINGS в < ORDER BY-спецификации > задаёт порядок сортировки записей внутри уровней иерархического запроса. (Без сортировки вывод записей выполняется в порядке обхода иерархического дерева, т.е. сверху-вниз, слева-направо).

    create or replace table test(name char(5), id int, parent int);
    insert into test values ('F', 1, NULL);
    insert into test values ('C', 2, 1);
    insert into test values ('H', 3, 2);
    insert into test values ('E', 4, 2);
    insert into test values ('B', 5, 1);
    insert into test values ('G', 6, 5);
    insert into test values ('D', 7, 6);
    insert into test values ('A', 8, 5);

    а) обычная сортировка

    select name, id , parent , level from test start with parent is null
    connect by prior id = parent order by name;
    
    NAME  ID          PARENT      LEVEL
    ----  --          ------      -----
    |A    |          8|          5|          3|
    |B    |          5|          1|          2|
    |C    |          2|          1|          2|
    |D    |          7|          6|          4|
    |E    |          4|          2|          3|
    |F    |          1|           |          1|
    |G    |          6|          5|          3|
    |H    |          3|          2|          3|

    б) иерархическая сортировка

    select name, id , parent , level from test start with parent is null
    connect by prior id = parent order siblings by name;
    
    NAME  ID          PARENT      LEVEL
     ----  --          ------      -----
    |F    |          1|           |          1|
    |B    |          5|          1|          2|
    |A    |          8|          5|          3|
    |G    |          6|          5|          3|
    |D    |          7|          6|          4|
    |C    |          2|          1|          2|
    |E    |          4|          2|          3|
    |H    |          3|          2|          3|
  10. Запрещается использовать одновременно < WHERE-спецификацию > и < ORDER BY-спецификацию > с опцией SIBLINGS. Т.е. запрос вида

    select * from test start with parent is null
    connect by prior id = parent where id <  > 1 order siblings by name;

    является синтаксически неправильным.

  11. Псевдостолбцы ROWID и ROWTIME в иерархических запросах запрещены. Например, нижеследующие запросы синтаксически некорректны:

    select * from TEST start with PARENT is null connect by prior ID = PARENT ORDER by ROWID;
    
    select * from TEST start with PARENT is null connect by prior ID = PARENT ORDER siblings by ROWID;
  12. Опция GROUP BY в иерархических запросах разрешена, например, нижеследующий запрос:

    select COUNT(*) COUNT,LEVEL from test start with PARENT is null connect by prior ID = PARENT GROUP BY LEVEL;
    
     COUNT LEVEL
     -----------
    |    1|    1|
    |    2|    2|
    |    4|    3|
    |    1|    4|
Общие правила
  1. Результирующая таблица по умолчанию имеет тот же список столбцов, что и исходная, с добавлением псевдостолбца с именем «LEVEL» типа INTEGER, который указывает номер уровня записи в иерархии.

  2. Наличие псевдостолбца с именем «LEVEL» в выборке вида SELECT * FROM… зависит от ключа /COMPATIBILITY=ORACLE в команде запуска ядра СУБД:

    • псевдостолбец будет присутствовать, если ключ не задан;

    • псевдостолбец будет отсутствовать, если ключ задан.

  3. Псевдостолбец LEVEL можно указывать в < основном запросе выборки > и < WHERE-спецификации > и нельзя в конструкциях START WITH и CONNECT BY.

  4. Сортировка записей в ответе (если не задана < GROUP BY-спецификация >) выполняется в таком порядке, чтобы все записи, подчиненные в иерархии некоторой записи, следовали за ней.

  5. Условие, содержащееся в конструкции START, определяет записи верхнего уровня в иерархии.

  6. Условие, содержащееся в конструкции CONNECT, определяет связи между записями предыдущего и последующего уровней.

  7. < Иерархический запрос > выполняется столько раз, сколько < начальных уровней > найдено в таблице, задаваемой < FROM-спецификацией >.

  8. Максимальное количество уровней для сортируемых иерархических запросов равно 4096.

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

  10. Для запросов с CONNECT BY можно подавать команды позиционного обновления и удаления (WHERE CURRENT OF).

Примеры
create or replace table tab1 (id int, "Фамилия" char(15), "Имя" char(10), "Год рождения" int, "Статус" char(10), "Поколение" int, "Отношение" int);
insert into tab1 values(10,'Петрова', 'Мария', 1989, 'дочь', 3,5);
insert into tab1 values(9,'Петров', 'Сергей', 1989, 'сын', 3, 5) ;
insert into tab1 values(8,'Иванов', 'Сергей', 1989, 'сын', 3,3 );
insert into tab1 values(7,'Иванов', 'Михаил', 1994, 'сын', 3,4 );
insert into tab1 values(6,'Иванов', 'Иван', 1972, 'сын', 2, 1);
insert into tab1 values(5,'Петрова', 'Анна', 1970, 'дочь', 2, 1);
insert into tab1 values(4,'Иванов', 'Андрей', 1974, 'сын', 2, 1);
insert into tab1 values(3,'Иванов', 'Сергей', 1968, 'сын', 2,1 );
insert into tab1 values(2,'Иванова', 'Мария', 1950, 'бабушка', 1,0);
insert into tab1 values(1,'Иванов', 'Иван', 1948, 'дедушка', 1,0);

Сколько детей и внуков у Иванова Ивана:

select count(*)-1 from tab1 start with id=1
connect by prior id="Отношение" and "Отношение"<  >0;

|8 |

Сколько всего внучат:

select count(*) from tab1 start with "Поколение"=3
  connect by prior id="Отношение" and
  prior id="Поколение" and "Отношение"<  >0;

|4 |

Сколько всего внучат, родившихся до 1990 года:

select count(*) from tab1 start with "Поколение"=3
  connect by prior id="Отношение" and
prior id="Поколение" and "Отношение"<  >0 where "Год рождения"< 1990;

|3 |

Сколько внучат от 10 до 15 лет:

select count(*) from tab1 start with "Поколение"=3 and datesplit(to_date('2000','yyyy'),'y')- "Год рождения" between 10 and 15
  connect by prior id="Отношение" and prior id="Поколение" and "Отношение"<  >0;

|3 |

Сколько лиц мужского пола в семействе:

select count(id) from tab1 start with id=1
  connect by prior id="Отношение" and "Статус"='сын' and "Отношение"<  >0;

|6 |

Сколько дядей и тетей у детей Анны Петровой (дочери Ивана Иванова):

select count(id)-1 from tab1
start with id=(select distinct id from tab1 where "Фамилия"='Иванов' and "Имя"='Иван' and "Поколение"=1)
  connect by prior id="Отношение"
  and "Фамилия" not like 'Петров%'
  and "Поколение"=2
  and "Отношение"<  >0;

|3 |

Имена и фамилии дядей и тетей детей Анны Петровой (дочери Ивана Иванова):

 select cast "Фамилия" || ' ' || "Имя" as char(30) from tab1
  where id in (select id from tab1
  start with id=(select distinct id from tab1 
                  where "Фамилия"= 'Иванов' 
                    and "Имя"= 'Иван' 
                    and "Поколение"=1)
connect by prior id="Отношение"
    and "Фамилия" not like 'Петров%'
    and "Поколение"=2
    and "Отношение"<  >0
 except
 select distinct id from tab1 
  where "Фамилия"= 'Иванов' 
    and "Имя"= 'Иван' 
    and "Поколение"=1);

|Иванов Сергей|
|Иванов Андрей|
|Иванов Иван  |
create table "Служащие"
(
  "Фамилия" char(40),
  "Таб_Номер" int primary key,
  "Таб_Номер_Руковод" int references "Служащие"("Таб_Номер"),
  "Должность" char(20)
);
INSERT INTO "Служащие" VALUES ('ИВАНОВ',7839,NULL,'РУКОВОДИТЕЛЬ');
INSERT INTO "Служащие" VALUES ('ПЕТРОВ',7698,7839,'МЕНЕДЖЕР');
INSERT INTO "Служащие" VALUES ('СИДОРОВ',7782,7839,'МЕНЕДЖЕР');
INSERT INTO "Служащие" VALUES ('СМИРНОВ',7566,7839,'МЕНЕДЖЕР');
INSERT INTO "Служащие" VALUES ('СИМОНОВ',7902,7566,'АНАЛИТИК');
INSERT INTO "Служащие" VALUES ('СОЛОВЬЕВ',7369,7902,'СЛУЖАЩИЙ');
INSERT INTO "Служащие" VALUES ('АНТОНОВ',7499,7698,'ПРОДАВЕЦ');
INSERT INTO "Служащие" VALUES ('СЕМЕНОВ',7521,7698,'ПРОДАВЕЦ');
INSERT INTO "Служащие" VALUES ('КУЗНЕЦОВ',7654,7698,'ПРОДАВЕЦ');
INSERT INTO "Служащие" VALUES ('МИРОНОВ',7788,7566,'АНАЛИТИК');
INSERT INTO "Служащие" VALUES ('ТИХОНОВ',7844,7698,'ПРОДАВЕЦ');
INSERT INTO "Служащие" VALUES ('БОРИСОВ',7876,7788,'СЛУЖАЩИЙ');
INSERT INTO "Служащие" VALUES ('ВАСИЛЬЕВ',7900,7698,'СЛУЖАЩИЙ');
INSERT INTO "Служащие" VALUES ('ТИХОНОВ',7934,7782,'СЛУЖАЩИЙ');

 SELECT *
   FROM "Служащие"
  START WITH "Должность" = 'РУКОВОДИТЕЛЬ'
CONNECT BY PRIOR "Таб_Номер" = "Таб_Номер_Руковод";

|Фамилия  |Таб_Номер|Таб_Номер_Рук|Должность    |LEVEL|
|ИВАНОВ   | 7839    | < NULL >     |РУКОВОДИТЕЛЬ | 1   |
|ПЕТРОВ   | 7698    | 7839        |МЕНЕДЖЕР     | 2   |
|АНТОНОВ  | 7499    | 7698        |ПРОДАВЕЦ     | 3   |
|СЕМЕНОВ  | 7521    | 7698        |ПРОДАВЕЦ     | 3   |
|КУЗНЕЦОВ | 7654    | 7698        |ПРОДАВЕЦ     | 3   |
|ТИХОНОВ  | 7844    | 7698        |ПРОДАВЕЦ     | 3   |
|ВАСИЛЬЕВ | 7900    | 7698        |СЛУЖАЩИЙ     | 3   |
|СИДОРОВ  | 7782    | 7839        |МЕНЕДЖЕР     | 2   |
|ТИХОНОВ  | 7934    | 7782        |СЛУЖАЩИЙ     | 3   |
|СМИРНОВ  | 7566    | 7839        |МЕНЕДЖЕР     | 2   |
|СИМОНОВ  | 7902    | 7566        |АНАЛИТИК     | 3   |
|СОЛОВЬЕВ | 7369    | 7902        |СЛУЖАЩИЙ     | 4   |
|МИРОНОВ  | 7788    | 7566        |АНАЛИТИК     | 3   |
|БОРИСОВ  | 7876    | 7788        |СЛУЖАЩИЙ     | 4   |

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

select "LEVEL","Фамилия" from "Служащие"
 start with "Должность" = 'АНАЛИТИК' connect BY PRIOR "Таб_Номер"="Таб_Номер_Руковод";

 LEVEL Фамилия
|1    |СИМОНОВ  |
|2    |СОЛОВЬЕВ |
|1    |МИРОНОВ  |
|2    |БОРИСОВ  |