Выборка данных. Фразы ORDER BY, GROUP BY и CONNECT BY предложения SELECT. Множественные операции

Фраза CONNECT BY предложения SELECT

Для моделирования в БД фрагментов прикладной области порой удобна иерархическая (многоуровневая) организация данных. Фраза CONNECT BY используется для запросов по иерархически организованным записям. Она представляет собой специфичное расширение SQL в Oracle, несовместимое с другими диалектами SQL (применяющими с той же целью собственные конструкции) и отсутствующее в стандарте.

Элементами фразы CONNECT BY являются:

• указание строки, для которой следует вывести предков или потомков в иерархии (необязательная конструкция START WITH);
• указание направления движения по иерархии, вверх или вниз (системная функция PRIOR);
• условное выражение, определяющее иерархическую, по мнению программиста, связь между строками.

Последнее, то есть условное выражение, определяющее иерархию, приходится всякий раз указывать в запросе явочным порядком, так как ни SQL, ни тем более реляционная модель не позволяет задавать иерархическую зависимость между строками при определении таблицы (отношения). Об этом приходится сожалеть; это неизбежное следствие того, что подобная крайне ответственная часть описания данных не хранится в БД и вообще не хранится, иначе как в голове у программиста.

Хранение древовидно зависимых данных в БД

Наиболее востребованным видом иерархии является дерево. Древовидная зависимость сотрудников в таблице EMP представлена столбцами EMPNO (уникальный "табельный номер сотрудника") и MGR ("табельный номер руководителя сотрудника"). Это именно тот способ задания древовидной зависимости данных, который чаще всего встречается в жизни (классификаторы; устройство сложных агрегатов на производстве; структура организации и так далее).

В жизни однако этот способ часто применяется с той поправкой, что ссылка на предка устраняется из основной таблицы, а зависимость "предок — потомок" выносится в особую самостоятельную таблицу:

Достоинств у такого решения два:

1. столбец MGR теперь полностью заполнен и не имеет (единственного!) пропущенного значения; в отдельной таблице, описывающей подчиненность сотрудников, открылась возможность естественным образом указать дополнительные свойства такой подчиненности, как, например, время работы под началом конкретного руководителя или что-нибудь еще.

Есть и недостаток: запрос о сотрудниках теперь придется обращать уже к двум таблицам, и это более затратно. С точки зрения создателей реляционной модели ответственность за это несут разработчики СУБД.

Примеры запросов по дереву

Пример употребления фразы CONNECT BY в запросе о сотрудниках:

SELECT     ename 
FROM       emp 
CONNECT BY PRIOR empno = mgr 
START WITH ename = 'SCOTT'
;

Будут выданы все подчиненные сотрудника SCOTT. Пример показывает использование слова PRIOR во фразе CONNECT BY и конструкции START WITH. Слово PRIOR (оператор, "системная функция") приписывается ведущему столбцу в отношении упорядочения, а не выражению, в котором упоминается столбец. Выбор программистом ведущего столбца из пары, участвующей в построении условного выражения, фактически задает направление движения по дереву: поиск родителей или же потомков (как в данном случае).

Ведущий столбец можно употребить и во фразе SELECT; сравните предыдущий пример со следующим:

SELECT     ename, PRIOR ename 
FROM       emp 
CONNECT BY PRIOR empno = mgr 
START WITH ename = 'SCOTT'
;

Степень отдаленности от начального узла просмотра дерева показывает специальная системная функция без параметров LEVEL ("псевдостолбец", по терминологии Oracle), доступная исключительно в запросах с CONNECT BY:

SELECT     LEVEL, ename 
FROM       emp 
CONNECT BY empno = PRIOR mgr 
START WITH ename IN ( 'SCOTT', 'ALLEN' )
;

(Все начальники сотрудников SCOTT и ALLEN с указанием уровня подчиненности).

SELECT     LEVEL, ename 
FROM       emp 
CONNECT BY PRIOR empno = mgr
;

("Лес" деревьев подчиненности всех сотрудников друг другу; практически этот запрос не очень интересен ввиду появившихся в версии 9 более удобных системных функций).

Упражнение. Выдайте дерево подчиненности сотрудников с использованием отступов пробелами. Для формирования отступов удобно воспользоваться функциями RPAD или LPAD.

Фильтрация узлов дерева

При необходимости какие-то узлы дерева в выдачу можно не включать. Фильтрацию можно вставить во фразу CONNECT BY или во фразу WHERE. Так как логически CONNECT BY обрабатывается ранее WHERE (см. логическую схему обработки предложения SELECT выше), результаты фильтрации будут разными.

Пример исключения из списка подчиненных сотрудника SCOTT и всех его потомков:

SELECT     ename, PRIOR ename
FROM       emp
CONNECT BY PRIOR empno = mgr AND ename <> 'SCOTT'
  START WITH mgr IS NULL
;

Пример исключения из списка потомков сотрудника SCOTT:

SELECT     ename, PRIOR ename
FROM       emp
WHERE      ename <> 'SCOTT'
CONNECT BY PRIOR empno = mgr 
  START WITH mgr IS NULL
;

Заметьте, что в тексте WHERE предшествует CONNECT BY, но это не соответствует порядку обработки.