Иерархические запросы
Довольно часто приходится сталкиваться с данными, которые организованы
иерархически. Это могут быть, например, люди, работающие в
202 Глава 7
организации, генеалогическое древо или список деталей, из которых состоит
двигатель. В этом разделе я покажу, как выполнять запросы к таблице,
в которой хранятся данные о служащих, работающих в нашем гипотетическом
магазине.
Данные для примера
Мы будем работать с таблицей more_employees, которая создается сценарием
store_schema.sql в следующем виде:
□ CREATE TABLE more_employees (
employee_id INTEGER
CONSTRAINT inore_employees_pk PRIMARY KEY,
manager_id INTEGER
CONSTRAINT more_empl_fk_fk_more_empl
REFERENCES more_employees(employee_id),
firs t_ n am e VARCHAR2(10) NOT NULL,
last_name VARCHAR2(10) NOT NULL,
t i t l e VARCHAR2(20),
s a la r y NUMBER(6, 0)
);
Столбец manager_id является обратной ссылкой на столбец employee_
id той же таблицы more_employees; manager_id указывает на менеджера
(если такой имеется) сотрудника. В таблице more_employees содержатся
следующие строки:
□ SELECT *
FROM more_employees;
ID MANAGER_ID FIRST_NAME LAST_NAME TITLE SALARY
1 James Smith CEO 800000
2 1 Ron Johnson S a le s Manager 600000
3 2 Fred Hobbs S a le s Person 200000
4 1 Susan Jones Support Manager 500000
5 2 Rob Green S a le s Person 40000
6 4 Jane Brown Support Person 45000
7 4 John Grey Support Manager 30000
8 7 Jean Blue Support Person 29000
9 6 Henry Heyson Support Person 30000
10 1 Kevin B la ck Ops Manager 100000
11 10 K e ith Long Ops Person 50000
12 10 Frank Howard Ops Person 45000
13 Doreen Penn Ops Person 47000
Нетрудно заметить, что, пользуясь этими данными, довольно трудно
понять взаимосвязи между различными служащими. На рис. 7.1 все эти
взаимосвязи приводятся в графической форме.
Из рисунка видно, что, элементы (или узлы) образуют дерево. У состоящих
из узлов деревьев есть несколько ассоциируемых с ними технических
терминов.
■ Корневой узел Корнем называется узел в вершине дерева. На рисунке
7.1 корневым узлом является CEO Джеймс Смит (James Smith).
Расширенные запросы 203
■ Родительский узел Родителем называется узел, для которого есть
один или более узлов, расположенных ниже него. Например, Джеймс
Смит является родителем следующих узлов: Рон Джонсон, Сьюзен
Джонс и Кевин Блэк.
■ Дочерний узел Дочерним (или подчиненным) называется узел, над
которым имеется один родительский узел. Например, родителем Рона
Джонсона является Джеймс Смит.
■ Лист Листом (или концевой вершиной) называется узел, у которого
нет дочерних узлов. Например, Фред Хоббс и Роб Грин являются листьями.
Для выполнения иерархических запросов можно использовать фразы
CONNECT BY и START WITH оператора SELECT.
Рис. 7.1. Взаимоотношения между служащими
Использование фраз CONNECT BY и START WITH
Фразы CONNECT BY и START WITH имеют следующий синтаксис:
□ SELECT [LEVEL], столбец, выражение, . . .
FROM таблица
[WHERE 0pa3a_where]
[[START WITH стартовое_условие] [CONNECT BY PRIOR условие_рг1ог]];
где
■ LEVEL - псевдостолбец, который указывает, как глубоко в дереве вы
находитесь. LEVEL возвращает 1 для корневого узла, 2 - для дочернего,
подчиненного корневому, и т. д.;
204 Глава 7
■ стартовое_условие определяет, с какого места начинается иерархический
запрос. При написании иерархического запроса обязательно
должна быть использована фраза START WITH. Примером задания параметра
стартовое_условие может служить условие employee_id = 1 ,
которое указывает, что запрос начинается со служащего № 1 ;
■ условие_рпог определяет отношение между родительскими и дочерними
строками. Когда вы пишете иерархический запрос, обязательно
должна быть использована фраза CONNECT BY PRIOR. Примером для
условие_рпог является employee_id = manager_id, указывающее на
отношение между родительским employee_id и дочерним manager_
id, т. е. тот факт, что столбец manager_id дочернего узла указывает на
столбец employee_id родительского узла.
Следующий запрос иллюстрирует использование фраз START WITH и
CONNECT BY PRIOR. В первой строке содержатся данные служащего № 1
(James Smith), во второй строке - служащего Ron Johnson, для которого
значение столбца manager_id равно 1 , и так далее:
□ SELECT employee_id, manager_id, first_name, last_name
FROM more_employees
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id;
ID MANAGER_ID FIRSTJAME LAST_NAME
1 James Smith
2 1 Ron Johnson
3 2 Fred Hobbs
5 2 Rob Green
4 1 Susan Jones
6 4 Jane Brown
9 6 Henry Heyson
7 4 John Grey
8 7 Jean B la ck
10 1 Kevin B la ck
11 10 K e ith Long
12 10 Frank Howard
13 10 Doreen Penn
Использование псевдостолбца LEVEL
В следующем запросе иллюстрируется применение псевдостолбца LEVEL
для отображения уровня в дереве:
□ SELECT LEVEL, employee_id, manager_id, first_name, last_name