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

This article mainly introduces how to use the connect by prior recursion algorithm in oracle. It is very detailed and has certain reference value. Friends who are interested must finish reading it.

Connect by prior Recursive algorithm in oracle

The use of the start with...connect by prior clause in Oracle connect by is used in structured queries, and its basic syntax is:

Select... From tablename start with condition 1

Connect by condition 2

Where condition 3

Example:

Select * from table

Start with org_id = 'HBHqfWGWPy'

Connect by prior org_id = parent_id

Simply put, store a tree structure in a table, for example, there are two fields in a table:

Org_id,parent_id can then form a tree structure by indicating who the parent of each record is.

The query using the above syntax can get all the records of the tree.

Where:

Condition 1 is the qualifying statement of the root node, of course, the qualification can be relaxed to obtain multiple root nodes, which is actually multiple trees.

Condition 2 is the connection condition, where the previous record is represented by PRIOR. For example, CONNECT BY PRIOR org_id = parent_id means that the org_id of the previous record is the parent_id of this record, that is, the father of this record is the previous record.

Condition 3 is a filter condition, which is used to filter all records returned.

A brief introduction is as follows:

When scanning the tree structure table early, you need to access each node of the tree structure according to this. A node can only be accessed once. The steps to access are as follows:

Step 1: start from the root node

Step 2: access the node

Step 3: determine whether the node has any unaccessed child nodes, if so, turn to its leftmost unaccessed child section, and perform the second step, otherwise perform the fourth step

Step 4: if the node is the root node, the access is complete, otherwise perform step 5

Step 5: return to the parent of the node and perform the third step.

In short: the process of scanning the entire tree structure is the process of traversing the tree in the middle order.

1. Description of tree structure

The data of the tree structure is stored in the table, and the hierarchical relationship between the data is the parent-child relationship, which is described by the relationship between the columns in the table, such as EMPNO and MGR in the EMP table. EMPNO represents the number of the employee, and MGR represents the number of the person leading the employee, that is, the MGR value of the child node is equal to the EMPNO value of the parent node. There is a MGR in each row of the table that represents the parent node (except the root node), and the entire tree structure can be determined by the parent node of each node.

Use the CONNECT BY nice START WITH clause in the SELECT command to query the tree structure relationships in the table. The format of the command is as follows:

SELECT .

CONNECT BY {PRIOR column name 1 = column name 2 | column name 1=PRIOR split name 2}

[START WITH]

Where the CONNECT BY clause states that each row of data will be retrieved in a hierarchical order and specifies that the data in the table will be connected to the tree structure. The PRIORY operator must be placed before either of the two columns of the join relationship. For the parent-child relationship between nodes, the PRIOR operator represents the parent node on one side and the child node on the other, thus determining whether the search tree structure is in the order of top-down or bottom-up. In a join relationship, column expressions are allowed in addition to column names. The START WITH clause is optional and is used to identify which node is the root node of the lookup tree. If the clause is omitted, it means that all rows that meet the query criteria are the root node.

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

2. About PRIOR

When prior is put together with the parent node column parentid, it traverses in the direction of the parent node; if prior is placed together with the child node column subid, it traverses in the direction of the leaf node. It doesn't matter who puts the parentid column or subid column before the "=". The key is who the prior is with.

3. Define the lookup start node

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

4. Use LEVEL

In a table with a tree structure, each row of data is a node in the tree structure, and each row of records can have a layer number because the nodes are in a different hierarchical location. The layer number is determined according to the distance between the node and the root node. No matter which node you start from, the layer number of the starting root node is always 1, the child node of the root node is 2, and so on.

5. Clipping of nodes and branches

When querying the tree structure, you can remove some rows in the table or a branch in the tree, and use the WHERE clause to define a single node in the tree structure to remove a single node in the tree, but it does not affect its descendant nodes (when retrieving from top to bottom) or their predecessors (when retrieving from bottom to top).

6. Sort display

As in other queries, you can use the ORDER BY clause in a tree structure query to change the order in which the query results are displayed without having to traverse the tree structure

7. Other parameters referenc

If you prompt for an error in ORA-01436, the error is reported because a loop is generated, and the child node has the same numerical value as the parent node.

Do not change the data to change the following SQL statement so that it does not generate a loop

SELECT r1.region_id,parent_id

FROM cnl_region r1

WHERE r1.region_id = 1 START WITH r1.region_id = 1

CONNECT BY NOCYCLE PRIOR r1.region_id = r1.parent_id

CONNECT BY NOCYCLE can delete recursive values without reporting errors

CONNECT_BY_ISCYCLE is used in conjunction with pseudo columns to show which ones are duplicates

-CONNECT_BY_ISLEAF=0 will have subordinate directories displayed

-CONNECT_BY_ISLEAF=1 displays those belonging to subordinate directories

=

Example:

