Shulou(Shulou.com)05/31 Report--

This article mainly shows you how to use "ORACLE hierarchical query start with and connect by", the content is simple and easy to understand, organized clearly, I hope to help you solve doubts, let Xiaobian lead you to study and learn "ORACLE hierarchical query start with and connect by how to use" this article bar.

Hierarchical queries are extensions of select statements to quickly find column-column membership in a table.

19.1 tree traversal

ORACLE is a relational database management system, which organizes data in the form of tables, and the data in some tables also presents tree structure relationships. For example, we are now discussing the EMP table, which contains two columns, EMPNO and MGR, that reflect the relationship between the leader and the led. This relationship between them is a tree structure.

Tree traversal has two directions

top--down

That is, a father finds his son, a father may have several sons, a son may have several grandchildren, traversal cannot lose his son, the order is left first.

down--top bottom up

That is, a son can only have one father, so the order should be: grandson-> son-> father-> grandfather.

19.2 CONNECT BY AND START WITH

Use CONNECT BY and START WITH clauses in SELECT commands to query tree structure relationships in tables. The command format is as follows:

SELECT ...

CONNECT BY {PRIOR Column 1= Column 2| Column 1=PRIOR Column 2}

[START WITH];

19.3 About CONNECT BY Clause

It is important to understand the CONNECT BY PRIOR clause, which determines the tree retrieval direction: top --> down (parent--> child) or down --> top (child--> parent).

In hierarchical tables, the parent column and child column of the table are determined (identity fixed), such as empno is the child column (child) in emp table, mgr is the parent column (parent).

RIOR keyword is like an arrow ("-->"),

connect by prior empno = mgr

connect by mgr = prior empno

The two sentences are syntactically equivalent, saying mgr (parent)--> empno(child), so the tree search direction is top --> down.

connect by empno = prior mgr

connect by prior mgr = empno

The two sentences are syntactically equivalent, both saying empno(child)--> mgr(parent), so the tree search direction is down --> top.

19.4 The START WITH clause is optional and identifies which node is the root node of the lookup tree. If this clause is omitted, it means that all rows that satisfy the query criteria are root nodes (each row becomes a tree root).

Example 1 shows EMP table data in tree structure.

SQL>select empno,ename,mgr from emp connect by prior empno=mgr start with empno=7839;

If you look closely at the empno column output order, it is the structure of each branch of the tree structure (starting from the root node) in the above image.

mgr (parent)--> empno(child), so the tree retrieval direction is top --> down.

Example 2: Start from SMITH node and search EMP tree structure from bottom to top.

SQL>select empno,ename,mgr

from emp

connect by empno=prior mgr

start with empno=7369

/

In this bottom-up search, only one branch of the tree is displayed.

empno(child)--> mgr(parent), so the tree retrieval direction is down --> top

19.5 Define the search start node

When querying the tree structure from the top down, you can start not only from the root node, but also define any node as the starting node to start looking down. The result of this lookup is a branch of the tree starting at that node.

Example 3 Find information about all employees who are directly or indirectly led by 7566(JONES).

SQL>SELECT EMPNO,ENAME,MGR

FROM EMP

CONNECT BY PRIOR EMPNO=MGR

START WITH EMPNO=7566

/

START WITH can specify not only one root node, but also multiple root nodes.

Example 4 Find information about all employees led by FORD and BLAKE.

SQL>SELECT EMPNO,ENAME,MGR

FROM EMP

CONNECT BY PRIOR EMPNO=MGR

START WITH ENAME IN ('FORD','BLAKE')

Father looking for son

19.6 Use LEVEL

In a query, you can use the pseudo-column LEVEL to show the relevant hierarchy for each row of data. LEVEL returns the hierarchy of the current node in the tree structure.

The pseudo-column LEVEL is numeric and can be used for various calculations in the SELECT command.

Example 5 Use LEVEL to change the display format of query results.

SQL> COLUMN LEVEL FORMAT A20

SQL> SELECT LPAD(LEVEL,LEVEL*3,' ')

as "LEVEL",EMPNO,ENAME,MGR

FROM EMP

CONNECT BY PRIOR EMPNO=MGR

START WITH ENAME='KING'

/

In SELECT, the function LPAD is used, which means filling with LEVEL*3 spaces. Since different rows are at different node positions and have different LEVEL values, the number of spaces filled will be determined according to their respective layer numbers. Spaces are spliced with layer numbers, and the result shows this hierarchical relationship.

View only employee information on level 2:

SQL> select t1.* from (select level LNUM ,ename,mgr from emp connect by prior empno=mgr start with ename='KING') t1 where LNUM=2;

19.7 Clipping of nodes and branches

When querying a tree structure, you can remove certain rows from the table, or you can clip a branch from the tree and use a WHERE clause to qualify a single node in the tree structure to remove a single node from the tree without affecting its descendant nodes (top-down retrieval) or predecessor nodes (bottom-up retrieval).

SQL>SELECT LPAD(LEVEL,LEVEL*3,' ')

as "LEVEL",EMPNO,ENAME,MGR

FROM EMP

WHERE ENAME'SCOTT'

CONNECT BY PRIOR EMPNO=MGR

START WITH ENAME='KING'

/

In this query, only a single node SCOTT in the tree is clipped. If you want to cut a branch of the tree structure, use the CONNECT BY clause. CONNECT BY clauses restrict the entire branch in the tree structure, pruning both individual nodes on the branch and its descendants (in top-down retrieval) or predecessors (in bottom-up retrieval).

Example 8. Display information about all employees under KING leadership, excluding one under SCOTT leadership.

SQL>SELECT LPAD(LEVEL,LEVEL*3,' ') as "LEVEL",EMPNO,ENAME,MGR FROM EMP CONNECT BY PRIOR EMPNO=MGR AND ENAME!= 'SCOTT' START WITH ENAME='KING';

/

In addition to cutting off the single node SCOTT, this query result also cuts off the child node ADAMS of SCOTT, that is, the branch SCOTT is cut off.

Of course, the WHERE clause can be used in conjunction with the CONNECT BY clause, so that both a single node and a branch of the tree can be pruned.

Example 9. Display information about all employees led by KING, excluding employee SCOTT, and one led by BLAKE.

SCOTT@hyyk> SELECT LPAD(LEVEL,LEVEL*3,' ') as "LEVEL",EMPNO,ENAME,MGR FROM EMP CONNECT BY PRIOR EMPNO=MGR AND ENAME!= 'SCOTT' and ENAME!= 'BLAKE' START WITH ENAME='KING';

When using SELECT statements to report tree-structured reports, it should be noted that the CONNECT BY clause does not act on table joins that appear in the WHERE clause. If you need to join, you can create a view using a tree structure, and then join this view with other tables to complete the desired query.

The above is "ORACLE hierarchical query start with and connect by how to use" all the contents of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.