[Oracle SQL] 계층형 쿼리

/* 
    계층형 쿼리 
        : 오라클에서만 제공됨
*/
 
create table bom_sphone(
    item_id number(3) not null,
    parent_id number(3),
    item_name varchar2(20) not null,
    primary key (item_id)
);
 
 
insert into bom_sphone values(100,null,'스마트폰');
insert into bom_sphone values(101,100,'메인pc');
insert into bom_sphone values(102,100,'배터리');
insert into bom_sphone values(103,101,'cpu');
insert into bom_sphone values(104,101,'메모리');
insert into bom_sphone values(105,101,'블루투스');
 
 
select * from bom_sphone;
 
 
 
select  s1.item_name, s1.item_id,s2.item_name parent_name
from bom_sphone s1, bom_sphone s2
where s1.parent_id = s2.item_id (+)
order by s1.item_id;
 
 
 
/* start with, connect by절을 이용한 계층형 쿼리를 할 수 있다 */
 
select lpad('*', 2*(level-1)) || item_name itemnames
from bom_sphone
start with parent_id is null /* 최상위 nod지정 */
connect by prior item_id = parent_id; 
 
 
 
/* 
    prior는 부모열을 찾아주는 의미 
    = connect by parent_id = prior item_id
*/
 
select level, lpad(' ', 4*(level-1)) || first_name||' '||last_name "이름"
from employees
start with manager_id is null /* 부모가 없으므로 최상위가 됨 */
connect by manager_id = prior employee_id;
 
 
select job.job_title, lpad(' ', 4*(level-1)) || emp.first_name||' '||emp.last_name "이름"
from employees emp, jobs job
where emp.job_id = job.job_id
start with emp.manager_id is null 
connect by emp.manager_id = prior emp.employee_id;