Spring 실습 (계층형 쿼리)

계층형 쿼리

계층형 쿼리 예시

노드(node) : 위 그림에서 원모양으로 표시된 항목을 노드라고 한다. 각각의 품목이 하나의 노드가 되며 실제 테이블에서는 하나의 로우에 대응

부모(parent) : 부모노드라고도 한다. 즉 트리구조에서 상위에 있는 노드를 말한다.

자식(child) : 자식노드라고도 한다. 위 그림에서 '모니터'는 컴퓨터의 자식노드이며, 컴퓨터는 모니터와 본체의 부모노드가 된다.

리프(leaf) : 리프노드라고도 한다. 더이상 하위에 연결된 노드가 없는 항목을 말한다. 즉 자식노드가 없는 노드라고 할수있으며 모니터, 프린터, 랜카드가 이에 해당된다.

루트(root) : 계층형, 트리구조에서 최상위에 있는 노드를 말한다. 컴퓨터가 이에 해당

레벨(level) : 트리구조에서의 각각의 계층을 말한다. 루트에 해당되는 '컴퓨터'가 1레벨이 되며, 순차적으로 그 하위에 있는 '모니터', '본체' 등이 2레벨이 된다.

--계층형 쿼리 연습
CREATE TABLE BOM (
     ITEM_ID INTEGER NOT NULL, -- 품목식별자
     PARENT_ID INTEGER, -- 상위품목 식별자
     ITEM_NAME VARCHAR2(20) NOT NULL, -- 품목이름
     ITEM_QTY INTEGER, -- 품목 개수
     PRIMARY KEY (ITEM_ID)
);

INSERT INTO BOM VALUES ( 1001, NULL, '컴퓨터', 1);
INSERT INTO BOM VALUES ( 1002, 1001, '본체', 1);
INSERT INTO BOM VALUES ( 1003, 1001, '모니터', 1);
INSERT INTO BOM VALUES ( 1004, 1001, '프린터', 1);
INSERT INTO BOM VALUES ( 1005, 1002, '메인보드', 1);
INSERT INTO BOM VALUES ( 1006, 1002, '랜카드', 1);
INSERT INTO BOM VALUES ( 1007, 1002, '파워서플라이', 1);
INSERT INTO BOM VALUES ( 1008, 1005, 'CPU', 1);
INSERT INTO BOM VALUES ( 1009, 1005, 'RAM', 1);
INSERT INTO BOM VALUES ( 1010, 1005, '그래픽카드', 1);
INSERT INTO BOM VALUES ( 1011, 1005, '기타장치', 1);

-- TREE JS
SELECT ITEM_ID, PARENT_ID
            , LEVEL
            , LPAD('ㄴ',2*(LEVEL-1)) || ITEM_NAME
            , ITEM_QTY
FROM     BOM
START WITH PARENT_ID IS NULL
CONNECT BY PRIOR ITEM_ID = PARENT_ID;

실행 결과

 

 

START WITH 조건1 ... CONNECT BY 조건2

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

• START WITH 조건1
  루트노드를 식별한다. 조건1을 만족하는 모든 ROW 들은 루트노드가 된다.
  START WITH절을 생략할수도 있는데 이러한 경우 모든 ROW들을 루트노드로 간주한다. 조건1에는 서브쿼리도 올수있다.
  
  
• CONNECT BY 조건2
  부모와 자식노드들 간의 관계를 명시하는 부분이다.
  START WITH과는 달리 조건2에서는 서브쿼리가 올수 없다.조건2에는 반드시 PRIOR연산자를 포함시켜야하며, 이는 부모노드의 컬럼을 식별하는데 사용된다. 
  
  
• PRIOR연산자 
  PRIOR키워드는 워직 계층형 쿼리에서만 사용하는 오라클 SQL연산자이다. 키워드라고 표현했지만 실제로는 CONNECT BY절에서 등호(=)와 동등한 레벨로 사용되는 연산자이며 CONNCET BY절에서 해당 컬럼의 부모로우를 식별하는데 사용된다. LEVEL도 일반적인 컬럼 처럼 SELECT, WHERE, ORDER BY 절에서 사용할수있다. 
  
  계층형 정보를 표현할 때 레벨을 나타낸다.
  
  
• 레벨 의사컬럼(LEVEL Pseudocolumn)
  앞의 예에서 본체의 PARENT_ID 컬럼에는 컴퓨터의 ITEM_ID값을 가지고 있으므로 PRIOR연산자는 ITEM_ID앞에 붙게 된다.