Research on Oracle Tree query (start with connect by prior)

The most important syntax structure of an oracle tree query is: select. Start with... Connect by... Prior, which allows you to display the relationships in the table as a tree.

1. Create test tables and data:

-- create a test table

CREATE TABLE YAG_TREE (

T_ID NUMBER (10) NOT NULL-- id

T_CONTEXT VARCHAR2 (20)-- content

PARENTID NUMBER (10)-parent id

);

-- create a sequence

CREATE SEQUENCE SEQ_YAG START WITH 1 MAXVALUE 100 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 level 1 Node 7 7 Tier 1 Node 7 1

3 parent node 1 murmurs-> first level node 7 murmurs-> secondary nodes 22 22 secondary nodes 22 7

2 parent node 1 murmur1-> first level node 8 8 level 1 node 8 1

3 parent node 1 murmurs-> first level node 8 murmurs-> secondary nodes 23 23 secondary nodes 23 8

-- Tree query 2: query all parent nodes of a node (that is, all previous generations)

SELECT * FROM YAG_TREE START WITH T_ID=26 CONNECT BY PRIOR PARENTID=T_ID

T_ID T_CONTEXT PARENTID

26 secondary nodes 26 11

11 Node 1 11 2

2 parent Node 2

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