connect by

2012. 12. 17. 11:17

START WITH ... CONNECT BY 절

설명

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

구문

SELECT column_list

FROM table_joins | tables

[WHERE join_conditions and/or filtering_conditions]

[START WITH condition]

CONNECT BY [NOCYCLE] 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 테이블

ID	MgrID	Name	BirthYear
1	NULL	Kim	1963
2	NULL	Moy	1958
3	1	Jonas	1976
4	1	Smith	1974
5	2	Verma	1973
6	2	Foster	1972
7	6	Brown	1981

tree_cycle 테이블

ID	MgrID	Name
1	NULL	Kim
2	11	Moy
3	1	Jonas
4	1	Smith
5	3	Verma
6	3	Foster
7	4	Brown
8	4	Lin
9	2	Edwin
10	9	Audrey
11	10	Stone

-- 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);

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

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

INSERT INTO tree_cycle VALUES (1,NULL,'Kim');

INSERT INTO tree_cycle VALUES (2,11,'Moy');

INSERT INTO tree_cycle VALUES (3,1,'Jonas');

INSERT INTO tree_cycle VALUES (4,1,'Smith');

INSERT INTO tree_cycle VALUES (5,3,'Verma');

INSERT INTO tree_cycle VALUES (6,3,'Foster');

INSERT INTO tree_cycle VALUES (7,4,'Brown');

INSERT INTO tree_cycle VALUES (8,4,'Lin');

INSERT INTO tree_cycle VALUES (9,2,'Edwin');

INSERT INTO tree_cycle VALUES (10,9,'Audrey');

INSERT INTO tree_cycle VALUES (11,10,'Stone');

-- 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

4 1 Smith

5 2 Verma

6 2 Foster

7 6 Brown

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

SELECT id, mgrid, name

FROM tree

START WITH mgrid IS NULL

CONNECT BY prior id=mgrid