[Oracle] 재귀쿼리 start with, connect by

오라클 계층 쿼리 사용 시 연결 조건

아래처럼 사용할 경우 부모 자식의 연결에서는 code_used 값이 체크되지 않고 전체 구성 후 where 값을 비교한다

select
  code_id, code_pid, code_used
  from code c
 where c.used = 1 -- 전체 결과에 대한 필터링
 start with code_pid = '' -- 시작 값
 connect by cod_pid = prior cod_id -- 자식 연결 조건

예시

계층 연결 후 code_used가 1인 것만 추출

이전

select code_id, code_pid, code_used, level lv from (
    select 'P' code_id, null code_pid, 1 code_used from dual
    union all
    select 'C1' code_id, 'P' code_pid, 0 code_used from dual
    union all
    select 'C10' code_id, 'C1' code_pid, 1 code_used from dual
) c
where code_used = 1
start with code_pid is null 
connect by code_pid = prior code_id 

select code_id, code_pid, code_used, level lv from (
    select 'P' code_id, null code_pid, 1 code_used from dual
    union all
    select 'C1' code_id, 'P' code_pid, 0 code_used from dual
    union all
    select 'C10' code_id, 'C1' code_pid, 1 code_used from dual
) c
start with code_pid is null and code_used = 1
connect by code_pid = prior code_id and code_used = 1

참고

• https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm