Опубликован: 28.12.2011 | Доступ: свободный | Студентов: 6784 / 673 | Оценка: 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'));

И сколько строк он все таки вернет
Ольга Угарова
Ольга Угарова
Россия, Москва, НИЯУ МИФИ