Hierarchical
Queries
If a table contains hierarchical data, then you can select
rows in a hierarchical order using the hierarchical query clause:
START
WITH
specifies the root row(s) of the
hierarchy.CONNECT
BY
specifies the relationship between
parent rows and child rows of the hierarchy.
Both the
CONNECT
BY
condition and the PRIOR
expression can take the
form of an uncorrelated subquery. However, CURRVAL
and NEXTVAL
are not valid
PRIOR
expressions, so
the PRIOR
expression
cannot refer to a sequence.
SELECT ename, empno, mgr, LEVEL
FROM emp
START WITH empno = 7839
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename;
FROM emp
START WITH empno = 7839
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename;
SELECT ename, empno, mgr, LEVEL,sys_connect_by_path(ename,'/')
FROM emp
START WITH empno = 7839
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename;
FROM emp
START WITH empno = 7839
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename;
CONNECT_BY_ISCYCLE Pseudocolumn
TheCONNECT_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.CONNECT_BY_ISLEAF Pseudocolumn
TheCONNECT_BY_ISLEAF
pseudocolumn returns 1 if the current row is a leaf of the tree defined by the CONNECT
BY
condition. Otherwise it returns 0. This information indicates whether a
given row can be further expanded to show more of the hierarchy.CONNECT_BY_ISLEAF Example The following example shows the first three levels of the
hr.employees
table, indicating for each row whether it is a leaf row (indicated by 1 in the IsLeaf
column) or whether it has child rows (indicated by 0 in the IsLeaf
column):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; Employee IsLeaf LEVEL Path --------------- ---------- ---------- ----------------------------------- Russell 0 2 /King/Russell Tucker 1 3 /King/Russell/Tucker Bernstein 1 3 /King/Russell/Bernstein Hall 1 3 /King/Russell/Hall Olsen 1 3 /King/Russell/Olsen Cambrault 1 3 /King/Russell/Cambrault Tuvault 1 3 /King/Russell/Tuvault Partners 0 2 /King/Partners King 1 3 /King/Partners/King Sully 1 3 /King/Partners/Sully McEwen 1 3 /King/Partners/McEwen Smith 1 3 /King/Partners/Smith Doran 1 3 /King/Partners/Doran Sewall 1 3 /King/Partners/Sewall Errazuriz 0 2 /King/Errazuriz Vishney 1 3 /King/Errazuriz/Vishney ... 34 rows selected.
LEVEL Pseudocolumn
For each row returned by a hierarchical query, theLEVEL
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.
No comments:
Post a Comment