Иерархические операторы запросов Oracle

Описание

Иерархический запрос - это тип запроса SQL, который обрабатывает данные иерархической модели. Существует особый случай более общих рекурсивных запросов с фиксированной точкой, которые вычисляют транзитивные замыкания. В стандартном SQL: 1999 иерархические запросы реализуются посредством рекурсивных общих табличных выражений.

Альтернативный синтаксис - нестандартная конструкция CONNECT BY; он был представлен Oracle в 1980-х годах. До Oracle 10g эта конструкция была полезна только для обхода ациклических графов, поскольку возвращала ошибку при обнаружении любых циклов.

В Oracle PRIOR и CONNECT_BY_ROOT операторы действительны только в иерархических запросах.

ПРИОР Оператор:

• Одно выражение в условии CONNECT BY должно быть квалифицировано оператором PRIOR.
• Если условие CONNECT BY является составным, то только для одного условия требуется оператор PRIOR, хотя у вас может быть несколько условий PRIOR.
• ПРИОР вычисляет непосредственно следующее выражение для родительской строки текущей строки в иерархическом запросе.
• ПРИОР чаще всего используется при сравнении значений столбцов с оператором равенства.

PRIOR заставляет Oracle использовать значение родительской строки в столбце. Операторы, отличные от знака равенства (=), теоретически возможны в предложениях CONNECT BY. Однако условия, созданные этими другими операторами, могут привести к бесконечному циклу возможных комбинаций. В этом случае Oracle обнаруживает цикл во время выполнения и возвращает ошибку.

CONNECT_BY_ROOT Оператор:

• CONNECT_BY_ROOT - унарный оператор, действительный только в иерархических запросах.
• Когда мы определяем столбец с помощью этого оператора, Oracle возвращает значение столбца, используя данные из корневой строки.
• Этот оператор расширяет функциональность условия CONNECT BY [PRIOR] иерархических запросов.

Ограничение: Вы не можете указать этот оператор в условии START WITH или CONNECT BY.

Примеры иерархических запросов

Следующий иерархический запрос использует предложение CONNECT BY, чтобы определить отношения между сотрудниками и менеджерами:

Пример таблицы: сотрудники

SELECT employee_id, first_name, last_name, manager_id
FROM employees
CONNECT BY PRIOR employee_id = manager_id;

Пример вывода:

 EMPLOYEE_ID FIRST_NAME LAST_NAME MANAGER_ID
----------- -------------------- ------------------- ------ ----------
        101 Neena Kochhar 100
	108 Нэнси Гринберг 101
	109 Даниэль Фавиет 108
	113 Луис Попп 108
	112 Жозе Мануэль Урман 108
	111 Исмаэль Скиарра 108
	110 Джон Чен 108
	205 Шелли Хиггинс 101
        206 Уильям Гитц 205
	...............

УРОВЕНЬ Пример:

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

Пример таблицы: сотрудники

SELECT employee_id, last_name, manager_id, LEVEL
FROM employees
CONNECT BY PRIOR employee_id = manager_id;

Выход

 EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL
----------- ------------------------- ---------- ---- ------
        101 Коххар 100 1
        108 Гринберг 101 2
        109 Фавиет 108 3
        113 Попп 108 3
        112 Урман 108 3
        111 Sciarra 108 3
        110 Чен 108 3
        205 Хиггинс 101 2
        206 Гитц 205 3
        204 Baer 101 2
        203 Маврис 101 2
        200 китов 101 2
        201 Хартштейн 100 1
        202 Fay 201 2 
	............... 

Примечание. В иерархическом запросе не указывайте ни ORDER BY, ни GROUP BY, так как они будут переопределять иерархический порядок результатов CONNECT BY. Если вы хотите упорядочить строки братьев и сестер одного и того же родителя, используйте предложение ORDER SIBLINGS BY.

Предыдущая: Арифметические операторы
Далее: Операторы Сета