7. При отсутствии части данных в таблице строгое условие не выведет нам часть строк. Например, если в таблице emp пустое поле mgr, то эти сотрудники не будут выведены. Для получения списка всех сотрудников, не зависимо от наличия у них руководителя требуется использовать внешнее объединение, Отсутствующие данные будут заменены пустыми значениями в итоговом отчёте. Выполните запрос select e1.ename,e1.job,e1.sal,e1.hiredate,e2.ename,e2.job,e2.sal,e2.hiredate from emp e1, emp e2 where e1.mgr=e2.empno(+) . Соединение таблиц описано в условии where, (+) обозначает возможное отсутствие данных по полю empno таблицы e2. Этот же запрос можно выполнить, используя join синтаксис
select e1.ename,e1.job,e1.sal,e1.hiredate,e2.ename,e2.job,e2.sal,e2.hiredate from emp e1 left outer join emp e2 on e1.mgr=e2.empno. Конструкция left outer join emp e2 on e1.mgr=e2.empno позволяет выполнить соединение, при котором часть данных из таблицы e2 может отсутствовать.
8. Выполните запрос
select level,deptno,empno,mgr,ename,job,sal from emp
connect by prior empno=mgr
start with mgr is null
Для президента фирмы (KING) руководитель отсутствует (в столбце mgr значение NULL). В запросе есть часть «START WITH mgr IS NULL», чтобы «раскрутить» всю иерархию подчиненности, начиная с корневой строки иерархии (с президента фирмы). Если мы не укажем в запросе эту часть, запрос выведет главную иерархию, начинающуюся с KING-а, а также все возможные частные иерархии. В столбце «level» выводится уровень иерархии в «дереве» подчиненности. Для служащих с level 2 непосредственным начальником является KING. Служащие с level 3 в запросе выводятся сразу вслед за строкой своего начальника с level 2 и т.д. Часть запроса «CONNECT BY PRIOR empno=mgr» (дословно соединяться по предшествующему) определяет, что значения столбца «empno» являются родительскими, предшествующими (PRIOR) к дочернему столбцу «mgr». В выводимой информации видно, что значения столбца «empno» располагаются выше тех же значений в столбце«mgr». Сначала выводится, например, строка с ename «KING», а затем только все строки со служащими, начальником которых является KING. Это и соответствует отношению при выводе информации по запросу «родитель (empno)» -«дочерний (mgr)». Выполните запрос select level,deptno,empno,mgr,ename,job,sal from emp
connect by prior empno=mgr
start with mgr=7698. Иерархия «разворачивается» от подчиненного к начальнику. У служащего с номером 7698 есть 4 подчинённых, у которых подчиненные отсутствуют.
9. Выполните запрос select lpad(' ',(level-1)*3,'_')||level||' '||ename as tree,deptno,empno,mgr,ename,job,sal from emp
connect by prior empno=mgr
start with mgr is null. Функция LPAD(строка 1, х, строка 2) дополняет строку 1 до размера x символами строки 2. Из дерева иерархии сотрудников можно выделить отдельные ветви. Выделим строки, связанные с отделом 20:
select lpad(' ',(level-1)*3,'_')||level||' '||ename as tree,deptno,empno,mgr,ename,job,sal from emp
connect by prior empno=mgr and deptno=20
start with mgr is null.
10. Выполните запрос
select ename as "Сотрудник",connect_by_root ename as "Начальник",level-1 as "Уровень", sys_connect_by_path(ename,'/') as "Расположение"
from emp
where level>1 and deptno=20
connect by prior empno=mgr
Выведены все возможные ветви для сотрудников отдела 20: SCOTT →ADAMS (одна срока), FORD → SMITH (одна строка), JONES и четыре его подчиненных (четыре строки), KING и пять его подчиненных из 20-го отдела (пять строк). Функция SYS_CONNECT_BY_PATH(last_name, '/') в иерархических запросах формирует путь от начальника к подчиненному, а часть запроса «CONNECT_BY_ROOT ename "Начальник"» позволяет вывести из нашей части (для отдела 20) общего дерева иерархии фирмы всех возможных начальников.
Уважаемый посетитель!
Чтобы распечатать файл, скачайте его (в формате Word).
Ссылка на скачивание - внизу страницы.