계층검색(트리검색)

--계층검색-------------------------------------------------------------

 

--트리검색(아래 -> 위)

select level, employee_id, last_name, job_id, manager_id

from employees

start with last_name = 'Vargas'

connect by prior manager_id = employee_id;

 

 

--트리검색(위 -> 아래)

select level, employee_id, last_name, job_id, manager_id

from employees

start with employee_id = 100

connect by prior employee_id = manager_id;

 

select level, last_name ||' report to '|| prior last_name "Walk Top Down"

from employees

start with last_name = 'King'

connect by prior employee_id = manager_id;

 

--level및 lpad를 사용하여 계층보고

select level, lpad(last_name, length(last_name) + (level * 2)-2, '_') || ' ' || first_name as org_chart

from employees

start with employee_id = 100

connect by prior employee_id = manager_id;

 

--계층제거

 

select level,department_id, employee_id, last_name, job_id, salary

from employees

start with manager_id is null

connect by prior employee_id = manager_id

and last_name != 'Kochhar';

 

    --이 경우 한명만 제거됨

    select level, department_id, employee_id, last_name, job_id, salary

    from employees

    where last_name != 'Kochhar'

    start with manager_id is null

    connect by prior employee_id = manager_id;