게시일: 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 /컴퓨터/프린터 프린터
-----------------------------------------------------------------------------------------------