Example of using Oracle hierarchical query and with functions

In development, everyone should have done something similar to department management. Generally, there is a lower-level department (sub-department) under the next department. This level is similar to a tree. In this case, the Parent and Child departments are generally divided into two or more tables. This is a common practice; sometimes it is possible to put all departments in a table for the convenience of data table management and add a field to identify the affiliation between departments. This looks a bit strange, but it is OK to use it together. Next I will share with you my experiences, that is, how to create a number chart based on this design. A simple approach is to query a list set, what should I do? First, let's look at my data table.
 
It is not hard to see that fparent is used for association between departments. How to Write SQL statements? The following is a lazy way of writing.
Copy codeThe Code is as follows:
-- | It is a connector. level can be considered as a constant.
Select t. fnum, LPAD ('', 3 * level-3) | '|-' | fname, t. fparent, Level
From m_depart t
-- Start with is followed by condition 1
Start with fparent is null
-- Connect by Prior followed by condition 2
Connect by Prior fnum = fparent

The following is the query result. The query result and number are very close.
 
The following is a commonly used with function, which may be used for complex queries. For more information, see the following article.