SQL > select * from emp

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981-4-2 2975.00 20

7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30

7782 CLARK MANAGER 7839 1981-6-9 2450.00 10

7839 KING PRESIDENT 1981-11-17 5000.00 10

7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30

7900 JAMES CLERK 7698 1981-12-3 950.00 30

7902 FORD ANALYST 7566 1981-12-3 3000.00 20

7934 MILLER CLERK 7782 1982-1-23 1300.00 10

7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20

7876 ADAMS CLERK 7788 1987-5-23 1100.00 20

14 rows selected

SQL > col path format A35

SQL > col level_id format A15

SQL > select e.mgrree.deptnoree.empnoree.enameree.joblevelrecoverySYSystCONNECTBYPATH (job,'/') path,lpad (', level*2) | | empno level_id

2 from emp e

3 start with mgr is null

4 connect by prior empno = mgr

5 order by level

MGR DEPTNO EMPNO ENAME JOB LEVEL PATH LEVEL_ID

-

10 7839 KING PRESIDENT 1 / PRESIDENT 7839

7839 20 7566 JONES MANAGER 2 / PRESIDENT/MANAGER 7566

7839 30 7698 BLAKE MANAGER 2 / PRESIDENT/MANAGER 7698

7839 10 7782 CLARK MANAGER 2 / PRESIDENT/MANAGER 7782

7566 20 7902 FORD ANALYST 3 / PRESIDENT/MANAGER/ANALYST 7902

7698 30 7521 WARD SALESMAN 3 / PRESIDENT/MANAGER/SALESMAN 7521

7698 30 7900 JAMES CLERK 3 / PRESIDENT/MANAGER/CLERK 7900

7782 10 7934 MILLER CLERK 3 / PRESIDENT/MANAGER/CLERK 7934

7698 30 7499 ALLEN SALESMAN 3 / PRESIDENT/MANAGER/SALESMAN 7499

7566 20 7788 SCOTT ANALYST 3 / PRESIDENT/MANAGER/ANALYST 7788

7698 30 7654 MARTIN SALESMAN 3 / PRESIDENT/MANAGER/SALESMAN 7654

7698 30 7844 TURNER SALESMAN 3 / PRESIDENT/MANAGER/SALESMAN 7844

7788 20 7876 ADAMS CLERK 4 / PRESIDENT/MANAGER/ANALYST/CLERK 7876

7902 20 7369 SMITH CLERK 4 / PRESIDENT/MANAGER/ANALYST/CLERK 7369

14 rows selected

SQL > select e.mgrree.deptnoree.empnoree.enamegrae.jobpr level

2 from emp e

3 start with mgr is null

4 connect by empno = prior mgr

5 order by level

MGR DEPTNO EMPNO ENAME JOB LEVEL

--

10 7839 KING PRESIDENT 1

SQL >

-- an error is reported with duplicate values.

WITH

T

AS

(

SELECT

'JOHN' EMPLOYEE

'JACK' MANAGER

FROM

DUAL

UNION ALL

SELECT

'JACK' EMPLOYEE

'JOHN' MANAGER

FROM

DUAL

)

SELECT

SYS_CONNECT_BY_PATH (EMPLOYEE,'/') as path

MANAGER

FROM

T

CONNECT BY PRIOR EMPLOYEE = MANAGER

After using CONNECT BY CYCLE, it can be displayed normally.

WITH

T

AS

(

SELECT

'JOHN' EMPLOYEE

'JACK' MANAGER

FROM

DUAL

UNION ALL

SELECT

'JACK' EMPLOYEE

'JOHN' MANAGER

FROM

DUAL

)

SELECT

SYS_CONNECT_BY_PATH (EMPLOYEE,'/') as path

MANAGER

FROM

T

CONNECT BY NOCYCLE PRIOR EMPLOYEE = MANAGER

PATH MANAGER

/ JOHN JACK

/ JOHN/JACK JOHN

/ JACK JOHN

/ JACK/JOHN JACK

-- display repetition with CONNECT_BY_ISCYCLE

WITH

T

AS

(

SELECT

'JOHN' EMPLOYEE

'JACK' MANAGER

FROM

DUAL

UNION ALL

SELECT

'JACK' EMPLOYEE

'JOHN' MANAGER

FROM

DUAL

)

SELECT

SYS_CONNECT_BY_PATH (EMPLOYEE,'/') as path

MANAGER

CONNECT_BY_ISCYCLE

FROM

T

CONNECT BY

NOCYCLE

PRIOR EMPLOYEE = MANAGER

PATH MANAGER CONNECT_BY_ISCYCLE

/ JOHN JACK 0

/ JOHN/JACK JOHN 1

/ JACK JOHN 0

/ JACK/JOHN JACK 1

The above is all the content of the article "how to use connect by prior Recursive algorithm in oracle". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow 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.