Use of Start With...connect by in Oracle (recursive query)

Ali Electric Surface asked the relevant knowledge, find this article on the Internet.

These keywords are query recursive data, forming a tree-like structure. Only Oracle support is available, and other data is implemented in conjunction with stored procedures

Grammar:

SELECT * FROM Some_table

[Where Condition 1]

Connect by [Condition 2]

Start with [condition 3];

The order in which the connect by and start with statements is placed does not affect the results of the query, [WHERE Condition 1] may not be required.

[WHERE Condition 1], [Condition 2], [condition 3] each have different scopes:

[Where Condition 1] is filtered according to a record selected by the "Connect by [Condition 2] start with [condition 3]", which is a filter for a single record and does not take into account the structure of the tree;

[Condition 2] Specifies the conditions of the construction tree, as well as the filtering conditions of the tree branch, where the filtering performed will filter the records that match the conditions and all the sub-nodes below;

[Condition 3] qualifies as the search starting point condition, if the top-down search is qualified as the root node condition, if the bottom-up search is limited as a leaf node conditions;

Look at the following example

TEST

EMPNO	ename	dpt	mar	sal
0075	xu	d1	0082	3000
0082	bao	d1	0096	40 XX
0096	zhao	d2	null	&nb sp;5000
0111	mu	d2	0082	4000
0056	zhang	d3	0111	3000
0923	wang	d3	0075	2000

select* from test start with EMPNO = ' 0111 ' Connect by EMPNO = Prior MAR

select* from test start with EMPNO = ' 0111 ' connect by prior EMPNO = MAR

Description of the prior:

Prior exists in [condition 2], can not, do not need to find only the records that meet the "start with [condition 3]", not in the search for the child nodes of these records.

There are two ways to do this: connect by prior empno=mgr or connect by Empno=prior Mgr,

The former means using a top-down search method (looking for a parent node and then looking for a child node),

The latter approach is to use a bottom-up search method (find the leaf node first and look for the parent node).

[Principle of execution]:

Iterate through each record in the table, comparing whether the condition after start with is met, and if not, continue to the next one, or the record as the root node if satisfied.

Then recursively look for the sub-nodes under the node, looking for conditions that are specified after connect by, such as the above example, is the empno of the current record equals the MGR of its child nodes, so loop through all the records of the complete table.

If there is a problem with the data, there is a loop, that is, a is the manager of B, B is the manager of C, and C is the manager of a, the query will appear ORA-01436: Error in the Connect by loop in the user data, can be added after connect by nocycle to solve the problem.