Определение иерархического запроса к таблице.
::=
::=
::=
::=
::=
Опция START WITH
задает условия выбора начальной записи иерархической структуры данных.
Опция CONNECT BY <условие связи>
задает отношение между узлами иерархической структуры данных. Часть элементов <условия связи>
должна быть использована в опции PRIOR
для ссылки на предыдущий уровень иерархии.
Опция PRIOR
устанавливает предыдущий уровень текущей записи иерархии. PRIOR
– унарный оператор и обрабатывается как обычные унарные + и - операторы. В неиерархических запросах при одной исходной таблице во всех <условиях связи>
имена столбцов (не считая внешних ссылок) рассматриваются как ссылки к одной и той же записи исходной таблицы. Напротив, в иерархическом запросе <условие связи>
должно содержать ссылки к двум записям – текущего и предыдущего уровня, которые оно и связывает между собой.
В <условиях связи> <предыдущий уровень>
и <последующий уровень>
можно менять местами. <Условие связи>
должно содержать ссылки к двум записям – текущего и предыдущего уровня, которые связывает между собой. Но так как в иерархическом запросе может участвовать только одна таблица, то различие между обращениями к записям текущего и предыдущего уровня делается следующим образом: если перед аргументом предиката стоит ключевое слово PRIOR
, то все обращения к столбцам в этом аргументе предиката относятся к записи предыдущего уровня, если же PRIOR
не стоит – то к записи текущего уровня.
Эти конструкции эквивалентны:
select * FROM "Служащие" START WITH "Должность" = 'РУКОВОДИТЕЛЬ' CONNECT BY PRIOR "Таб_Номер"="Таб_Номер_Руковод";
select * FROM "Служащие" START WITH "Должность" = 'РУКОВОДИТЕЛЬ' CONNECT BY "Таб_Номер_Руковод" = PRIOR "Таб_Номер";
<Предыдущий уровень>
и <последующий уровень>
должны ссылаться на <значимые выражения>
, содержащие разные столбцы таблицы.
Типы данных <предыдущего уровня>
и <последующего уровня>
должны быть совместимыми.
Условия WHERE
, START
и CONNECT
в иерархических запросах можно задавать в произвольном порядке.
Столбец 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 <значение>
(для сложных условий результат может быть не верным).
Опция 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|
Запрещается использовать одновременно <WHERE-спецификацию>
и <ORDER BY-спецификацию>
с опцией SIBLINGS
. Т.е. запрос вида
select * from test start with parent is null connect by prior id = parent where id <> 1 order siblings by name;
является синтаксически неправильным.
Псевдостолбцы 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;
Опция 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|
Результирующая таблица по умолчанию имеет тот же список столбцов, что и исходная, с добавлением псевдостолбца с именем «LEVEL» типа INTEGER
, который указывает номер уровня записи в иерархии.
Наличие псевдостолбца с именем «LEVEL» в выборке вида SELECT * FROM… зависит от ключа /COMPATIBILITY=ORACLE в команде запуска ядра СУБД:
псевдостолбец будет присутствовать, если ключ не задан;
псевдостолбец будет отсутствовать, если ключ задан.
Псевдостолбец LEVEL
можно указывать в <основном запросе выборки>
и <WHERE-спецификации>
и нельзя в конструкциях START WITH
и CONNECT BY
.
Сортировка записей в ответе (если не задана <GROUP BY-спецификация>
) выполняется в таком порядке, чтобы все записи, подчиненные в иерархии некоторой записи, следовали за ней.
Условие, содержащееся в конструкции START
, определяет записи верхнего уровня в иерархии.
Условие, содержащееся в конструкции CONNECT
, определяет связи между записями предыдущего и последующего уровней.
<Иерархический запрос>
выполняется столько раз, сколько <начальных уровней>
найдено в таблице, задаваемой <FROM-спецификацией>
.
Максимальное количество уровней для сортируемых иерархических запросов равно 4096.
Если в иерархии записей обнаружено нарушение структуры дерева, фиксируется исключительная ситуация.
Для запросов с 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 | БОРИСОВ |