How to do recursive query in Oracle

Recursive query of Oracle database is a very useful technology. Recursive query is often used when querying complex data such as tree structure, organizational structure, hierarchical relationship, etc. Recursive query allows us to use one SQL statement to complete the query of the entire tree structure without using complex loop logic to traverse level by level.

This article will introduce how to perform recursive queries in Oracle, and demonstrate an example to illustrate how to query a simple node relationship.

1. The principle of recursive query

Recursive query is implemented based on the principle of joining its own table. Self-joining refers to the process of connecting itself, and recursive query is realized by cyclic matching of each record connected to itself.

In Oracle, we usually need to use public expression WITH RECURSIVE or CTE (Common Table Expression) to implement recursive queries. Through CTE, we can create a set of virtual tables that can reference each other, thereby simplifying the process of recursive queries.

2. Syntax of recursive query

The basic syntax of recursive query is as follows:

WITH( ) AS
(

UNION ALL

)
< Recursive query body>

Among them, the recursive public expression contains two parts:

(1) The initial selection of the recursive public expression: The initial selection is the position where the recursive query starts, and it returns A collection of records matching the columns defined in the recursive public expression.

(2) The recursive part of the recursive public expression: The recursive part is a recursive query process, which will gradually expand the scope of the query.

The body of the recursive query is composed of query statements and is used to filter and display the results of the recursive query.

3. Example of recursive query

Next we will use a simple example to demonstrate how to use recursive query to query node relationships.

In the following example, we assume that there is a node relationship table with the following structure:

CREATE TABLE node(
id INT PRIMARY KEY,
node_name VARCHAR2(50) ,
parent_id INT
);

Among them, id represents the unique identifier of the node, node_name represents the name of the node, and parent_id represents the parent node ID of the current node.

Now we want to query all the child nodes of a node, we can use recursive query to achieve this.

First, we need to find an initial choice, which is where we start the query. In this example, we specify the query node ID as 1, and the SQL statement is as follows:

WITH node_cte(id, node_name, parent_id, level) AS
(

SELECT id, node_name, parent_id, 1 AS level
FROM node
WHERE id = 1
UNION ALL
SELECT n.id, n.node_name, n.parent_id, level + 1 
FROM node n, node_cte c
WHERE n.parent_id = c.id

)
SELECT * FROM node_cte;

In this example, we create a public expression named node_cte. In the initial selection, we selected the node record with node ID 1 and set its level attribute to 1.

In the recursive part, we query downwards step by step by connecting our own table and the current public expression until we find all child nodes. In the join condition, we use c.id to represent the node ID of the previous level, thus achieving the effect of recursive query.

Finally, we used the Select statement to display the query results and got the following output:

ID Node_name Parent_id Level

3 Grandchild1 2 3
4 Grandchild2 2 3
5 Child2 1 2
6 Grandchild3 5 3#stendChild4 5 3
# 2 Starting from the node with node ID 1, all child nodes are queried, and the levels increase step by step.

Summary:

This article briefly introduces the principle, syntax and an example of recursive query in Oracle database. Recursive query is an efficient and convenient query method, which is very useful when dealing with hierarchical data and organizational structure. Proficient in recursive query technology can help us process and analyze data more quickly and improve data query efficiency.

The above is the detailed content of How to do recursive query in Oracle. For more information, please follow other related articles on the PHP Chinese website!