Recursive query of SQL statements with a tree structure

1. You can use the start with... connect by clause in Oracle.

The start with... connect by clause recursive query is generally used for a table to maintain a tree structure. Create example Table: Create Table tbl_test (ID number, name varchar2 (100 byte), PID Number default 0); insert test data: insert into tbl_test (ID, name, pid) values ('1', '10', '0'); insert
Tbl_test (ID, name, pid) values ('2', '11', '1'); insert into tbl_test (ID, name, pid) values ('3 ', '20', '0'); insert into tbl_test (ID, name, pid) values ('4', '12', '1'); insert into tbl_test (ID, name, pid)
Values ('5', '123', '2 '); recursive select * from root to tree end from tbl_test start with ID = 1 connect by prior id = PID recursively select * From tbl_test start with ID = 5 connect by prior PID = ID
2. Write a function and call the function Query

-- Test Data
Createtable Tb (ID char (3), PID char (3), name nvarchar (10 ))
Insert TB select '001', null, 'shandong province'
Unionallselect '002', '001', 'yantai City'
Unionallselect '004 ', '002', 'zhaoyuan City'
Unionallselect '003 ', '001', 'qingdao'
Unionallselect '005 ', null, 'sihui City'
Unionallselect '006 ', '005', 'qingyuan City'
Unionallselect '007 ', '006', 'sub-city'
Go
-- Query functions of a specified node and all its subnodes
Createfunction f_cid (@ idchar (3 ))
Returns @ t_leveltable (ID char (3), levelint)
As
Begin
Declare @ levelint
Set @ level = 1
Insert @ t_levelselect @ ID, @ levelwhile @ rowcount> 0
Begin
Set @ level = @ LEVEL + 1insert @ t_levelselect A. ID, @ levelfrom tb a, @ t_level B where a. PID = B. ID and B. Level = @ Level-1ENDRETURNENDGO
-- Call the function to query 002 and all its subnodes
Select a. * from tb a, f_cid ('002 ') B where a. ID = B. ID