Oracle Tree queries and functions

The most important thing about Oracle tree queries is the Select...start with ... connect by...prior syntax. Depending on the syntax, we can list a table-shaped structure in the order of the tree. Below is a list of common query methods for tree queries in Oracle, as well as the frequently used Oracle attribute functions related to tree queries, where only a tree query in a table is involved and does not involve associations in multiple tables.

As an example of a table in a project I've done, the table structure is as follows:

SQL code CREATE TABLE flfl
(  
ID number is not NULL,
MC NVARCHAR2 (),
FLJB number,
sjflid Number
)  

FLJB is the level of the tree, which in many queries can speed up the query efficiency of SQL. The features shown below basically do not use this keyword.

Sjflid storage is the ancestor ID, if it is a top-level parent node, the sjflid is null (to add, it was originally designed, but now know that the table is best to have a null record, which will cause the full-text scan, proposed to change to 0 instead).

From the most basic operation, we list the common operations in tree queries, so the queried nodes are based on the family's generation.

1. Find all top-level parent nodes (the longest generation) in the tree. Assuming that the tree is a directory structure, the first operation always finds all the top-level nodes and then finds its subordinate nodes based on that node.

SQL code SELECT * from flfl WHERE sjflid is   NULL;

This is a primer, no use to tree-type query.

2. Find the immediate child node (all sons) of a node. If you are looking for a subordinate sub-class node, it is not necessary to use a tree query.

SQL code SELECT * from flfl WHERE sjflid = 819459;

This can be found directly under the sub-class node with ID 819459.

3. Find all the immediate child nodes (all descendants) of a node.

SQL code SELECT * from flfl START with ID = 819459 CONNECT  c11> by sjflid = PRIOR ID;

This looks for all of the immediate sub-class nodes under the node with ID 819459, including all the subordinate nodes of the child's generation and grandchildren.

4. Find the immediate parent node (father) of a node. If you are looking for a node directly under the parent node, it is not necessary to use a tree-type query.

SQL code SELECT b.* from flfl a joins FLFL b on a.sjflid = b  . ID WHERE a.id = 6758;

This finds the immediate parent node of the node with ID 6758, which is associated with the same table.

5. Find all the immediate parent nodes (ancestors) of a node.

SQL code SELECT * from flfl START with ID = 6758 CONNECT    /c8> by PRIOR sjflid = ID;

Here is the ID 6758 of all the immediate parent node, for example is to find a person's father, grandfather and so on. But it is worth noting that the order of the results of this query is to list the subclass node and then list the parent class node, let's say it is a reverse.

There are two tree queries listed above, the 3rd statement and the 5th statement, the difference between the two statements is that the location of the prior keyword is different, so the query is determined in different ways. when sjflid = Priorid, the database iterates over the same record as the ID sjflid based on the current ID, so the result of the query is to iterate through all the subclass records, while the prior ID =sjflid The database iterates over the current sjflid with the same ID as the current sjflid, so the result of the query is the result of all the parent classes.

Here is a series of deeper queries against the tree structure, where the query is not necessarily the best way to query, perhaps just one of the implementations.

6. Query the sibling node (brother) of one node.

SQL code SELECT a.*
From flfl a
WHERE EXISTS (SELECT *
From flfl b
WHERE a.sjflid = b.sjflid and b.id = 6757);

The query here is the node with ID 6757 is the same parent node, like a brother.

7. Query the node (Gypsy) that is sibling to one node (s). If you set the Level field in the table, the FLJB in the table above, then it will be easy to do such queries, the same level is the same as that node, where the implementation is listed when not using this field!

SQL code With tmp as
(SELECT a.*, level Lev
From flfl a
START with a.sjflid is NULL
CONNECT by a.sjflid = PRIOR a.id)
SELECT *
From tmp
WHERE lev = (SELECT Lev
From tmp
WHERE ID = 819394)

Here are two tricks, one using level to identify each node's levels in the table, and using the WITH syntax to simulate a temporary table with a level.

8. Query the sibling node of the parent node of a node (uncle and uncle).

