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

Специальные системные функции в предложениях с CONNECT BY

Специально для запросов по дереву создан ряд системных функций. Один пример — LEVEL — уже приводился. Другой пример — функция SYS_CONNECT_BY_PATH, позволяющая получить для каждой строки ее полный "путь", считая от точки отсчета:

COLUMN epath FORMAT A100
SELECT     LEVEL, SYS_CONNECT_BY_PATH ( ename, '/' ) epath
FROM       emp
CONNECT BY PRIOR empno = mgr
  START WITH mgr IS NULL
;

Список функций, специально предназначенных для употребления в предложениях с CONNECT BY:

[9-) начиная с версии 9

[10-) начиная с версии 10

Упорядочение результата

Фраза CONNECT BY выдает в результате дерево, но не заботится о порядке перечисления "веток" в пределах одного уровня. Упорядочить по заданному критерию ветви дерева традиционными средствами возможно, но делать это крайне неудобно. С версии 9 задачу много проще решить употреблением специальной фразы ORDER SIBLINGS BY:

SELECT            LEVEL, SYS_CONNECT_BY_PATH ( ename, '/' ) epath
FROM              emp
CONNECT BY          PRIOR empno = mgr
  START WITH        mgr IS NULL
  ORDER SIBLINGS BY ename
;

Фразы ORDER BY и ORDER SIBLINGS BY в предложении SELECT — взаимоисключающие.

Обработка зацикливания

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

SQL> UPDATE emp SET mgr = 7876 WHERE ename = 'JONES';
1 row updated.

У Джонса начальником поставлен Адамс (теперь Адамс → Джонс; здесь стрелка указывает на подчиненного), но тот же Адамс и среди его подчиненных (Джонс → Скотт → Адамс):

SQL> SELECT     SYS_CONNECT_BY_PATH ( ename, '/' ) epath
  2  FROM       emp
  3  CONNECT BY PRIOR empno = mgr
  4* START WITH ename = 'JONES'
SQL> ;
ERROR:
ORA-01436: CONNECT BY loop in user data

Указание NOCYCLE заставит Oracle завершить рекурсивный просмотр записей при обнаружении зацикленности и не сообщать об ошибке:

SQL> SELECT     SYS_CONNECT_BY_PATH ( ename, '/' ) epath
  2  FROM       emp
  3  CONNECT BY NOCYCLE PRIOR empno = mgr
  4* START WITH ename = 'JONES'
  5  /
EPATH
--------------------
/JONES
/JONES/SCOTT
/JONES/SCOTT/ADAMS
/JONES/FORD
/JONES/FORD/SMITH
SQL> ROLLBACK;

В любом случае "бесконечного" выполнения запроса при использовании CONNECT BY не случится.

Недревовидная иерархия

Фраза CONNECT BY способна рекурсивно обрабатывать не только древовидно организованные данные, но и иерархию общего вида. Выполним:

CREATE TABLE way (
  node     VARCHAR2 ( 20 )
, parent   VARCHAR2 ( 20 )
, distance NUMBER   (  5 ) 
);
INSERT INTO way VALUES ( 'Ленинград', 'Москва',    696 );
INSERT INTO way VALUES ( 'Новгород',  'Москва',    538 );
INSERT INTO way VALUES ( 'Ленинград', 'Новгород',  179 );
INSERT INTO way VALUES ( 'Выборг',    'Ленинград', 135 );
COMMIT;

Обратите внимание, что создана не "таблица с расстояниями", а таблица с направленными маршрутами, предоставляющая расстояния между городами с точки зрения Москвы (здесь — единственная вершина иерархии). Такое представление данных и приводимые ниже запросы плохо подходят для решения более общей задачи поиска маршрута между двумя произвольными точками.

Запрос вниз по иерархии от узла 'Москва' (присутствует только в качестве предка):

SQL> COLUMN route FORMAT a40
SQL> SELECT     SYS_CONNECT_BY_PATH ( node, '/' ) route
  2  FROM       way
  3  CONNECT BY PRIOR node = parent
  4  START WITH parent = 'Москва'
  5  ;
ROUTE
---------------------------------------------------
/Ленинград
/Ленинград/Выборг
/Новгород
/Новгород/Ленинград
/Новгород/Ленинград/Выборг

Запрос вверх по иерархии от узла 'Выборг':

SQL> SELECT     SYS_CONNECT_BY_PATH ( node, '/' ) route
  2  FROM       way
  3  CONNECT BY node = PRIOR parent
  4  START WITH node = 'Выборг'
  5  ;
ROUTE
---------------------------------------------------
/Выборг
/Выборг/Ленинград
/Выборг/Ленинград
/Выборг/Ленинград/Новгород

Упражнение. Внесите в таблицу WAY зацикленность данных и проверьте реакцию фразы CONNECT BY на цикл.