connect by .. start with 문......
오라클 고급 쿼리중에는 Connect by 라는 기능이 있다. 이 기능은 계층형 자료를 표현하는데 편리함을 더해주며, 주로 조직도 등을 표현하는데 사용되고 있다.
[예] 다음과 같은 정보가 DB 내에 입력되어 있다고 할때...
ID Dept_name parentID
--------------------------------------
0000 한국대학교 0
1000 인문대학 0000
1100 국어국문학과 1000
1200 문화정보학부 1000
1210 철학과 1200
1220 문헌정보학과 1200
1230 역사학과 1200
2000 공과대학 0000
2100 전기전자컴퓨터공학부 2000
2110 전기공학과 2100
2120 전자공학과 2100
2130 컴퓨터과학과 2100
일반적으로 사용하는 조직도라고 할때, 이 내용을 계층형 자료로 표현하는데는 쉽지않다. 심지어 MS-SQL이나 Sybase 는 프로시저를 만들어 써야 한다. 하지만 오라클에서는 Connect by 라는 간단한 쿼리를 제공한다.
Select ID, Dept_name, parentID from department
Connect by prior ID=parentID
Start with ID='2130'
[결과값]
ID Dept_name parentID
--------------------------------------
0000 한국대학교 0
2000 공과대학 0000
2100 전기전자컴퓨터공학부 2000
2130 컴퓨터과학과 2100
이때 Connect by 문은 자식노드와 부모노드를 연결해주며 Start with 조건에 검색할 내용을 입력하면 된다.
하지만 이때 유의할점은 Connect by loop 에러의 발생이다.
위의 예제에서 만약 한국대학교의 parentID 가 0 이 아니고 2100 이라면...?
컴퓨터과학과->전기전자컴퓨터공학부->공과대학->한국대학교->컴퓨터과학과->전기전자... 이런식으로 무한루프에 빠질 가능성이 있다. 이러한 오류를 Connect by loop라고 한다. Connect by loop는 말 그대로 계층형 쿼리를 만드는 도중 무한루프에 빠져버리는 현상을 말하는 것이다.
이런 현상을 검증하기 위하여 오라클 10g 이상의 버전에서는 다음과 같은 방법을 제공한다.
Select Dept_name, CONNECT_BY_ISCYCLE from department
Connect by NOCYCLE prior ID=parentID
Start with ID='2130'
위의 쿼리처럼 CONNECT_BY_ISCYCLE / NOCYCLE을 이용하여 쿼리를 작성하면 CONNECT_BY_ISCYCLE 값은 정상일경우 0, 무한루프에 빠질경우 1을 반환하고 모든 결과물은 단 한번만 나타나게 된다.
[출처] Connect by 쿼리, 그리고 Loop 검증|작성자 도토리