[Oracle] 오라클 - 계층형 질의 (Hierarchical Query)

 

[Oracle]
오라클 - 계층형 질의 (Hierarchical Query)

---

✔️ 계층형 질의

: 계층형 쿼리는 2차원 구조의 테이블로 저장된 데이터를 계층형 구조로 결과를 반환하는 쿼리다.

❌ LEVEL : CONNECT BY 줄이 있는 경우만 사용 가능하다.

 

 

-- 상위에서 하위로 출력

SELECT num, subject, LEVEL, parent
FROM soft
 START WITH num = 1      -- 출력을 시작할 최상위 행
 CONNECT BY PRIOR num = parent;

💡 PRIOR num = parent; -- 계층관계지정 = (나(num) 을 부모(parent) 로 사용하는 행)
-- prior : 이전행과 다음행 연결하는 역할
-- parent : 상위정보를 가진 컬럼!! null 이 최상위 부모
-- LEVEL : 검색된 결과의 계층별로 부여되는 번호  (같은 촌수)
--START WITH : 해당 번호 아래 자식들만 나옴. 최상위 부모로 설정안하면 최상위 부모의 자식들 안나옴
-- PRIOR num 을 아버지로 보는 컬럼들 나옴

 

 

-- 가시적으로 보는 계층형 질의

SELECT num, LPAD('   ', (LEVEL-1)*4) || subject subject, 
	LEVEL, parent
    FROM soft
    START WITH num = 1      -- 출력을 시작할 최상위 행
    CONNECT BY PRIOR num = parent;  -- 계층관계지정

💡 최상위 행 레벨 1
상위에서 하위로 내려가며 LPAD 로 자리에 공백 발생

 

 

-- 하위에서 상위로 출력 : 오라클 서적의 부모까지 거슬러올라가기

    SELECT num, LPAD('   ', (LEVEL-1)*4) || subject subject, 
	LEVEL, parent
    FROM soft
    START WITH num = 15      -- 출력을 시작할 최상위 행
    CONNECT BY PRIOR parent = num;  -- 계층관계지정 
			-- parent 의 아버지를 찾는 것

 

 

 

✔️ 계층형 질의 정렬하기

: 계층형 질의는 정렬할 때 ORDER BY 를 사용하지 않는다. 대신, ORDER BY 사이에 SIBLINGS 을 써서 같은 레벨끼리 정렬 가능하다.

SELECT num, subject, LEVEL, parent
    FROM soft
    START WITH num = 1
    CONNECT BY PRIOR num = parent
    ORDER SIBLINGS BY subject;

 

 

 

✔️ 계층형 질의 조건주기 (WHERE)

: 계층형 질의에서 WHER 조건절은 마지막에 평가된다. 

 

   SELECT num, LPAD('   ', (LEVEL-1)*4) || subject, 
	LEVEL, parent
    FROM soft
    WHERE num != 3
    START WITH num = 1
    CONNECT BY PRIOR num = parent  
 ORDER SIBLINGS BY subject;

💡 num 3 데이터는 빠지고 모두 출력된다.

 

 

⭐ 만약 WHERE 절에 조건을 주지 않고, CONNECT BY 절에 AND 조건을 준다면, 데이터베이스의 하위 데이터도 출력되지 않는다. (NUM 3의 데이터 뿐만 아니라 하위 데이터도 출력 안된다.)

    SELECT num, subject, LEVEL, parent
    FROM soft
    WHERE num != 3
    START WITH num = 1
    CONNECT BY PRIOR num = parent AND num !=3 ;

 

 

⭐ CONNECT_BY_ROOT : 최상위 출력 (subject 의 최상위)

: 최상위 부모가 누구인지 출력하고 싶을 때 쓴다. 

SELECT num, subject, parent, CONNECT_BY_ROOT subject
FROM soft
START WITH num=1
CONNECT BY PRIOR num=parent;

 

 

🔒 문제

-- 오늘부터 7일간 출력

SELECT SYSDATE + LEVEL -1 FROM dual CONNECT LEVEL <=7;