Задача. Вызов многозначной функции.

Имеется иерархическая таблица сотрудников kk_employee из задачи "Соединение иерархической таблицы с обычной".

Написать функцию, которая по сотруднику выдает два значения:

- на каком уровне иерархии находится сотрудник (у самого верховного босса уровень = 1).

- список его боссов через ';', упорядоченный по карьерному уровню, начиная от самого мелкого босса, заканчивая самым большим боссом.

Потренируемся на мышках. В подзапросе q1 раскручиваем иерархию в направлении от подчиненного к боссу.

Пишем "connect by prior boss_id = id" вместо "connect by prior id = boss_id", как было в задаче "Соединение иерархической таблицы с обычной".

Далее во внешнем запросе выбираем строку с максимальным уровнем и отсекаем самого сотрудника, т.к. он сам себе не босс:

select max(lvl),

max(substr(bosses, instr(bosses || ';', ';', 2) + 1)) keep(dense_rank last order by lvl)

from (select level lvl, sys_connect_by_path(fio, ';') bosses

from kk_employee

start with fio = 'мышь Пухлик'

connect by prior boss_id = id) q1;

Создадим необходимый тип для возвращаемого будущей функцией значения:

create or replace type type_employee_boss as object

(

levels_number integer,

boss_list varchar2(4000)

);

По результатам тренировки на мышках напишем необходимую функцию:

create or replace function get_employee_boss(in_fio in varchar2)

return type_employee_boss as

v_result type_employee_boss;

v_level integer;

v_boss_list varchar2(4000);

begin

select max(lvl),

max(substr(bosses, instr(bosses || ';', ';', 2) + 1)) keep(dense_rank last order by lvl)

into v_level, v_boss_list

from (select level lvl, sys_connect_by_path(fio, ';') bosses

from kk_employee

start with fio = in_fio

connect by prior boss_id = id) q1;

v_result := type_employee_boss(v_level, v_boss_list);

return v_result;

end get_employee_boss;

Создадим вьюшку, которая будет использовать функцию с несколькими результатами:

create or replace view vw_get_employee_boss as

select id, fio,

(f).levels_number hierarchy_level,

(f).boss_list boss_list

from (select id, fio, get_employee_boss(fio) f from kk_employee) x;

Посмотрим на результаты:

select * from vw_get_employee_boss;

Добавим к этой задаче подзадачу. Требуется для сотрудника 'мышь Пухлик' запросить список его боссов через ';', упорядоченный по карьерному уровню в другом порядке, начиная от самого большого босса, заканчивая самым мелким боссом.

Возьмем запрос, который назывался “потренируемся на мышках”. С помощью строковой функции reverse продемонстрируем, как развернуть список боссов в другом порядке:

select max(lvl),

max(bosses) keep(dense_rank last order by lvl),

max(reversed_bosses) keep(dense_rank last order by lvl),

max(bosses_backward_path) keep(dense_rank last order by lvl)

from (select level lvl,

sys_connect_by_path(fio, ';') bosses,

sys_connect_by_path(reverse(fio), ';') reversed_bosses,

reverse(sys_connect_by_path(reverse(fio), ';')) bosses_backward_path

from kk_employee

start with fio = 'мышь Пухлик'

connect by prior boss_id = id) q1;

Также можно самостоятельно изучить статью о pipelined функциях, которые возвращают целые таблицы:

http://www.sql.ru/faq/faq_topic.aspx?fid=221