SQL code With tmp as
(SELECT flfl.*, level Lev
From flfl
START with sjflid is NULL
CONNECT by sjflid = PRIOR ID)
SELECT b.*
From tmp B,
(SELECT *
From tmp
WHERE ID = 7004 and lev = 2) a
WHERE B.lev = 1
UNION all
SELECT *
From tmp
WHERE sjflid = (SELECT DISTINCT x.id
From tmp x,
tmp y,
(SELECT *
From tmp
WHERE ID = 7004 and lev > 2) z
WHERE y.id = z.sjflid and x.id = y.sjflid);

Here the query is divided into the following steps. First, like the 7th, the whole table is used to add the level of temporary tables, and secondly, according to the level to determine a few types, in the above cited example, there are three cases:(1) The current node is the top node, that is, the query out of the Lev value is 1, then it does not have a parent node, not considered. (2) The current node is a level 2 node, the query out of the Lev value is 2, so long as the guarantee Lev Level 1 is the sibling node of its ancestor. (3) The other situation is 3 and above, then it is necessary to select the superior node (grandfather), then to judge the grandfather's subordinate node is the parent node of the node is the sibling node. Finally, the result set is formed by combining the results of the query with Union.

9. Query the sibling node (family uncle) of the parent node of a node.

This is actually the same as the 7th case.

SQL code With tmp as
(SELECT a.*, level Lev
From flfl a
START with a.sjflid is NULL
CONNECT by a.sjflid = PRIOR a.id)
SELECT *
From tmp
WHERE lev = (SELECT Lev
From tmp
WHERE ID = 819394)-1

You just have to make a level decision.

Basically, the common query is inside, and there are some uncommon ones. Among them, the content of the query is the basic information of the node, all the basic fields in the data table, but there are some special requirements in the tree query, which is to deal with the query data, including the tree path.

add a concept that, for a database, the root node is not necessarily the top-level node designed in the database, and for the database, the root node is where start with starts.

Some of the specific tree-related requirements are listed below.

10. Name to list the full path of the name.

There are two common cases, one that is listed from the top level until the name (or other attribute) of the current node, and one that is listed from the current node until the name (or other attribute) of the top-level node. Take the address as an example: the domestic habit is from the province to start, to the city, to the county, to the neighborhood, and foreign habits just the opposite (the teacher said, has not received foreign mail, who can send a look).

Start at the top:

SQL code SELECT Sys_connect_by_path (MC, '/')
From flfl
WHERE ID = 6498
START with sjflid is NULL
CONNECT by sjflid = PRIOR ID;

Start with the current node:

SQL code SELECT Sys_connect_by_path (MC, '/')
From flfl
START with ID = 6498
CONNECT by PRIOR sjflid = ID;

I'm going to have to put up a complaint here again. Oracle provides only one Sys_connect_by_path function, but forgets the order in which strings are concatenated. In the above example, the first SQL is traversed from the root node, and the second SQL is directly to find the current node, in terms of efficiency is already very diverse, more critical is that the first SQL can only select one node, and the second SQL is to traverse a tree. PS again.

The Sys_connect_by_path function starts at the start with the beginning of the traversal, and notes its traversal to the node, start with the beginning of the place is treated as the root node, the path will be traversed according to the delimiter in the function, a new string is formed, This feature is still very powerful.

11. Lists the root node of the current node.

As stated earlier, the root node is where start with begins.

SQL code SELECT connect_by_root MC, flfl.*
From flfl
START with ID = 6498
CONNECT by PRIOR sjflid = ID;

The connect_by_root function is used before the column to record the contents of the root node of the current node.

12. List whether the current node is a leaf.

This is more common, especially in dynamic catalogs, where it is useful to find out if there are subordinate nodes in the content.

SQL code SELECT connect_by_isleaf, flfl.*
From flfl
START with sjflid is NULL
CONNECT by sjflid = PRIOR ID;

The connect_by_isleaf function is used to determine if the current node contains a subordinate node, and if it is included, it is not a leaf node, which returns 0, or 1 if it does not contain a subordinate node.

At this point, the Oracle tree query is basically finished, the data in the above example is used to the data in the project done, because the contents of the content may not be understood, so all with some new examples to illustrate. All of the above SQL is tested on this machine, also can achieve the corresponding function, but does not guarantee that the best solution to such problems (such as 8th clearly written in the stored procedure is better), if who has a better solution, or any questions about the Oracle Tree query, welcome message discussion, The above SQL has any problem also welcome the message criticism.

Oracle Tree queries and functions