SQL CONNECT BY
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 CONNECT_BY 예시[편집]
1.1 CONNECT_BY_ISCYCLE 가상컬럼[편집]
The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0.
You can specify CONNECT_BY_ISCYCLE only if you have specified the NOCYCLE parameter of the CONNECT BY clause. NOCYCLE enables Oracle to return the results of a query that would otherwise fail because of a CONNECT BY loop in the data.
1.2 CONNECT_BY_ISLEAF 가상컬럼[편집]
SELECT last_name "Employee", CONNECT_BY_ISLEAF "IsLeaf",
LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM employees WHERE LEVEL <= 3 AND department_id = 80 START WITH employee_id = 100 CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4 ORDER BY "Employee", "IsLeaf";
1.3 LEVEL 가상컬럼[편집]
For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on. A root row is the highest row within an inverted tree. A child row is any nonroot row. A parent row is any row that has children. A leaf row is any row without children. Figure 3-1 shows the nodes of an inverted tree with their LEVEL values.
