CONNECT_BY_ISCYCLE
CONNECT BY 중복참조를 체크해주는 함수
CONNECT BY NOCYCLE 를 사용할 경우에만 사용가능
중복참조의 경우 "ORA-01436 CONNECT BY의 루프가 발생되었습니다." 발생
- 정상적인 CONNECT BY
WITH CV_MST AS (
SELECT '0001' AS P_ITEM_NO, '0003' AS C_ITEM_NO, 'PARENT' AS ITEM_NM FROM DUAL UNION ALL
SELECT '0003' AS P_ITEM_NO, '0005' AS C_ITEM_NO, 'CHILD1' AS ITEM_NM FROM DUAL UNION ALL
SELECT '0003' AS P_ITEM_NO, '0006' AS C_ITEM_NO, 'CHILD2' AS ITEM_NM FROM DUAL UNION ALL
SELECT '0003' AS P_ITEM_NO, '0007' AS C_ITEM_NO, 'CHILD3' AS ITEM_NM FROM DUAL UNION ALL
SELECT '0003' AS P_ITEM_NO, '0008' AS C_ITEM_NO, 'CHILD4' AS ITEM_NM FROM DUAL
)
SELECT CM.*
, LEVEL
FROM CV_MST CM
CONNECT BY PRIOR C_ITEM_NO = P_ITEM_NO
START WITH P_ITEM_NO = '0001'
상호참조가 되게 수정하여 ORA-01436 발생시킴
WITH CV_MST AS (
SELECT '0001' AS P_ITEM_NO, '0003' AS C_ITEM_NO, 'PARENT' AS ITEM_NM FROM DUAL UNION ALL
SELECT '0003' AS P_ITEM_NO, '0005' AS C_ITEM_NO, 'CHILD1' AS ITEM_NM FROM DUAL UNION ALL
SELECT '0003' AS P_ITEM_NO, '0001' AS C_ITEM_NO, 'CHILD2' AS ITEM_NM FROM DUAL UNION ALL
SELECT '0003' AS P_ITEM_NO, '0007' AS C_ITEM_NO, 'CHILD3' AS ITEM_NM FROM DUAL UNION ALL
SELECT '0003' AS P_ITEM_NO, '0008' AS C_ITEM_NO, 'CHILD4' AS ITEM_NM FROM DUAL
)
SELECT CM.*
, LEVEL
FROM CV_MST CM
CONNECT BY PRIOR C_ITEM_NO = P_ITEM_NO
START WITH P_ITEM_NO = '0001'
NOCYCLE 를 이용하여 오류 없이 상호참조 확인
WITH CV_MST AS (
SELECT '0001' AS P_ITEM_NO, '0003' AS C_ITEM_NO, 'PARENT' AS ITEM_NM FROM DUAL UNION ALL
SELECT '0003' AS P_ITEM_NO, '0005' AS C_ITEM_NO, 'CHILD1' AS ITEM_NM FROM DUAL UNION ALL
SELECT '0003' AS P_ITEM_NO, '0001' AS C_ITEM_NO, 'CHILD2' AS ITEM_NM FROM DUAL UNION ALL
SELECT '0003' AS P_ITEM_NO, '0007' AS C_ITEM_NO, 'CHILD3' AS ITEM_NM FROM DUAL UNION ALL
SELECT '0003' AS P_ITEM_NO, '0008' AS C_ITEM_NO, 'CHILD4' AS ITEM_NM FROM DUAL
)
SELECT CM.*
, LEVEL
, CONNECT_BY_ISCYCLE CBI
FROM CV_MST CM
CONNECT BY NOCYCLE PRIOR C_ITEM_NO = P_ITEM_NO
START WITH P_ITEM_NO = '0001'
참고 URL
http://cafe.daum.net/_c21_/bbs_search_read?grpid=1DpW4&fldid=MZXA&datanum=5