BLAKE 0

CLARK 0

SCOTT 0

EMP - маленькая таблица в 14 строк, поэтому легко увидеть, что служащий KING является единственным корневым узлом, и не обязательно применять функцию SIGN к операции COUNT(*). Но если корневых узлов может быть несколько, тогда в скалярном подзапросе необходимо использовать или SIGN, или таблицу в одну строку, как показано ранее для подзапросов IS BRANCH и IS LEAF.

Oracle

Те, кто использует более ранние версии Oracle (до Oracle Database 10g), могут рассматривать решение для других СУБД, потому что оно подойдет (без всяких изменений) и для Oracle. При работе с Oracle Database 10g и более поздними версиями есть возможность воспользоваться преимуществами двух функций, которые сильно упрощают задачу по идентификации корневых и концевых узлов: это функции CON-

NECT BY ROOT и CONNECT BY ISLEAF соответственно. На момент написания данной книги, чтобы использовать CONNECT BY ROOT и CONNECT BY ISLEAF, в выражении SQL должен присутствовать оператор CONNECT BY. Первый шаг - найти концевые узлы, применяя CONNECT BY ISLEAF следующим образом:

select ename,

connect by isleaf is leaf from emp

start with mgr is null connect by prior empno = mgr order by 2 desc

ENAME IS LEAF

ADAMS 1

SMITH 1

ALLEN 1

TURNER 1

MARTIN 1

WARD 1

JAMES 1

MILLER 1

KING 0

JONES 0

BLAKE 0

CLARK 0

FORD 0

SCOTT 0

Далее с помощью скалярного подзапроса ищем узлы ветвления. Узлы ветвления - это служащие, являющиеся руководителями и подчиненными одновременно:

select ename,

(select count(*) from emp e

where e.mgr = emp.empno

and emp.mgr is not null

and rownum = 1) is branch

from emp

start with mgr is null connect by prior empno = mgr order by 2 desc

ENAME

IS BRANCH

JONES

SCOTT

BLAKE

FORD

CLARK

KING

MARTIN

MILLER

JAMES

TURNER

WARD

ADAMS

ALLEN

SMITH

Фильтр по ROWNUM обязателен и гарантирует возвращение 1 или 0 и ничего другого.

Последний шаг - с помощью функции CONNECT BY ROOT найти корневые узлы. Решение находит ENAME корневого узла и сравнивает его со всеми строками, возвращенными запросом. Если соответствий не обнаружено, эта строка является корневым узлом:

select ename,

decode(ename,connect by root(ename),1,0) is root from emp

start with mgr is null connect by prior empno = mgr order by 2 desc

ENAME

IS ROOT

KING

JONES

SCOTT

ADAMS

FORD

SMITH

BLAKE

ALLEN

WARD

MARTIN 0

TURNER 0

JAMES 0

CLARK 0

MILLER 0

Для Oracle 9i Database и более поздних версий в качестве альтернативы

CONNECT BY ROOT можно использовать функцию SYS CONNECT

BYPATH. Вот версия предыдущего решения для Oracle 9i Database: select ename,

decode(substr(root,1,instr(root )-1),NULL,1,0) root from ( select ename,

ltrim(sys connect by path(ename ) ) root from emp start with mgr is null connect by prior empno=mgr )

ENAME ROOT

KING 1

JONES 0

SCOTT 0

ADAMS 0

FORD 0

SMITH 0

BLAKE 0

ALLEN 0

WARD 0

MARTIN 0

TURNER 0

JAMES 0

CLARK 0

MILLER 0

Функция SYS CONNECT BY PATH составляет иерархию, начиная с корневого значения, как показано ниже:

select ename,

ltrim(sys connect by path(ename ) ) path from emp start with mgr is null connect by prior empno=mgr

ENAME PATH

KING KING

JONES KING,JONES

SCOTT KING,JONES,SCOTT

ADAMS KING,JONES,SCOTT,ADAMS

FORD KING,JONES,FORD