Oracle recursive functions

Data in the tree structure is stored in the table. The hierarchy between the data is the parent-child relationship. It is described by the relationship between columns in the table, such as EMPNO and MGR In the EMP table. EMPNO indicates

Data in the tree structure is stored in the table. The hierarchy between the data is the parent-child relationship. It is described by the relationship between columns in the table, such as EMPNO and MGR In the EMP table. EMPNO indicates

Oracle start with connect by usage

Connect by prior recursive algorithm in oracle

In Oracle, the start with... connect by prior clause connect by is used in structured queries. Its basic syntax is:
Select... from tablename start with condition 1
Connect by condition 2
Where Condition 3;
Example:
Select * from table
Start with org_id = 'hbh1_wgwpy'
Connect by prior org_id = parent_id;

Simply put, a tree structure is stored in a table. For example, a table has two fields:
Org_id and parent_id indicate the parent of each record to form a tree structure.
You can use the preceding syntax to query all records of the tree.
Where:
Condition 1 is a restriction statement for the root node. Of course, you can relax the restriction to obtain multiple root nodes. Actually, it is multiple trees.
Condition 2 is the connection condition, where PRIOR is used to represent the previous record. For example, connect by prior org_id = parent_id indicates that the org_id of the previous record is the parent_id of this record, that is, the father of this record is the previous record.
Condition 3 is a filtering condition used to filter all returned records.

A brief introduction is as follows:
When you scan a tree structure table early, you need to access each node in the tree structure. A node can only be accessed once. The steps are as follows:
Step 1: start with the root node;
Step 2: Access the node;
Step 3: Check whether the node has any child nodes that have not been accessed. If yes, switch to the left-side unaccessed subsection of the node and perform Step 2. Otherwise, execute step 4;
Step 4: If the node is the root node, the access is completed; otherwise, step 5 is executed;
Step 5: return to the parent node of the node and perform step 3.

In short, the process of scanning the entire tree structure is also the process of traversing the tree in the middle order.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Oracle recursive query

1. Tree Structure Description
Data in the tree structure is stored in the table. The hierarchy between the data is the parent-child relationship. It is described by the relationship between columns in the table, such as EMPNO and MGR In the EMP table. EMPNO indicates the employee's number, MGR indicates the number of the person who leads the employee, that is, the MGR value of the child node is equal to the EMPNO value of the parent node. In each row of the table, there is a MGR representing the parent node (except the root node). The entire tree structure can be determined through the parent node of each node.
You can use the connect by and start with clause in the SELECT command to query the tree structure of the table. The command format is as follows:
SELECT...
Connect by {PRIOR column name 1 = column name 2 | column name 1 = PRIOR split name 2}
[Start with];
The connect by clause indicates that each row of data is retrieved in a hierarchical order and that the data in the table is connected to the tree structure. The PRIORY operator must be placed before one of the two columns of the connection relationship. For the parent-child relationship between nodes, the PRIOR operator represents the parent node on one side and the child node on the other side to determine whether the order of the tree structure is top-down or bottom-up. In addition to column names, you can also use column expressions in the join relationship. The start with clause is optional to identify the node used as the root node for searching the tree structure. If this clause is omitted, all rows that meet the query conditions are used as the root node.
Start with: You can specify not only one root node, but also multiple root nodes.
2. About PRIOR
The PRIOR operator is placed before and after the equal sign, which determines the query order.
When PRIOR is placed before a moderate number in the connect by clause, it is forced to search from the root node to the leaf node in sequence, that is, the parent node is directed to the child node through the tree structure, we call it a top-down approach. For example:
Connect by prior empno = MGR
When the PIROR operator is placed behind a moderate number in the connect by clause, it forces the sequential retrieval from the leaf node to the root node, that is, the child node is directed to the parent node through the tree structure, we call it a bottom-up approach. For example:
Connect by empno = PRIOR MGR
In this way, you should also specify a Start node.
3. Define the Start Node for search
When querying the tree structure from top to bottom, you can not only start from the root node, but also define any node as the Start Node to start searching down. In this way, the search result is a branch of the structure tree starting with this node.
4. Use LEVEL
In a table with a tree structure, each row of data is a node in the tree structure. Because the nodes are in different levels, each row of records can have a layer number. The layer number is determined based on the distance between the node and the root node. No matter which node starts, the layer number of the starting root node is always 1, and the child node of the root node is 2.
5. Crop nodes and branches
When querying the tree structure, you can remove some rows in the table, or cut off a branch in the tree, and use the WHERE clause to limit a single node in the tree structure ,, to remove a single node in the tree, but it does not affect its child nodes (top-down retrieval) or its predecessors (top-down retrieval ).
6. Sort display
Like other queries, the order by clause can also be used in tree structure queries to change the display ORDER of query results without having to traverse the tree structure.

Bytes ----------------------------------------------------------------------------------------------------------

Oracle provides the start with connect by syntax structure to implement recursive queries.

1. A simple example:
SQL> select * from test;

BILL_MONTH DAY_NUMBER MSISDN
--------------------------------------------------
200803 1 13800
200803 3 13800
200803 2 13800
200803 2 13801
200803 4 13804
200803 5 13804
200803 7 13804
200803 8 13804
200803 6 13802
200803 6 13801
200803 7 13801
200803 8 13801

12 rows selected

SQL>
SQL> select * from test
2 start with day_number = 1
3 connect by prior day_number = day_number-1 and prior msisdn = msisdn
4;

BILL_MONTH DAY_NUMBER MSISDN
--------------------------------------------------
200803 1 13800
200803 2 13800
200803 3 13800

SQL>

The preceding statement finds the data starting from 1 and increasing from day_number to 1.