Hierarchical inquiry in Oracle
The hierarchical structure is very common in real life, so early database is a hierarchical database, and this relationship is reflected in real. Later, the hierarchical database was confirmed to be very cumbersome, so the relational database has achieved success. However, the hierarchical structure still exists, and the level in the relational database does not have a good manifestation. Oracle provides a powerful hierarchy query to solve this problem.
First, fixed and variable layer
The hierarchical relationship can be roughly divided into two.
One is a level fixed, such as the United States divided into various states, states under the county, and the county is divided into the town and the city. This situation If it is not a good choice in a table, it is easier to query and operate in three tables of the main slave structure.
The other is the hierarchical variable. As shown in the figure list:
The structural hierarchy is as follows:
Second, Oracle level query
Oracle provides the Start With and Connect By clauses of the SELECT query statement to implement hierarchical queries. To make a hierarchy, you must have two things: Identify the conditions and subcords of the root record points to its father's column name.
Look at our example, the Parent_assembly column determines the father of the record. Since the root record AirPlane has no father, the column parent_assembly is empty, while the column of other records is not empty, and the root record can be identified. The two conditions are met, the constructor is as follows:
Select assembly_id, assembly_name, parent_assembly
From bill_of_materials
START with PARENT_ASSEMBLY IS NULL
Connect by Parent_assembly = prior assembly_id;
Third, parse the CONNECT BY clause
From a point of view, Connect By can be seen as a connection specification. Implement the recursive connection, or use a standard SQL statement to connect Bill_OF_MATERIAL to itself and returns the parent record of each row.
SELECT BOM1.ASSEMBLY_ID, BOM1.ASSEMBLY_NAME, BOM2.ASSEMBLY_ID PARENT
From bill_of_materials bom1 left outer join bill_of_materials bom2
On Bom1.parent_assembly = Bom2.Assembly_id;
The records returned by this SQL statement are the same. The only difference is that the order of the line is different. Of course, the order in course is very important, and the order of the connected SQL statement returns to records cannot reflect the father and child relationship.
Many problems cannot be solved by self-connection, for example, you can't use the self-connection query to select components that just make up a plane. But Connect By can solve all the problems of all parent son structures.
Fourth, how to use WHERE clause
To filter records, there are two ways. First, start from the root node of the query, use the start with clause to limit the query specific node. For example, we can use the following statement, only query information on two seats and its components on the plane:
Select assembly_id, assembly_name, prior assembly_id parent
From bill_of_materials
Start with assembly_id in (205, 206)
Connect by Parent_assembly = prior assembly_id;
But sometimes other conditions, such as Part_Number IS Not Null, then you need to introduce the WHERE clause.
Select assembly_id, assembly_name, prior assembly_id parent
From bill_of_materialswhere part_number is not null
Start with assembly_id in (205, 206)
Connect by Parent_assembly = prior assembly_id;
The filtering condition in the above statement is to work after the Connect By statement processing is completed. When the tree has been generated, the filtering conditions began to affect the records returned by the query.
Five, level connection
To write a hierarchy query, you must understand how Oracle handles such a query.
Ø First achieve
Ø Secondary Connect By processing is on the record line returned by the connection.
Ø The filter condition defined by the WHERE clause is applied to the record line returned in the Connect BY operation.
I know the above order, let us see a query:
SELECT Assembly_id ID, PARENT_ASSEMBLY PARENT, Assembly_Name Name,
Bom.Part_Number Part, Current_Inventory Inventory
From bill_of_materials bom, part p
Where bom.part_number = p.part_number
Start with assembly_id = 200
Connect by Parent_assembly = prior assembly_id;
In the above query, the first processes the connection --bom.part_number = p.part_number.
And only the lowest level of nodes in the Bill_Of_Materials table (leaf node) is not empty in its part_number column. After both, the part_number is empty in the BILL_OF_MATERIALS table, and those columns are the parent of the leaf node. So the next step is performed, the query will not return any records because the root node of the query is not found --ASSEMBLY_ID = 200. Note: There is no root means no output.
The solution is to use external connections ( ) so that the connection by operation will succeed.
SELECT Assembly_id ID, PARENT_ASSEMBLY PARENT, Assembly_Name Name,
Bom.Part_Number Part, Current_Inventory Inventory
From bill_of_materials bom, part p
Where bom.part_number = p.part_number ( )
Start with assembly_id = 200
Connect by Parent_assembly = prior assembly_id;
The connection conditions in the WHERE clause are first processed, and the filter conditions in the WHERE clause are to work after the Connect By processing is completed.
The following query is selected to select a record of all stocks of spare parts less than 500:
SELECT Assembly_id ID, PARENT_ASSEMBLY PARENT, Assembly_Name Name,
Bom.Part_Number Part, Current_Inventory Inventory
From bill_of_materials bom, part p
Where bom.part_number = p.part_number ( )
And P.Current_INVENVENTORY <500
Start with assembly_id = 200
Connect by parent_assembly = prior assembly_id; and p.current_inventory <500 belongs to filter conditions, last processed.
You may notice that the above queries are used by the old version of the connection, very unfortunate, the Oracle 9i database exists between the Connnect By query and the new JOIN clause. The above statement is written by a new JOIN clause:
SELECT Assembly_id ID, PARENT_ASSEMBLY PARENT, Assembly_Name Name,
Bom.Part_Number Part, Current_Inventory Inventory
From bill_of_materials bom Left Outer Join PART P
On bom.part_number = p.part_number
WHERE P.CURRENT_INVENVENTORY <500
Start with assembly_id = 200
Connect by Parent_assembly = prior assembly_id;
This statement will not return any value because the root node is filtered out too early.
Of course, Oracle has already clear this bug, is being repaired.
Sixth, level
We often want to know the levels of a given record in the tree, Oracle provides Level pseudo columns. Level can appear any part of the SQL statement, and return to 1, the child of the root node returns 2, the grandson returns 3 ....
It is usually used to realize the regeneration of records for a clearer tree structure.
Seven, node path
Oracle 9i provides a new function SYS_CONNECT_BY_PATH, which gives the full path from the root node to the specified node. This function has two parameters: column names and separators, and column names are not limited to Connect By columns.
E.g:
SELECT SYS_CONNECT_BY_PATH (askEMBLY_NAME, '/') Name_Path
From bill_of_materials
Where part_number = 1019
START with PARENT_ASSEMBLY IS NULL
Connect by Parent_assembly = prior assembly_id;
Eight, sort
I want to sort the results, of course, standard ORDER BY clauses can be used, but it will destroy the father and child structure, which is generally not recommended.
Oracle 9i also provides a keyword SIBLINGS for an Order By clause that allows hierarchical data to be classified in a meaningful manner while retaining hierarchies.
Such as:
SELECT RPAD (', 2 * (level-1)) || askEMBLY_NAME Assembly_name
From bill_of_materials
Start with assembly_id = 200
Connect by parent_assembly = prior assembly_id
Order siblings by assembly_name;
Sort by alphabetical order on each level (Level), which is very useful for generated tree reports.
