FROM more_employees
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id
ORDER BY LEVEL;
Расширенные запросы 205
LEVEL EMPLOYEE_ID MANAGER_ID FIRST.NAME LAST_NAME
1 1 James Smith
2 2 1 Ron Johnson
2 4 1 Susan Jones
2 10 1 Kevin Black
3 3 2 Fred Hobbs
3 7 4 John Grey
3 12 10 Frank Howard
3 13 10 Doreen Penn
3 11 10 K e ith Long
3 5 2 Rob Green
3 6 4 Jane Brown
4 9 6 Henry Heyson
4 8 7 Jean Blue
В следующем запросе для определения числа уровней в дереве используется
функция COUNT( ) и псевдостолбец LEVEL:
□ SELECT C0UNT(DISTINCT LEVEL)
FROM more_employees
START WITH employee_id' = 1
CONNECT BY employee_id = manager_id;
COUNT(DISTINCTLEVEL)
4
Форматирование результатов иерархического запроса
Можно форматировать результаты иерархического запроса, используя псевдостолбец
LEVEL и функцию LPAD() , которая слева дополняет значение заданными
символами. В приведенном ниже примере функция LPAD(1
2* LEVEL - 1) используется для вставки 2* LE V E L -1 пробелов. Это требуется для
создания с помощью пробелов отступа перед именем служащего; величина
пробела зависит от значения LEVEL (при LEVEL = 1 отступа нет, при LEVEL = 2
делается отступ на две позиции, при LEVEL =3 - на четыре, и так далее).
□ SET PAGESIZE 999
COLUMN employee FORMAT A25
SELECT LEVEL,
LPADC ‘ , 2 * LEVEL - 1 ) || first_name || 1 ‘ ||
last_name AS employee
FROM more_employees
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id;
LEVEL EMPLOYEE
1 James Smith
2 Ron Johnson
3 Fred Hobbs
3 Rob Green
2 Susan Jones
206 Глава 7
34
3
4
2
3
3
3
Jane Brown
Henry Heyson
John Grey
Jean Blue
Kevin B la ck
K e ith Long
Frank Howard
Doreen Penn
По этим результатам можно легко понять взаимоотношения между сотрудниками.
Начинаем не с корневого узла
Вовсе не обязательно при обходе дерева всегда начинать с корневого узла:
используя фразу START WITH, можно начать обход с любого узла. В следующем
запросе начальным является узел, соответствующий служащей Susan
Jones; в этом случае LEVEL возвращает 1 для Susan Jones, 2 - для Jane Brown
и т. д.:
П SELECT LEVEL, . .
LPAD(‘ 2 * LEVEL - 1 ) || first_name || ‘ ' II
last_name AS employee
FROM more_employees
START WITH last_name = 'Jones’
CONNECT BY PRIOR employee_id = manager_id;
LEVEL EMPLOYEE
1 Susan Jones
2 Jane Brown
3 Henry Heyson
2 John Grey
3 Jean Blue
Если бы в магазине было более одного служащего с одинаковой фамилией,
можно было бы просто использовать во фразе START WITH запроса
столбец employeejd. В следующем запросе используется значение столбца
employee_id для Susan Jones, равное 4:
□ SELECT LEVEL,
LPADC ‘ , 2 * LEVEL - 1 ) || firs t_ n am e || ' ‘ II
last_name AS employee
FROM more_employees
START WITH employee_id = 4
CONNECT BY PRIOR employee_id = manager_id;
Запрос возвращает те же самые строки, что и предыдущий запрос.
Использование подзапросов во фразе START WITH
Во фразе START WITH можно использовать подзапросы. Следующий запрос
использует подзапрос для выборки значения employee_id служащего Kevin
Black. Это значение employee_id затем передается во фразу START WITH:
Расширенные запросы 207
□ SELECT LEVEL
LPAD( ‘ ‘ , 2 * LEVEL - 1) || first_n am e || ' ' ||
last_name AS employee
FROM more_employees
START WITH employee_id = (
SELECT employee_id
FROM more_employees
WHERE first_n ame = ‘ K e v in ’
AND last_name = ‘ B la c k ’
) CONNECT BY PRIOR employee_id =
manager_id;
LEVEL EMPLOYEE
1 Kevin B lack
2 K e ith Long
2 Frank Howard
2 Doreen Penn
Восходящий обход дерева
He обязательно обходить дерево сверху вниз - от родителей к детям; можно
начать с дочернего узла и вести обход снизу вверх. Этого можно добиться,
если поменять местами дочерний и родительский узлы во фразе CONNECT
BY PRIOR. Например, фраза CONNECT BY PRIOR manager_id = employee_id соединяет
manage r_ id дочернего столбца с employee_id родительского узла.
Следующий запрос начинается со служащего Jean Blue, после чего обход
дерева производится в направлении снизу вверх, вплоть до служащего James
Smith; LEVEL возвращает значение 1 для Jean Blue, 2 - для John Grey и т. д.:
□ SELECT LEVEL
LPADC ‘, 2 * LEVEL - 1) || first_name 11 ‘ ‘ 11
last_name AS employee
FROM more_employees
START WITH last_name = ‘Blue’
CONNECT BY PRIOR manager_id = employee_id;
LEVEL EMPLOYEE
1 Jean Blue
2 John Grey
3 Susan Jones
4 James Smith
Исключение из иерархического запроса узлов и ветвей
Используя фразу WHERE, можно исключить из дерева запроса конкретный
узел. Следующий запрос исключает из результатов запроса данные о служащем
Ron Johnson, используя для этого фразу WHERE last_name !=
Johnson':
□ SELECT LEVEL
LPADC ‘, 2 * LEVEL - 1) || first.name || ‘ ‘ ||
208 Глава 7
last_name AS employee
FROM more_employees
WHERE last_name != 'Johnson'
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id;
LEVEL EMPLOYEE
1 James Smith
3 Fred Hobbs
3 Rob Green
2 Susan Jones
3 Jane Brown
4 Henry Heyson
3 John Grey