II.2.7. Иерархические запросы

Q2_26 Древовидная структура таблицы EMPLOYEE, отражающая подчиненность сотрудников фирмы

demo@10g> select level,DEPARTMENT_ID,EMPLOYEE_ID,last_name,job_id,salary

from EMPLOYEE

connect by prior EMPLOYEE_ID=manager_id

start with manager_id is null;

Где /3/:

CONNECT BY (соединить по) - задает зависимость между родительскими и
дочерними вершинами (строками) иерархии. Если в заданной иерархии
обнаруживается петля, то Oracle возвращает сообщение об ошибке.

PRIOR (предшествующий) - определяет направление обхода дерева. В примере
задано направление от родителя к потомку (прочитать эту часть запроса следует так: предшестаующим столбцу EMPLOYEE_id является столбец MANAGER_ID) . Направлению от потомка к родителю соответствовало бы условие EMPLOYEE_id = PRIOR manager_id или PRIOR manager_id =EMPLOYEE_id.

START WITH (начать с) - если задано, то позволяет указать, с какой вершины
(строки) или вершин (строк) начать построение дерева. То есть задает
корневую (вые) вершины (строки).

Используя информацию, заданную в этих предложениях, Oracle формирует иерархию следующим образом:

1. Выбирает корневую строку (строки) иерархии в соответствии с условиями
заданными во фразе START WITH. Если эта фраза опущена, то Oracle будет
использовать все строки таблицы в качестве корневых (попробуйте). Условие в
START WITH может содержать подзапрос.

2. Для каждой корневой строки выбираются дочерние строки, каждая из
которых должна удовлетворять условию фразы CONNECT BY по отношению к
одной из корневых строк.

3. Oracle выбирает следующие одно за другим поколения дочерних строк.
Сначала выбираются потомки строк, полученных на этапе 2, затем потомки
этих потомков и так далее. Oracle всегда выбирает дочерние строки на
основании условия CONNECT BY по отношению к текущей родительской
строке.

4. Если запрос содержит фразу WHERE, то Oracle исключает из иерархии все
строки, которые не удовлетворяют условию, заданному в этой фразе.
5. Oracle возвращает результат запроса, в котором выбранные строки
расположены в иерархическом порядке, то есть потомки выводятся после
своих родителей.

Q2_27 Используя функцию LPAD и псевдостолбец LEVEL можно украсить

выводимый результат, предварительно отформатировав солбец «tree» :

demo@10g> col tree format a20

demo@10g> select lpad(' ',(level-1)*3)||level||' '||last_name as tree,

EMPLOYEE_ID,job_id,manager_id from EMPLOYEE

connect by prior EMPLOYEE_ID=manager_id

start with last_name='KING';

Q2_28 Вывести информацию о подчиненных JONES и о нем самом

demo@10g> select lpad(' ',(level-1)*3)||level||' '||last_name as tree,

EMPLOYEE_ID,job_id,manager_id from EMPLOYEE

connect by prior EMPLOYEE_ID=manager_id

start with last_name='JONES';

Q2_29 Вывести информацию о начальниках SMITH-а и о нем самом

demo@10g> select lpad(' ',(level-1)*3)||level||' '||last_name as tree,

EMPLOYEE_ID,job_id,manager_id from EMPLOYEE

connect by EMPLOYEE_ID = prior manager_id

start with last_name='SMITH';

Q2_30 Из дерева иерархии сотрудников можно исключать отдельные ветви.

Исключим все строки, связанные с «DOYLE»

select lpad(' ',(level-1)*3)||level||' '||last_name as tree,

EMPLOYEE_ID,job_id,manager_id

from EMPLOYEE

connect by prior EMPLOYEE_ID = manager_id and last_name <> 'DOYLE'

start with salary=5000;