계층형 쿼리

/* 계층형 쿼리(오라클에만 있음) */

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, '메인pcb');
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_id, s1.item_name, s2.item_name parent_name
from bom_sphone s1, bom_sphone s2
where s1.parent_id = s2.item_id (+)
order by item_id;


-- start with, connect by 절을 이용하여 계층형 쿼리 가능
select lpad('*', 2*(level-1)) || item_name itemnames
from bom_sphone
start with parent_id is null
connect by prior item_id = parent_id; -- 연결하겠다는 의미?
-- connect by parent_id = prior item_id; -- 위와 같은 의미


-- 조인 이용하여 직급별 계층형 쿼리
select level, jobs.job_title 직위,
      lpad(' ', 4*(level-1)) || emp.first_name || ' ' || emp.last_name 이름
from employees emp, jobs
where emp.job_id = jobs.job_id
start with emp.manager_id is null
connect by emp.manager_id = prior emp.employee_id;