재귀 쿼리(Recursive Query)

트리 구조로 되어있는 데이터가 있다.

LV1은 학교 LV2는 학년 LV3는 반 LV4에는 학생이 저장되어 있다고 하자.

예시 데이터

• A학교 (LV1)
  • 1학년 (LV2)
    • 1반 (LV3)
      • 김민준 (학생)
      • 박서준 (학생)
      • 이하은 (학생)
    • 2반 (LV3)
      • 정수민 (학생)
      • 최지우 (학생)
      • 한서연 (학생)
  • 2학년 (LV2)
    • 1반 (LV3)
      • 이지훈 (학생)
      • 김예림 (학생)
      • 윤채원 (학생)
    • 2반 (LV3)
      • 박지훈 (학생)
      • 이서연 (학생)
      • 최민서 (학생)
• B학교 (LV1)
  • 1학년 (LV2)
    • 1반 (LV3)
      • 김지영 (학생)
      • 박재현 (학생)
      • 장윤서 (학생)

만약 데이터가 변경되지 않는다면, 굳이 재귀를 사용하지 않고 SQL문을 작성해도 원하는 결과를 얻을 수 있겠지만,

데이터가 변경된다면, 쿼리도 바꿔줘야 하는 귀찮음을 겪을 것이다.

예를 들어 LV5에 학생의 가족 구성원이 생겼다 하자.
그렇게 된다면 LV5에 대한 쿼리를 다시 작성해야 할 것이다.

또한 쿼리는 매우 길어질 것이다. 

결국 확장성과 가독성, 유지보수성을 위해 재귀 쿼리를 사용하는 것이 좋다.

 

내가 가진 테이블은 아래와 같이 구성되어 있다

 

• ALLINFO: 여러 데이터가 저장된 테이블
• GUIDXX: 데이터의 고유 식별자입니다. 각 행을 유일하게 구분하는 고유한 ID라고 생각하면 됩니다. 재귀 쿼리에서 부모-자식 관계를 연결할 때 자식의 UPIDXX와 연결됩니다.
• UPIDXX: 부모 데이터의 고유 식별자입니다. 이 컬럼은 해당 행이 속한 상위 레벨(부모) 데이터의 GUIDXX를 가리킵니다. 이 컬럼을 통해 계층 구조를 역추적하거나, 재귀적으로 하위 데이터를 찾아낼 수 있습니다.
• ROOTTP: 트리의 최상위 루트 유형을 나타냅니다. 쿼리에서는 'SCHOOL'라는 특정 유형의 트리 데이터만 필터링하는 데 사용됩니다.
• CODELV: **데이터의 레벨(단계)**을 의미합니다. 트리의 깊이를 나타내는 것으로, 1이 최상위 레벨(학교), 2가 다음 레벨(학년)처럼 단계별로 숫자가 증가합니다.
• CODEVL: 데이터의 고유 코드 값입니다. 예를 들어 '0001', '0002'처럼 각 레벨의 데이터를 나타내는 실제 코드 값입니다. SORT_CODEVL을 만들 때 이 값을 사용합니다.
• CODENM: 데이터의 이름입니다. CODEVL에 해당하는 '학교', '1학년', '1반', '김민준' 같은 이름을 저장합니다.
• CODEOR: 데이터의 정렬 순서를 나타냅니다. 같은 레벨 내에서 데이터가 표시될 순서를 정하는 데 사용됩니다.
• USEYNX: 데이터의 사용 여부를 나타냅니다. 'Y'는 사용 중, 'N'은 사용하지 않음을 의미하며, 쿼리에서는 'Y'인 데이터만 조회합니다.

위 정보를 토대로 아래처럼 쿼리를 짜면 재귀를 사용하지 않을 때 보다 훨씬 관리하기 편해질 것이다.

WITH RECURSIVE CTE AS (
    SELECT COMPCD
           ,GUIDXX
           ,UPIDXX
           ,ROOTTP
           ,CODELV
           ,CODEVL
           ,CODENM
           ,CODEOR
           ,CAST(CODEVL AS VARCHAR(500)) AS "SORT_CODEVL"
     FROM  CMMDMT
    WHERE  1 = 1
      AND  COMPCD = ${param(_compcd)}
      AND  ROOTTP = 'SCHOOL'
      AND  USEYNX = 'Y'
      AND  CODELV = 1

    UNION ALL

    SELECT  CMT.COMPCD AS "COMPCD"
           ,CMT.GUIDXX AS "GUIDXX"
           ,CMT.UPIDXX AS "UPIDXX"
           ,CMT.ROOTTP AS "ROOTTP"
           ,CMT.CODELV AS "CODELV"
           ,CMT.CODEVL AS "CODEVL"
           ,CMT.CODENM AS "CODENM"
           ,CMT.CODEOR AS "CODEOR"
           ,CONCAT(C.SORT_CODEVL, '>', CMT.CODEVL) AS "SORT_CODEVL"
     FROM  CMMDMT CMT
     INNER JOIN CTE C ON (C.GUIDXX = CMT.UPIDXX)
    WHERE  1 = 1
      AND  CMT.COMPCD = ${param(_compcd)}
      AND  CMT.ROOTTP = 'SCHOOL'
      AND  CMT.USEYNX = 'Y'
)
SELECT *
 FROM  CTE
  ORDER BY SORT_CODEVL;

 

 

1. UNION ALL 위쪽의 첫 번째 SELECT 문입니다. 이 부분은 재귀의 시작점 역할을 합니다. ROOTTP가 'SCHOOL'이고 CODELV가 1인 최상위 레벨 데이터를 가져온다.
2. 1번에서 찾은 GUIDXX를 바탕으로 하위 데이터를 찾는다.
3. 더이상 하위 데이터를 찾지 못할 때 까지 반복한다.
4. SORT_CODEVL을 통해 정렬한다. SORT_CODEVL은 학교코드>학년코드>반코드>학생코드 같이 정렬하기 쉽게 저장되어 있다.

또한 USEYNX를 통해 필요한 데이터만 순차적으로 탐색할 수 있다.