[ORACLE]계층형 쿼리 Hierarchical Query

계층형 쿼리 Hierarchical Query

 

평등한 관계가 아닌 계급적이고 수직적인 구조.

 

 

노드

각 품목이 하나의 노드가 되며 실제 테이블에서는 하나의 로우에 대응

부모

트리주고에서 상위노드. 

자식

트리구조에서 하위노드

리프

자식노드가 없는 최하위 노드

레벨

트리구조에서 각각의 계층. 루트= 레벨1

 

 

계층형 쿼리작성 

[DDL]

CREATE TABLE BOM (

     ITEM_ID INTEGER NOT NULL, -- 품목식별자

     PARENT_ID INTEGER, -- 상위품목 식별자

     ITEM_NAME VARCHAR2(20) NOT NULL, -- 품목이름

     ITEM_QTY INTEGER, -- 품목 개수

     PRIMARY KEY (ITEM_ID)

);

 

1. 일반 조인 사용한 계층형 쿼리.

SELECT b1.item_name, b1.item_id, b2.parent_item FROM bom b1, bom b2

WHERE b1.parent_id = b2 =item_id(+) ORDER BY b1.item_id; 

 

테이블수는 하나이고 각각 품목 정보는 부모-자식관계로 연결되어 있으므로 셀프조인 사용.

로트노드에 해당하는 컴퓨터의 경우 최상위 레벨 1에 속하므로, parent_id 컬럼값은  null이다.

 

 

2. START WITH ~ CONNECT BY PRIOR  

사용한 계층형 쿼리 

 

SELECT  item_name, item_id, parent_id FROM bom 

START WITH parent_id IS NULL  -- 루트노트를 지정 

CONNECT BY PRIOR item_id = parent_id ;  -- 부모-자식 노드간 관계 연결.

 

** 레벨들여쓰기 

SELECT LPAD(' ', 2*(LEVEL-1)) || item_name item_names,

       

 item_id,         parent_id

FROM bom

START WITH parent_id IS NULL 

CONNECT BY PRIOR item_id = parent_id;

 

** PRIOR : 자식노드 앞에 붙어야함!!! 

 

CONNECT BY PRIOR item_id = parent_id;  또는 

CONNECT BY  parent_id = PRIOR item_id; 

 

 

계층형 쿼리의 확장 

오라클에서는 계층형 쿼리에서 추가적인 정보 제공하기 위한 몇가지 키워드를 더 제공한다.

 

CONNECT_BY_ROOT  루트노드찾기 

 

CONNECT_BY_ISCYCLE  중복 참조값 찾기. 

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

 

CONNECT_BY_ISLEAF 리프노드 찾기 

 

SYS_CONNECT_BY_PATH 루트 찾아가기