Oracle hierarchical query learning summary, oracle level

Oracle hierarchical query learning summary, oracle level
Hierarchical query document directory:

1.1 Summary 21.2 node terminology 21.3 use the connect by and start with clauses 31.4 hierarchical query instance 31.5 query from bottom up 41.6 Delete nodes and branches from hierarchical query 5

Document address:

Http://wenku.baidu.com/view/324fb47a700abb68a982fbd1

Body

Note: The image cannot be displayed. Please go to the library to view it.

Address: http://blog.csdn.net/sunansheng/article/details/46492299

1. Hierarchical query 1.1 Summary

We often see organization-level data. For example, a company's employees constitute a level, which can be represented in a tree. For example, the Oracle table scott. emp can be represented using the following tree:

The following is the row returned by the scott. emp table:

The MGR column is a self-reference column, which refers to the EMPNO column. MGR indicates the manager or superior of an employee. In ORACLE, you can use the connect by and start with clauses to query data in this hierarchy.

1.2 node terminology

As shown in, these elements constitute a tree. There are some technical terms for the node composition tree, as follows:

1)Root Node: The root node is the node at the top of the tree. As shown in, the root node is "KING ".

2)Parent node: One or more nodes under the parent node.

3)Subnode: The child node has a parent node.

4)Leaf node: Leaf nodes are nodes without subnodes.

 

1.3 Use the connect by and start with clauses

The connect by and start with clauses of SELECT statements have the following syntax:

SELECT [LEVEL], column... FROM TABLE [WHERE where_condition] [[Start with start_condition] [connect by prior prior_condition]

Where:

LLEVEL: It is a "pseudo column" that represents the layer of the tree. For the root node, LEVEL is 1, and the child node LEVEL of the root node is 2...

LStart_condition: Defines the starting point of a hierarchical query. The start with clause must be specified for hierarchical queries. For example, you can specify start_condition as EMPNO = 7839 (KING), which indicates that the query starts from KING.

LPrior_condition: Defines the relationship between the parent row and the Child row. When writing a hierarchical query, you must define the connect by prior clause. For example, prior_condition can be defined as ENPNO = MGR, indicating that the parent node's ENPNO has a relationship with the child node's MGR, that is, the child node's MGR points to the parent node's ENPNO. Which is followed by the PRIOR keyword to represent the parent node.

1.4 hierarchical query instance

1) query the level of the EMP table. Query employees under "JONES"

SELECT e. empno, E. mgr, E. ename FROM scott. emp e Start with e. ename = 'Jones' Connect by prior e. empno = e. mgr   Query results: EMPNO MGR ENAME -------------------- 7566 7839 JONES 7788 7566 SCOTT 7876 7788 ADAMS 7902 7566 FORD 7369 7902 SMITH

Expected results:

2) use pseudo column LEVEL

Select level, E. empno, E. mgr, E. ename FROM scott. emp e Start with e. ename = 'Jones' Connect by prior e. empno = e. mgr   Query results: LEVEL EMPNO MGR ENAME ------------------------------ 1 7566 7839 JONES 2 7788 7566 SCOTT 3 7876 7788 ADAMS 2 7902 7566 FORD 3 7369 7902 SMITH

 

1.5 query from bottom up

You do not have to traverse the tree from the parent node to the child node from top to bottom. You can also traverse the tree from the bottom of a child node. The method is to swap the order of the parent and child nodes in the connect by prior clause. For example, connect by prior mgr = empno can CONNECT the mgr of the child node to the empno of the parent node.

-- From the leaf node "SMITH", query the root node from the bottom up: Select level, E. empno, E. mgr, E. ename FROM scott. emp e Start with e. ename = 'Smith' Connect by prior e. mgr = e. empno;   Query results: LEVEL EMPNO MGR ENAME ------------------------------ 1 7369 7902 SMITH 2 7902 7566 FORD 3 7566 7839 JONES 4 7839 KING

 

1.6 Delete nodes and branches from hierarchical queries

1. delete a node

You can use the WHERE clause to remove a specific node from the query tree. The following query uses WHERE e. ename! = 'Ford 'clause removes FORD from the result

Select level, E. empno, E. mgr, E. ename FROM scott. emp e WHERE e. ename! = 'Scott' Start with e. ename = 'Jones' Connect by prior e. empno = e. mgr;   Query results: LEVEL EMPNO MGR ENAME ------------------------------ 1 7566 7839 JONES 3 7876 7788 ADAMS 2 7902 7566 FORD 3 7369 7902 SMITH

As you can see, although SCOTT has removed it from the result, his subordinate ADAMS is still in the result. To remove the entire branch from the query results, you can use the AND clause in the connect by prior clause. For example, the following example uses AND e. ename! = 'Scott 'removes SCOTT and all his subordinates from the result

2. Delete Branch

Select level, E. empno, E. mgr, E. ename FROM scott. emp e Start with e. ename = 'Jones' Connect by prior e. empno = e. mgr AND e. ename! = 'Scott ';   Query results: LEVEL EMPNO MGR ENAME ------------------------------ 1 7566 7839 JONES 2 7902 7566 FORD 3 7369 7902 SMITH