계층형쿼리

계층형 쿼리

example)

create table bom_phone(
    item_id number(3) not null,
    parent_id number(3),
    item_name varchar2(20) not null,
    primary key(item_id)
);
insert into bom_phone values(100, null, '스마트폰');
insert into bom_phone values(101, 100, '메인PCB');
insert into bom_phone values(102, 100, '배터리');
insert into bom_phone values(103, 101, 'CPU');
 

select s1.item_name, s1.item_id, s2.item_name parent_name
from bom_phone s1, bom_phone s2
where s1.parent_id = s2.item_id (+1)
order by s1.item_id:

<start with, connect by 절을 이용한 계층형 쿼리>

select lpad(' ', 2*(level-1)) || item_name as itemnames
from bom_phone
start with parent_id is null
connect by prior item_id = parent_id;