Опция 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|

Опция 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).