계층형 쿼리(Hierarchical Query)

ex)tblComputer 테이블

컴퓨터 

    -본체

        -메인보드

        -그래픽카드

        -랜카드

        -메모리

    -모니터

        -보호필름

    -프린터

        -잉크 카트리지

        -A4 용지

create table tblComputer

(

    seq number primary key, --PK

    name varchar2(100) not null, --요소명

    qty number not null, --수량

    pseq number references tblComputer(seq) --부모부품(FK)

);

1. 셀프 조인

: inner join or outer join > 동일한 테이블을 가지고 실행 

select * from tblComputer c1 

inner join tblComputer c2

on c1.seq = c2.pseq; -- c1(부모) , c2(자식)

2. 계층형 쿼리

: start with 절 connect by 절

select * from tblComputer

start with seq = 1 -루트 노느 지정

connect by prior seq = pseq; -부모 자식 연결 고리(PK=FK)

**계층형 쿼리의 의사 컬럼(level)

select name,level from tblComputer 

start with seq =1

connect by prior seq = pseq;

**계층형 쿼리의 의사컬럼(prior) : 부모 레코드 참조 

<prior (부모레코드 컬럼 이름)>

select name,level, prior qty from tblComputer 

start with seq =1

connect by prior seq = pseq;

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

-- 계층형 게시판

select 

    lpad(' ',(level-1) *5) || name as "현재 카테고리",

    prior name as "부모 카테고리",

    connect_by_root name as "루트 카테고리",--자기가 속한 그룹의 루트

    connect_by_isleaf ,--0 : 자식이 있는 /1 : 자식이 없는

    sys_connect_by_path(name,'▷') as "경로" --계층구조 경로 (path) // sys_connect_by_path(컬럼명,'구분자')

    from tblCategory

    start with pseq is null

        connect by prior seq = pseq

            order siblings by name asc;

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

[START WITH 조건1] [CONNECT BY 조건2]

- START WITH 조건1 : 루트노드를 식별한다. 조건1을 만족하는 모든 ROW 들은 루트노드가 된다./ 조건1에는 서브쿼리도 올수있다.

- CONNECT BY 조건2 : 부모와 자식노드들 간의 관계를 명시하는 부분이다./

 조건2에는 반드시 PRIOR연산자를 포함시켜야 하며, 이는 부모노드의 컬럼을 식별하는데 사용된다./ 조건2에는 서브쿼리가 올수 없다.

*PRIOR연산자

- 오직 계층형 쿼리에서만 사용하는 오라클 SQL연산자이다. 

- CONNECT BY절에서 =와 동등한 레벨로 사용되는 연산자이며, CONNECT BY절에서 해당 컬럼의 부모ROW를 식별하는데 사용된다.

*LEVEL 의사컬럼

- 계층형 정보를 표현할 때 레벨을 나타낸다.

- 일반적인 컬럼처럼 SELECT, WHERE, OREDER BY 절에서 사용할 수 있다.

*CONNECT_BY_ROOT 컬럼

- 루트노드 찾기

*CONNECT_BY_ISCYCLE

- 중복 참조값 찾기

- 반드시 CONNECT BY절에 NOCYCLE이 명시되어 있어야 사용이 가능하다.

*CONNECT_BY_ISLEAF

- 리프노드 찾기 

- 리프노드에 해당하면 1 / 그렇지 않으면 0을 반환

*SYS_CONNECT_BY_PATH 

- 루트 찾아가기