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

Если таблица содержит иерархически связанные данные, то можно выбирать из нее строки в иерархическом порядке, используя следующие фразы:

START WITH – идентифицирует строки, которые должны использоваться как корневые в иерархическом запросе. Эта фраза задает условие, которому должны удовлетворять корневые строки иерархии. Если эта фраза опущена, то ORACLE использует все строки в таблице как корневые строки. Условие START WITH может содержать подзапрос.

CONNECT BY - специфицирует отношение между родительскими и порожденными строками в иерархическом запросе. Эта фраза содержит условие, которое определяет это отношение. Условие может быть любым общим условием, однако какая-либо часть этого условия должна использовать оператор PRIOR, чтобы сослаться на родительскую строку. Часть условия, содержащая оператор PRIOR, должна иметь один из следующих видов:

PRIOR выражение оператор_сравнения выражение или выражение оператор_сравнения PRIOR выражение

Отыскивая "детей" текущей родительской строки, ORACLE вычисляет выражение, перед которым стоит PRIOR, для родительской строки, а второе выражение - для каждой строки в таблице. Те строки, для которых условие истинно, становятся порожденными для текущей строки. Фраза CONNECT BY может содержать и другие условия, чтобы осуществить дальнейшую фильтрацию строк, выбираемых запросом. Фраза CONNECT BY не может содержать подзапрос.

Если фраза CONNECT BY приводит к циклическому перебору иерархии, то ORACLE возвращает ошибку. Цикл возникает, если одна строка является одновременно как прямым предком, так и прямым потомком другой строки..

WHERE – ограничивает строки, возвращаемые запросом, не затрагивая других строк иерархии.

ORACLE формирует иерархию, используя информацию из этих фраз запроса, следующим образом:

1. ORACLE выбирает корневые строки иерархии. Это те строки, которые удовлетворяют условию фразы START WITH.

2. ORACLE выбирает порожденные строки для каждой корневой строки. Каждая порожденная строка должна удовлетворять условию фразы CONNECT BY по отношению к одной из корневых строк.

3. ORACLE выбирает последовательные поколения порожденных строк. Сначала ORACLE выбирает "детей" для строк, возвращаемых на шаге 2, затем "детей" этих "детей", и т.д. ORACLE всегда выбирает порожденные строки путем вычисления условия CONNECT BY по отношению к текущей родительской строке.

4. Если запрос содержит фразу WHERE, то ORACLE удаляет из иерархии все строки, которые не удовлетворяют условию фразы WHERE. ORACLE вычисляет это условие для каждой строки индивидуально, вместо того чтобы удалять всех "детей" строки, не удовлетворяющей этому условию.

5. ORACLE возвращает строки в порядке, который показан на следующей диаграмме (порожденные строки показаны под своими родительскими строками).

Предложения SELECT, выполняющие иерархические запросы,подвержены следующим ограничениям:

Предложение SELECT, выполняющее иерархический запрос, не может в то же время выполнять соединение.

Такое предложение не может выбирать данные из представления, запрос которого выполняет соединение.

Если в иерархическом запросе используется фраза ORDER BY, то ORACLE сортирует строки, как указано фразой ORDER BY, а не в том порядке, какой показан выше в шаге 5.

Предложения SELECT, выполняющие иерархические запросы, могут использовать псевдостолбец LEVEL. Этот псевдостолбец возвращает УРОВЕНЬ строки - значение 1 для корневого узла, 2 для узла, непосредственно порожденного корневым, 3 для "внука", и т.д. Количество уровней, возвращаемых иерархическим запросом, может быть лимитировано доступной памятью пользователя.

Пример 46. Предположим, что имеется таблица сотрудников некоторой условной компании. В этой таблице для каждого сотрудника хранится:

фамилия - поле FAM

код сотрудника - поле KS

код сотрудника, которому подчиняется данный сотрудник – поле KSR

название должности – поле ND.

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

SELECT LPAD(' ',2*(LEVEL-1)) || fam fams, ks, ksr, nd FROM sotr START WITH nd = 'ПРЕЗИДЕНТ' CONNECT BY PRIOR ks = ksr;

Результат:

fams	ks	ksr	nd
НИКОЛАЕВ			ПРЕЗИДЕНТ
ПЕТРОВ			НАЧАЛЬНИК ОТДЕЛА КАДРОВ
КОШКИН			ГЛАВНЫЙ СПЕЦИАЛИСТ
МАКАРОВ			НАЧАЛЬНИК СЛУЖБЫ БЕЗОПАСНОСТИ
ВОРОБЬЕВА			НАЧАЛЬНИК ПЛАНОВО-ЭКОНОМИЧЕСКОГО ОТДЕЛА
МЫШКИН			ВЕДУЩИЙ ЭКОНОМИСТ
АНТОНОВА			ЭКОНОМИСТ