계층형 쿼리의 확장

게시일: Nov 07, 2011 7:55:5 AM

- 루트 노드 찾기 (CONNECT_BY_ROOT)

select level -- 계층형 구조에서의 각 레벨을 의미함 (루트노드가 레벨 1, 계층형 쿼리에서만 사용)

, product_name

, lpad(' ', 4*(level-1)) || product_name level_product_name

, connect_by_root product_id root_product_id

, connect_by_root product_name root_product_name

from (

select 1 product_id, null parent_id, '컴퓨터' product_name from dual union all

select 2 product_id, 1 parent_id, '본체' product_name from dual union all

select 3 product_id, 1 parent_id, '모니터' product_name from dual union all

select 4 product_id, 1 parent_id, '프린터' product_name from dual union all

select 5 product_id, 2 parent_id, 'Mother Board' product_name from dual union all

select 6 product_id, 2 parent_id, '랜카드' product_name from dual union all

select 7 product_id, 2 parent_id, 'Power Supply' product_name from dual union all

select 8 product_id, 5 parent_id, 'RAM' product_name from dual union all

select 9 product_id, 5 parent_id, 'CPU' product_name from dual union all

select 10 product_id, 5 parent_id, '그래픽 장치' product_name from dual union all

select 11 product_id, 5 parent_id, '기타 장치' product_name from dual

)

start with parent_id is null -- 루트노드 검색

connect by prior product_id = parent_id; -- 부모와 자식노드를 연결

-----------------------------------------------------------------------------------------

LEVEL PRODUCT_NAME LEVEL_PRODUCT_NAME ROOT_PRODUCT_ID ROOT_PRODUCT_NAME

-----------------------------------------------------------------------------------------

1 컴퓨터 컴퓨터 1 컴퓨터

2 본체 본체 1 컴퓨터

3 Mother Board Mother Board 1 컴퓨터

4 RAM RAM 1 컴퓨터

4 CPU CPU 1 컴퓨터

4 그래픽 장치 그래픽 장치 1 컴퓨터

4 기타 장치 기타 장치 1 컴퓨터

3 랜카드 랜카드 1 컴퓨터

3 Power Supply Power Supply 1 컴퓨터

2 모니터 모니터 1 컴퓨터

2 프린터 프린터 1 컴퓨터

-----------------------------------------------------------------------------------------

- 중복 참조값 찾기 (CONNECT_BY_ISCYCLE)

☞ 중복참고를 하는 경우 이를 판별하여 자식노드가 있을 경우 1을, 없을 경우 0을 반환

☞ connect_by_iscycle은 반드시 connect by 절에 nocycle이 명시되어 있어야 사용이 가능.

select level

, product_id

, connect_by_iscycle cycle

, lpad(' ', 4*(level-1)) || product_name level_product_name

from (

select 1 product_id, null parent_id, '컴퓨터' product_name from dual union all

select 2 product_id, 6 parent_id, '본체' product_name from dual union all

select 3 product_id, 1 parent_id, '모니터' product_name from dual union all

select 4 product_id, 1 parent_id, '프린터' product_name from dual union all

select 5 product_id, 2 parent_id, 'Mother Board' product_name from dual union all

select 6 product_id, 2 parent_id, '랜카드' product_name from dual union all

select 7 product_id, 2 parent_id, 'Power Supply' product_name from dual union all

select 8 product_id, 5 parent_id, 'RAM' product_name from dual union all

select 9 product_id, 5 parent_id, 'CPU' product_name from dual union all

select 10 product_id, 5 parent_id, '그래픽 장치' product_name from dual union all

select 11 product_id, 5 parent_id, '기타 장치' product_name from dual

)

start with product_id = 2

connect by nocycle prior product_id = parent_id;

------------------------------------------------

LEVEL PRODUCT_ID CYCLE LEVEL_PRODUCT_NAME

------------------------------------------------

1 2 0 본체

2 5 0 Mother Board

3 8 0 RAM

3 9 0 CPU

3 10 0 그래픽 장치

3 11 0 기타 장치

2 6 1 랜카드

2 7 0 Power Supply

------------------------------------------------

-> 잘못된 값으로 갱신된 데이터를 찾을 수 있다.

