Oracle tree structure data hierarchy display

In practical applications, tree structure data is widely used, such as the directory structure of books and the classification of organization departments !!!!!

Such as the Directory of a book

Name	Code	P_code
Directory	Mulu	0
Chapter 1	Zh_1	Mulu
Section 1	Ji_1_1	Zh_1
First	Hu_1_1_1	Ji_1_1
Second	Hu_1_1_2	Ji_1_1
Third time	Hu_1_1_3	Ji_1_1
Section 2	Ji_1_2	Zh_1
First	Hu_1_2_1	Ji_1_2
Second	Hu_1_2_2	Ji_1_2
Third time	Hu_1_2_3	Ji_1_2
Chapter 2	Zh_2	Mulu
........

If we want to count something at a time, we need to divide it into directories, chapters, sections, and replies! The related items are only directly related to the reply!

Then our data must be like this.

A corresponds to chapter 1, section 2, and section 3, which is converted into chapter 1, chapter 2, and Chapter 2, and Chapter 3. That's all !!!

Oracle has a dedicated way to present tree data, which is much easier than other types of databases !!!

select tt.code, tt.name,tt.p_code,  code_path,name_path,  regexp_substr(code_path,'[^/]+[A-Za-z0-9_]*',1,1) lev1_code,  regexp_substr(name_path,'[^/]+[A-Za-z0-9_]*',1,1) lev1_name,  regexp_substr(code_path,'[^/]+[A-Za-z0-9_]*',1,2) lev2_code,  regexp_substr(name_path,'[^/]+[A-Za-z0-9_]*',1,2) lev2_name,  regexp_substr(code_path,'[^/]+[A-Za-z0-9_]*',1,3) lev3_code,  regexp_substr(name_path,'[^/]+[A-Za-z0-9_]*',1,3) lev3_name  from (select t.*,level,CONNECT_BY_ROOT code as root,CONNECT_BY_ISLEAF ,   SYS_CONNECT_BY_PATH (code, '/')  code_path,   SYS_CONNECT_BY_PATH (name, '/')  name_pathfrom book_mulu t START WITH p_code='0' CONNECT BY NOCYCLE t.p_code = PRIOR t.code) tt

I used the Oracle connect by syntax here. There are a lot of materials on the Internet.

If you want to go down from the top of the tree or go up from the bottom of the tree, the priority of this keyword is the key !!! For men and women, priority is given to women. For men and women, prior is held by women. therefore, from the top of the tree to the bottom, the following priority, so prior to code; the bottom of the tree to the top, prior to p_code, this is easy to understand !!! Nocycle refers to the problematic data of loop loops !!!

Level, connect_by_root, connect_by_isleaf, and connect_by_iscycle are special keywords in the connect by syntax. There may be other keywords. I am not sure yet !!!

Level is the level of the tree, starting from 1! (The tree here refers to the queried data tree, which may be part of the entire tree );

Connect_by_root is the first node of the tree;

Connect_by_isleaf indicates whether the current node is a leaf; 1 indicates yes; 0 indicates no;

Connect_by_iscycle if the content of a Father's Day point is referenced in the current row and a loop appears in the tree, "1" is displayed; otherwise, "0" is displayed ";

The sys_connect_by_path function may have been used in some row/column conversions. A good function can concatenate the directory layers of the tree !!!!

For example, sys_connect_by_path (name, '/') indicates chapter 1, section 2, and section 3.

With this, we can integrate the data and combine Oracle string functions with a clear hierarchy tree to sort the layers easily!