Oracle Tree Query (start with connect by prior) Explore

The most important syntax structure for Oracle tree queries is: Select...start with...connect by...prior, which allows you to display the relationships in a table as a tree.

1. Create test tables and data:

--Create a test table

CREATE TABLE Yag_tree (

T_ID Number (Ten) is not NULL,--id

T_context VARCHAR2 (20),--content

ParentID Number (10)--Parent ID

);

--Create a sequence

CREATE SEQUENCE Seq_yag START with 1 MAXVALUE INCREMENT by 1 nocycle CACHE 10;

--Generate parent node data

BEGIN

For I in 1..5 LOOP

INSERT into Yag_tree VALUES (Seq_yag. Nextval, ' parent node ' | | Seq_yag. Currval,null);

COMMIT;

END LOOP;

END;

--Generate first-level node data

BEGIN

For I in 1..15 LOOP

IF I <=3 Then

INSERT into Yag_tree VALUES (Seq_yag. Nextval, ' first-level node ' | | Seq_yag. currval,1);

Elsif I<=6 Then

INSERT into Yag_tree VALUES (Seq_yag. Nextval, ' first-level node ' | | Seq_yag. currval,2);

Elsif I<=9 Then

INSERT into Yag_tree VALUES (Seq_yag. Nextval, ' first-level node ' | | Seq_yag. currval,3);

Elsif I<=12 Then

INSERT into Yag_tree VALUES (Seq_yag. Nextval, ' first-level node ' | | Seq_yag. currval,4);

Elsif I<=15 Then

INSERT into Yag_tree VALUES (Seq_yag. Nextval, ' first-level node ' | | Seq_yag. currval,5);

COMMIT;

END IF;

END LOOP;

END;

--Generate level two node data

BEGIN

For I in 1..15 LOOP

INSERT into Yag_tree VALUES (Seq_yag. Nextval, ' Level two node ' | | Seq_yag. Currval,seq_yag. CURRVAL-15);

COMMIT;

END LOOP;

END;

2, tree Query application:

--Tree Query 1: Queries all child nodes below the parent node id=1 (that is, all descendants)

SELECT level/* Depth */,sys_connect_by_path (t_context, '---> ')/* Tree path */,a.*

From Yag_tree A

START with t_id = 1

CONNECT by PRIOR t_id = ParentID;

Level Sys_connect_by_path t_id T_context ParentID

-------- ----------------------------- ----------- -------------------- -----------

1 parent Node 11 parent Node 1

2 parent Node 1---> First-level node6 61-node 6 1

3 parent Node 1---> First-level node 6---> Two-level node Two-node 21 6

2 parent Node 1---> First-level node 7 7 node 7 1

3 parent Node 1---> First-level node 7---> Two-level node 222-level node 7

2 parent Node 1---> First-level node 8 8 node 8 1

3 parent Node 1---> First-level node 8---> Two-level node Two-level node 8

--Tree Query 2: Query all parent nodes of a node (that is, all first generation)

SELECT * from Yag_tree START with t_id=26 CONNECT by PRIOR parentid=t_id;

t_id T_context ParentID

----------- -------------------- -----------

Level 262 Node 26 11

Level 111 Node 11 2

2 Parent Node 2

<-------to be continued-------->

Learning Resources: http://www.cnblogs.com/linjiqin/archive/2013/06/24/3152674.html

This article is from the "Qytag (upspringing)" blog, so be sure to keep this source http://qytag.blog.51cto.com/6125308/1530176