- 리프노드 찾기 (CONNECT_BY_ISLEAF)

☞ 리프노드에 해당할 경우 1을, 그렇지 않을경우 0을 반환

select product_id

, level

, connect_by_isleaf leafs

, lpad(' ', 4*(level-1)) || product_name level_product_name

from (

select 1 product_id, null parent_id, '컴퓨터' product_name from dual union all

select 2 product_id, 1 parent_id, '본체' product_name from dual union all

select 3 product_id, 1 parent_id, '모니터' product_name from dual union all

select 4 product_id, 1 parent_id, '프린터' product_name from dual union all

select 5 product_id, 2 parent_id, 'Mother Board' product_name from dual union all

select 6 product_id, 2 parent_id, '랜카드' product_name from dual union all

select 7 product_id, 2 parent_id, 'Power Supply' product_name from dual union all

select 8 product_id, 5 parent_id, 'RAM' product_name from dual union all

select 9 product_id, 5 parent_id, 'CPU' product_name from dual union all

select 10 product_id, 5 parent_id, '그래픽 장치' product_name from dual union all

select 11 product_id, 5 parent_id, '기타 장치' product_name from dual

)

start with parent_id is null

connect by prior product_id = parent_id;

-----------------------------------------------------

PRODUCT_ID LEVEL LEAFS LEVEL_PRODUCT_NAME

-----------------------------------------------------

1 1 0 컴퓨터

2 2 0 본체

5 3 0 Mother Board

8 4 1 RAM

9 4 1 CPU

10 4 1 그래픽 장치

11 4 1 기타 장치

6 3 1 랜카드

7 3 1 Power Supply

3 2 1 모니터

4 2 1 프린터

-----------------------------------------------------

- 경로 찾아가기 (SYS_CONNECT_BY_PATH)

☞ SYS_CONNECT_BY_PATH ( COLUMN, char )

☞ char : 구분자

select product_id

, level

, sys_connect_by_path(product_id, '/') id_path

, sys_connect_by_path(product_name, '/') name_path

, lpad(' ', 4*(level-1)) || product_name level_product_name

from (

select 1 product_id, null parent_id, '컴퓨터' product_name from dual union all

select 2 product_id, 1 parent_id, '본체' product_name from dual union all

select 3 product_id, 1 parent_id, '모니터' product_name from dual union all

select 4 product_id, 1 parent_id, '프린터' product_name from dual union all

select 5 product_id, 2 parent_id, 'Mother Board' product_name from dual union all

select 6 product_id, 2 parent_id, '랜카드' product_name from dual union all

select 7 product_id, 2 parent_id, 'Power Supply' product_name from dual union all

select 8 product_id, 5 parent_id, 'RAM' product_name from dual union all

select 9 product_id, 5 parent_id, 'CPU' product_name from dual union all

select 10 product_id, 5 parent_id, '그래픽 장치' product_name from dual union all

select 11 product_id, 5 parent_id, '기타 장치' product_name from dual

)

start with parent_id is null

connect by prior product_id = parent_id;

-----------------------------------------------------------------------------------------------

PRODUCT_ID LEVEL ID_PATH NAME_PATH LEVEL_PRODUCT_NAME

-----------------------------------------------------------------------------------------------

1 1 /1 /컴퓨터 컴퓨터

2 2 /1/2 /컴퓨터/본체 본체

5 3 /1/2/5 /컴퓨터/본체/Mother Board Mother Board

8 4 /1/2/5/8 /컴퓨터/본체/Mother Board/RAM RAM

9 4 /1/2/5/9 /컴퓨터/본체/Mother Board/CPU CPU

10 4 /1/2/5/10 /컴퓨터/본체/Mother Board/그래픽 장치 그래픽 장치

11 4 /1/2/5/11 /컴퓨터/본체/Mother Board/기타 장치 기타 장치

6 3 /1/2/6 /컴퓨터/본체/랜카드 랜카드

7 3 /1/2/7 /컴퓨터/본체/Power Supply Power Supply

3 2 /1/3 /컴퓨터/모니터 모니터

4 2 /1/4 /컴퓨터/프린터 프린터

-----------------------------------------------------------------------------------------------