START WITH ... CONNECT BY 절

설명

계층적 질의란 테이블에 포함된 행(row)간에 수직적 계층 관계가 성립되는 데이터에 대하여 계층 관계에 따라 각 행을 출력하는 질의이다. START WITH ... CONNECT BY 절은 SELECT 구문과 결합하여 사용된다.

CONNECT BY ... START WITH로 두 절의 순서를 바꿔서 사용할 수도 있다.

구문

SELECT column_list

    FROM table_joins | tables

    [WHERE join_conditions and/or filtering_conditions]

    [hierarchical_clause]

 

hierarchical_clause :

    [START WITH condition] CONNECT BY [NOCYCLE] condition

    | CONNECT BY [NOCYCLE] condition [START WITH condition]

START WITH 절

START WITH 절은 계층 관계가 시작되는 루트 행(root row)을 지정하기 위한 것으로, START WITH 절 다음에 계층 관계를 검색하기 위한 조건식을 포함한다. 만약, START WITH 절에 다음에 위치하는 조건식이 생략되면 대상 테이블 내에 존재하는 모든 행을 루트 행으로 간주하여 계층 관계를 검색할 것이다.

참고 START WITH 절이 생략되거나, START WITH 조건식을 만족하는 결과 행이 존재하지 않는 경우, 테이블 내의 모든 행을 루트 행으로 간주하여 각 루트 행에 속하는 하위 자식 행들 간 계층 관계를 검색하므로 결과 행들 중 일부는 중복되어 출력될 수 있다.

CONNECT BY [NOCYCLE] PRIOR 절

• PRIOR : CONNECT BY 조건식은 한 쌍의 행에 대한 상-하 계층 관계(부모-자식 관계)를 정의하기 위한 것으로, 조건식 내에서 하나는 부모(parent)로 지정되고, 다른 하나는 자식(child)으로 지정된다. 이처럼 행 간의 부모-자식 간 계층 관계를 정의하기 위하여 CONNECT BY 조건식 내에 PRIOR 연산자를 이용하여 부모 행의 칼럼 값을 지정한다. 즉, 부모 행의 칼럼 값과 같은 칼럼 값을 가지는 모든 행은 자식 행이 된다.
• NOCYCLE : CONNECT BY 절의 조건식에 따른 계층 질의 결과는 루프를 포함할 수 있으며, 이것은 계층 트리를 생성할 때 무한 루프를 발생시키는 원인이 될 수 있다. 따라서, CUBRID는 루프를 발견하면 기본적으로 오류를 반환하고, 특수 연산자인 NOCYCLE이 CONNECT BY 절에 명시된 경우에는 오류를 발생시키지 않고 해당 루프에 의해 검색된 결과를 출력한다.
  만약, CONNECT BY 절에서 NOCYCLE이 명시되지 않은 계층 질의문을 수행 중에 루프가 감지되는 경우, CUBRID는 오류를 반환하고 해당 질의문을 취소한다. 반면, NOCYCLE이 명시된 계층 질의문에서 루프가 감지되는 경우, CUBRID는 오류를 반환하지는 않지만 루프가 감지된 행에 대해 CONNECT_BY_ISCYCLE 값을 1로 설정하고, 더 이상 계층 트리의 검색을 확장하지 않을 것이다.

예제

다음은 계층 질의문을 수행하는 예제이다.

-- tree 테이블을 만들고 데이터를 삽입하기

CREATE TABLE tree(ID INT, MgrID INT, Name VARCHAR(32), BirthYear INT);

 

INSERT INTO tree VALUES (1,NULL,'Kim', 1963);

INSERT INTO tree VALUES (2,NULL,'Moy', 1958);

INSERT INTO tree VALUES (3,1,'Jonas', 1976);

INSERT INTO tree VALUES (4,1,'Smith', 1974);

INSERT INTO tree VALUES (5,2,'Verma', 1973);

INSERT INTO tree VALUES (6,2,'Foster', 1972);

INSERT INTO tree VALUES (7,6,'Brown', 1981);

 

-- CONNECT BY 절을 이용하여 계층 질의문 수행하기

SELECT id, mgrid, name

    FROM tree

    CONNECT BY PRIOR id=mgrid

    ORDER BY id;

 

id  mgrid       name

======================

1   null        Kim

2   null        Moy

3   1       Jonas

3   1       Jonas

4   1       Smith

4   1       Smith

5   2       Verma

5   2       Verma

6   2       Foster

6   2       Foster

7   6       Brown

7   6       Brown

7   6       Brown

 

-- START WITH 절을 이용하여 계층 질의문 수행하기

SELECT id, mgrid, name

    FROM tree

    START WITH mgrid IS NULL

    CONNECT BY prior id=mgrid

    ORDER BY id;

 

id  mgrid       name

=============================

1   null        Kim

2   null        Moy

3   1       Jonas

4   1       Smith

5   2       Verma

6   2       Foster

7   6       Brown