Oracle hierarchy query Connect by usage __oracle

If the table contains hierarchical data, you can use the Hierarchy query clause to select the row-level order. 1. The grammar of the hierarchy query clause

Hierarchy query clause syntax:

{CONNECT by [nocycle] condition [and condition] ... [START with condition]
| START with Condition CONNECT by [nocycle] condition [and condition] ...
}

START with: Specifies the row of nodes for the hierarchy.

CONNECT by: Specifies the relationship of the parent row of the hierarchy to the child row. The nocycle parameter instructs the Oracle database query to return rows, even though the connect by has a loop in the data. Usually used with the connect_by_iscycle pseudo column to see if the row contains loops. In a hierarchical query, the condition of an expression must be qualified with the prior operator to query the parent row, for example:

... PRIOR expr = expr
Or
... expr = PRIOR expr

If the condition of connect by is a compound condition, only one condition prior operator is required, however there can be multiple prior conditions. For example:

CONNECT by last_name!= ' King ' and PRIOR employee_id = manager_id ...
CONNECT by PRIOR employee_id = manager_id and
PRIOR account_mgr_id = customer_id ...

Prior is a unary operator and a unary +-arithmetic operator with the same precedence. Prior immediately calculates the parent row of the current row based on the expression in the hierarchy query.

Prior must be used in conjunction with the equality operator that compares the column values (the PRIOD keyword can be either side of the equality operator).

The CONNECT by condition and the priod expression form an unrelated subquery structure. Therefore Currval and nextval are invalid prior expressions, so prior expressions cannot be used in query sequences.

You can further refine a hierarchy query by using the Connect_by_root operator to qualify columns in the query list. This operator extends the ability of the hierarchical query connect by [PRIOR] condition, not only to return the parent row immediately, but also to return all row root node rows in the hierarchy.

2. Hierarchy Query pseudo column

Hierarchical query pseudo columns are valid only in hierarchical queries, hierarchical query pseudo columns: 2.1.connect_by_iscycle pseudo columns

If the current row has one child row, and the child row is the ancestor row of the current row, Connect_by_iscycle returns 1, otherwise it returns 0.

Connect_by_iscycle can be specified only if the nocycle parameter is specified in the Connect by clause. Because the connect by has circular data, nocycle can enable Oracle to return query results, otherwise the query will fail. 2.2.connect_by_isleaf pseudo Columns

If the current row is a leaf node of the CONNECT by criteria tree, the connect_by_isleaf pseudo column returns 1 or 0. This information also indicates whether a given row can be expanded further, showing more levels. 2.3.LEVEL pseudo-column

Each row returned by a hierarchy query, returns 1 with the node row level pseudo column, returns 2 to the node's child node row level, and so on. The row to the node is the topmost row of the inverted tree. A child node row is any non-node row. The parent node row is any row that has child nodes. The leaf node row is any row without child nodes.

3.EXAMPLES 3.1.CONNECT by Example

Query the superiors of all employees.

Sql> Select E.empno, E.ename, e.mgr from emp e connect by prior e.empno = E.mgr;

EMPNO ename MGR
----- ---------- -----
7788 SCOTT 7566
7876 ADAMS 7788
7902 FORD 7566
7369 SMITH 7902
7499 ALLEN 7698
7900 JAMES 7698
7844 TURNER 7698
7654 MARTIN 7698
7521 WARD 7698
7934 MILLER 7782
7876 ADAMS 7788
7566 JONES 7839
7788 SCOTT 7566
7876 ADAMS 7788
7902 FORD 7566
7369 SMITH 7902

........ 3.2.LEVEL Example

Use level virtual columns to display the hierarchy of parent rows to child rows

Sql> Select E.empno, E.ename, e.mgr,level from emp e connect by prior e.empno = E.mgr;

EMPNO ename MGR Level
----- ---------- ----- ----------
7788 SCOTT 7566 1
7876 ADAMS 7788 2
7902 FORD 7566 1
7369 SMITH 7902 2
7499 ALLEN 7698 1
7900 JAMES 7698 1
7844 TURNER 7698 1
7654 MARTIN 7698 1
7521 WARD 7698 1
7934 MILLER 7782 1
7876 ADAMS 7788 1
7566 JONES 7839 1
7788 SCOTT 7566 2
7876 ADAMS 7788 3
7902 FORD 7566 2
7369 SMITH 7902 3

............

3.3.START with examples

Start with employee king and check out all employees ' superiors

Sql> Select E.empno, E.ename, e.mgr, Level
2 from EMP E
3 Connect by Prior E.empno = E.mgr
4 start with e.ename = ' KING ';

EMPNO ename MGR Level
----- ---------- ----- ----------
7839 KING 1
7566 JONES 7839 2
7788 SCOTT 7566 3
7876 ADAMS 7788 4
7902 FORD 7566 3
7369 SMITH 7902 4
7698 BLAKE 7839 2
7499 ALLEN 7698 3
7521 WARD 7698 3
7654 MARTIN 7698 3
7844 TURNER 7698 3
7900 JAMES 7698 3
7782 CLARK 7839 2
7934 MILLER 7782 3

3.4.NOCYCLE Examples

Create a connect by loop data, designate employee Scott as the employee King's superior, and there will be a dead loop.

To create circular data:

sql> update emp e set e.mgr = ' 7788 ' where e.ename = ' KING ';

Query the superiors of the employee starting with King:

Sql> Select E.empno, E.ename, e.mgr, Level
2 from EMP E
3 Connect by Prior E.empno = E.mgr
4 start with e.ename = ' KING ';

Select E.empno, E.ename, e.mgr, Level
From EMP E
Connect by Prior E.empno = E.mgr
Start with e.ename = ' KING '

ORA-01436: CONNECT by loop in user data

Using the nocycle parameter, query the employee's superiors starting with King:

Sql> Select E.empno, E.ename, e.mgr, Level, connect_by_iscycle "CYCLE"
2 from EMP E
3 Connect by nocycle prior e.empno = E.mgr
4 start with e.ename = ' KING ';

EMPNO ename MGR Level CYCLE
----- ---------- ----- ---------- ----------
7839 KING 7788 1 0
7566 JONES 7839 2 0
7788 SCOTT 7566 3 1
7876 ADAMS 7788 4 0
7902 FORD 7566 3 0
7369 SMITH 7902 4 0
7698 BLAKE 7839 2 0
7499 ALLEN 7698 3 0
7521 WARD 7698 3 0
7654 MARTIN 7698 3 0
7844 TURNER 7698 3 0
7900 JAMES 7698 3 0
7782 CLARK 7839 2 0
7934 MILLER 7782 3 0