Examples of using Oracle hierarchy queries and with functions

In the development of what you should have done similar department management such functions, in general, a department below there is the next level (sub departments), this level is similar to a tree. In this case, the parent department and the child department are generally divided into 2 or more tables, this is a more conventional approach; sometimes it may seem strange to put all the departments in a table for the convenience of datasheet management and add a field to identify the affiliation between the departments, but it's OK to make it work. Below I share my experience with you, that is, based on this design how to do a number of graphs, the simple way is to query out a list collection can, how to do it first look at my datasheet

It is not difficult to see the department with Fparent Association, that SQL statement how to write it, the following is a lazy way of writing

Copy Code code as follows:
-- || is a connector, level can be viewed as a constant
Select T.fnum,lpad (', 3*level-3 ') | | | -'|| FName Fname,t.fparent,level Lev
From M_depart t
--start with Back is condition 1
Start with fparent is null
--connect by Prior is the condition 2
Connect by Prior Fnum=fparent

Here are the query results, and the query results are very close to

Almost there. There is also a more commonly used with functions, generally do complex queries may be used, you can refer to the following article