Опубликован: 28.12.2011 | Доступ: свободный | Студентов: 7046 / 737 | Оценка: 3.81 / 3.53 | Длительность: 19:30:00
ISBN: 978-5-9963-0488-2
Лекция 7:

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

< Лекция 6 || Лекция 7: 123456 || Лекция 8 >

Специальные системные функции в предложениях с 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:

Функция Описание
LEVEL Номер уровня в дереве (1 — корень, 2 — нижележащий уровень и т. д.)
SYS_CONNECT_BY_PATH (столбец, разделитель)[9-) Путь от корня дерева к узлу
CONNECT_BY_ISCYCLE[10-) 1, если потомок узла является одновременно его предком, иначе 0
CONNECT_BY_ISLEAF[10-) 1, если узел не имеет потомков
CONNECT_BY_ROOT (столбец) или CONNECT_BY_ROOT столбец[10-) Значение из строки-корня
PRIOR (столбец) или PRIOR столбец Значение из строки — прямого предка

[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 на цикл.

< Лекция 6 || Лекция 7: 123456 || Лекция 8 >
Ярослав Прозоров
Ярослав Прозоров

В лекции № 7 "Введение в Oracle SQL" в подразделе "Несамостоятельность группировки с обобщениями ROLLUP, CUBE и GROUPING SETS"  представленная таблица сравнения содержит ошибки - окончания запросов пропущены. Видимо, ошибки вызваны некорректным переносом материала лекции.

Володимир Миколайчук
Володимир Миколайчук
Помогите разобраться поетапно с логикой запроса
-------TOOLS
NAME PRICE TYPE
drill 155 A
sawzall 192 N
mitre saw 292 M
router 86 I
RAD 145 M
jigsaw 128 I
screwdriver 77 P
------TOOL_TYPES
TYPE USAGE
A Always
I Often
M Sometimes
N Rarely
P Never

Запрос SQL:
SELECT t.type, SUM(t.price)
FROM tools t
GROUP BY t.type
HAVING SUM(t.price) >= (SELECT AVG(price)
FROM tools
WHERE type IN (SELECT type
FROM tool_types
WHERE usage = 'Often'));

И сколько строк он все таки вернет
Анатолий Федоров
Анатолий Федоров
Россия, Москва, Московский государственный университет им. М. В. Ломоносова, 1989
Дмитрий Квашнёв
Дмитрий Квашнёв
Россия, Коломна, Московский государственный открытый университет, 2001