Доступные версии документации

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

Функция

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

Спецификация
         
<иерархический запрос>::=
<начальный уровень>::=
<предыдущий уровень>::=
<последующий уровень>::=
Синтаксические правила
  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   | БОРИСОВ  |
Заметили ошибку?
Выделите текст и нажмите Ctrl + Enter