계층형 쿼리에서 제공되는 의사컬럼과 함수

앞서서 계층형 쿼리에 대해서 알아보았다.

https://fox-dev-diary.tistory.com/entry/%EA%B3%84%EC%B8%B5%ED%98%95%EC%BF%BC%EB%A6%AC

계층형쿼리

이번에는 위 글에 이어서 계층형 쿼리에서 제공되는 의사컬럼과 함수에 대해서 알아보자.

간단하게 정리하면 다음과 같다.

• 함수
  • CONNECT_BY_ROOT
  • SYS_CONNECT_BY_PATH(colm, char)
• 의사컬럼
  • LEVEL
  • CONNECT_BY_ISLEAF
  • CONNECT_BY_ISCYCLE

함수부터 차례로 알아보자

CONNECT_BY_ROOT

CONNECT_BY_ROOT는 계층형쿼리에서 최상위 로우를 반환하는 연산자이다.

SELECT department_id, LPAD(' ', 3*(LEVEL-1)) || department_name, LEVEL
     , CONNECT_BY_ROOT department_name AS root_name
  FROM departments
 START WITH parent_id IS NULL
 CONNECT BY PRIOR department_id = parent_id
 ORDER SIBLINGS BY department_name;

결과를 보면 모든 로우에 최상위 로우인 총무기획부를 출력하고 있다.

SYS_CONNECT_BY_PATH(colm, char)

SYS_CONNECT_BY_PATH는 루트 노드로부터 시작해 현재 데이터까지 연결된 경로 정보를 반환한다.

첫 번째 파라미터는 컬럼, 두 번째 파라미터는 구분자이다.

SELECT department_id, LPAD(' ', 3*(LEVEL-1)) || department_name, LEVEL
     , SYS_CONNECT_BY_PATH(department_name, '|')
  FROM departments
 START WITH parent_id IS NULL
 CONNECT BY PRIOR department_id = parent_id
 ORDER SIBLINGS BY department_name;

결과를 보면 각 로우별로 어떤 계층 경로를 타고 왔는지 알 수 있다.

여기서 주의할 점은 두 번째 매개변수인 구분자로 해당 컬럼에 포함된 문자는 사용할 수 없다는 것이다.

위 컬럼에서 '/' 문자를 사용하고 있는데 구분자로 '/'를 사용하게 되면 다음과 같은 에러가 뜬다.

ORA-30004: SYS_CONNECT_BY_PATH 함수를 사용할 때 열 값의 일부로 분리자를 사용할 수 없습니다

LEVEL

LEVEL은 전 글에서부터 사용해와서 자세하게 설명을 안해도 느낌이 오는 의사 컬럼이다.

루트 노드를 1로 시작하여 말그대로 계층의 레벨을 나타내는 것이다.(depth)

예시는 그 동안 써왔기 때문에 생략하겠다.

CONNECT_BY_ISLEAF

CONNECT_BY_ISLEAF는 해당 로우가 최하위 자식 로우(리프 노드)이면 1을, 아니면 0을 반환하는 의사컬럼이다.

SELECT department_id, LPAD(' ', 3*(LEVEL-1)) || department_name, LEVEL
     , CONNECT_BY_ISLEAF
  FROM departments
 START WITH parent_id IS NULL
 CONNECT BY PRIOR department_id = parent_id
 ORDER SIBLINGS BY department_name;

결과를 보면 자식 노드가 없는 최하위 노드(리프노드)에 1을 반환하고 나머지는 0이 나오는 것을 확인할 수 있다.

CONNECT_BY_ISCYCLE

CONNECT_BY_ISCYCLE은 주로 무한루프로 생긴 오류를 찾는데 사용된다.

기본적으로 계층형 쿼리는 루프(반복) 알고리즘을 사용한다.

계층형 구조나 레벨은 테이블에 있는 데이터에 따라 동적으로 변경되는데,

내부적으로는 루프를 돌면서 자식 노드를 찾아가는 것이다.

루프 알고리즘에서 주의할 점은 당연히 무한루프에 빠지게 되는 것이다.

계층형 쿼리에서도 부모-자식 간의 관계를 정의하는 값이 잘못 입력되면 무한루프에 빠지게 된다.

예시를 들어보자.

 

생산팀(170)의 부모 부서는 구매/생산부(30)인데,

구매/생산부(30)의 parent_id 값을 생산팀(170)으로 바꾸면 두 부서가 상호 참조가 되어 무한루프에 빠진다.

직접 실행해보자.

UPDATE departments
   SET parent_id = 170
 WHERE department_id = 30;
 
 SELECT department_id, LPAD(' ', 3*(LEVEL-1)) || department_name, LEVEL
     , parent_id
  FROM departments
 START WITH department_id = 30
CONNECT BY PRIOR department_id = parent_id;

ORA-01436: CONNECT BY의 루프가 발생되었습니다

해당 에러는 무한루프가 발생했다는 의미이다.

이런 상황이 오면 루프가 발생된 원인을 찾아서 데이터를 수정해야 한다.

이를 위해서 먼저 CONNECT BY 절에 NOCYCLE을 추가하고

SELECT절에 CONNECT_BY_ISCYCLE 의사 컬럼을 사용하여 문제인 지점을 찾을 수 있다.

CONNECT_BY_ISCYCLE은 현재 로우가 자식을 갖고 있는데,

동시에 그 자식 로우가 부모 로우이면 1을 아니면 0을 반환한다.

SELECT department_id, LPAD(' ', 3*(LEVEL-1)) || department_name as depname, LEVEL
     , CONNECT_BY_ISCYCLE IsLoop, parent_id
  FROM departments
 START WITH department_id = 30
 CONNECT BY NOCYCLE PRIOR department_id = parent_id;

생산팀(170)의 부모는 구매/생산부(30)이고,

구매/생산부(30)의 부모가 다시 생산팀(170)이 되어 IsLoop 컬럼 값이 1이 나왔다.

이런식으로 오류의 원인을 찾아 데이터를 수정하면 된다.