[ex26] Hierarchical Query

ex26_hierarchical
    
    계층형 쿼리, Hierarchical Query
    - 오라클 전용
    - 레코드 관계가 서로 상하 수직 구조일때 사용
    - 서로 다른 테이블간의 관계가 수직 구조일때 사용
    - 자기 참조를 하는 테이블에서 사용 
    - ex) 카테고리, 답변형 게시판, 조직도 등.. 트리 구조에 사용
    
    컴퓨터
        - 본체
            - 메인보드
            - 그래픽카드
            - CPU
            - 메모리
        - 모니터
            - 모니터암
            - 보호필름

 

-- Case 1.
create table tblComputer (
    seq number primary key,                                --식별자(PK)
    name varchar2(50) not null,                            --부품명
    qty number not null,                                   --수량
    pseq number null references tblComputer(seq)           --부모부품(FK)
    
);

insert into tblComputer values (1, '컴퓨터', 1, null);

insert into tblComputer values (2, '본체', 1, 1);
insert into tblComputer values (3, '메인보드', 1, 2);
insert into tblComputer values (4, '그래픽카드', 1, 2);
insert into tblComputer values (5, 'CPU', 1, 2);
insert into tblComputer values (6, '메모리', 2, 2);

insert into tblComputer values (7, '모니터', 1, 1);
insert into tblComputer values (8, '모니터암', 1, 7);
insert into tblComputer values (9, '보호필름', 1, 7);




-- Case 2.
-- 계층형 쿼리 못씀(자기참조형에서만 사용 가능)
create table tblCategoryBig (
    seq number primary key,                 --식별자(PK)
    name varchar2(100) not null             --카테고리명
);

create table tblCategoryMedium (
    seq number primary key,                             --식별자(PK)
    name varchar2(100) not null,                        --카테고리명
    pseq number not null references tblCategoryBig(seq) --부모카테고리(FK)
);

create table tblCategorySmall (
    seq number primary key,                                 --식별자(PK)
    name varchar2(100) not null,                            --카테고리명
    pseq number not null references tblCategoryMedium(seq)  --부모카테고리(FK)
);


insert into tblCategoryBig values (1, '카테고리');

insert into tblCategoryMedium values (1, '컴퓨터용품', 1);
insert into tblCategoryMedium values (2, '운동용품', 1);
insert into tblCategoryMedium values (3, '먹거리', 1);

insert into tblCategorySmall values (1, '하드웨어', 1);
insert into tblCategorySmall values (2, '소프트웨어', 1);
insert into tblCategorySmall values (3, '소모품', 1);

insert into tblCategorySmall values (4, '테니스', 2);
insert into tblCategorySmall values (5, '골프', 2);
insert into tblCategorySmall values (6, '달리기', 2);

insert into tblCategorySmall values (7, '밀키트', 3);
insert into tblCategorySmall values (8, '베이커리', 3);
insert into tblCategorySmall values (9, '도시락', 3);



-- Case 1.
-- tblComputer
-- 1. 조인
select
    c1.name as "부품명",
    c2.name as "부모부품명"
from tblComputer c1
    inner join tblComputer c2
    
        on            c2.seq = c1.pseq;
      --connect by prior seq = pseq; (위랑 같은 역할)

-- 2. 계층형 쿼리
/*
    
    구문
    - start with절 + connect by절
    
    계층형 쿼리 의사 컬럼
    a. prior > 자기와 연관된 부모 레코드 참조
    b. level > 세대수(depth) 

*/

select
    seq,
    lpad(' ', (level-1) * 5) || name, --level 사용해서 시각적효과
    prior name,
    level
from tblComputer
    --start with seq = 1 --결과셋의 루트 지정
    --start with seq = 7 --모니터 부터 가져옴
    --start with seq = (select seq from tblComputer where name = '본체') --본체만 가져옴
    start with pseq is null --최상위 계층 가져옴
        connect by prior seq = pseq; --현재 레코드와 부모 레코드를 연결(조인 on 역할)

select * from tblSelf;

select 
    lpad(' ', (level-1) * 2) || name as "직원명"
from tblSelf
    start with seq = 1
        connect by super = prior seq;

------------------

select * from tblCategoryBig;
select * from tblCategoryMedium where pseq = 1;
select * from tblCategorySmall where pseq = 2;

--결과에서 상위 카테고리 반복 싫으면 select 3번 해야됨. join하면 중복은 필연
select
    *
from tblCategoryBig b
    inner join tblCategoryMedium m
        on b.seq = m.pseq
            inner join tblCategorySmall s
                on m.seq = s.pseq;