Oracle 10g connect_by_iscycle
The purpose of the connect_by_iscycle pseudo-column is to
return 1 if the current row has a child which is also its
ancestor. If there is no relation, it returns a 0 value. This
information can then be used to further expand the hierarchy.
connect_by_iscycle can only be used if you have specified the
nocycle parameter of the CONNECT BY clause. The NOCYCLE clause
enables Oracle to return the results of a query that would fail
because of a CONNECT BY loop.
connect_by_iscycle Example
In the standard Oracle example table, scott.emp, provided for
this purpose in all Oracle databases, the employee King is the
ultimate boss and has no manager (no entry for the mgr column).
One of his employees is Clark, who is the manager of department
10. If we update the employees table to set Clark as King’s
manager, we can create a loop in the data to show the use of the
CONNECT_BY_ISCYCLE pseudo-column:
Without the update the SELECT:
SQL> SELECT ename "Employee",
2 LEVEL, SYS_CONNECT_BY_PATH(ename, ’/’) "Path"
3 FROM scott.emp
4 WHERE level <= 3 AND deptno = 10
5 START WITH ename = ’KING’
6 CONNECT BY PRIOR empno = mgr AND LEVEL <= 4;
Generates:
Employee LEVEL Path
-------------- --------- ------------------
KING 1 /KING
CLARK 2 /KING/CLARK
MILLER 3 /KING/CLARK/MILLER
Now, let's give Clark a really big promotion (don't we all
dream about becoming our boss’s boss?).
SQL> UPDATE scott.emp SET mgr = 7782
2 WHERE empno = 7939;
1 row updated.
Now let's check out the hierarchy again:
SQL> SELECT ename "Employee",
2 LEVEL, SYS_CONNECT_BY_PATH(ename, ’/’) "Path"
3 FROM scott.emp
4 WHERE level <= 3 AND deptno = 10
5 START WITH ename = ’KING’
6 CONNECT BY PRIOR empno = mgr AND LEVEL <= 4;
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
By adding the nocycle parameter in the CONNECT BY condition, we
can cause Oracle to return the rows despite the loop. The
connect_by_iscycle pseudo-column will show you which rows contain
the cycle:
SQL> SELECT ename "Employee", CONNECT_BY_ISCYCLE
"Cycle",
2 LEVEL, SYS_CONNECT_BY_PATH(ename, ’/’) "Path"
3 FROM scott.emp
4 WHERE level <= 3 AND deptno = 10
5 START WITH ename = ’KING’
6 CONNECT BY NOCYCLE PRIOR empno = mgr AND LEVEL
<= 4;
Employee CYCLE LEVEL Path
-------------- ----- --------- ------------------
KING 0 1 /KING
CLARK 1 2 /KING/CLARK
MILLER 0 3 /KING/CLARK/MILLER
|
