Oracle SQL递归的使用详解

什么是递归

递归是指一个过程在执行过程中调用自身的行为。在SQL中，递归可以用来解决一些需要遍历树状结构或者图状结构的问题，例如：递归地查找组织机构内的成员，递归地计算利润分配等。

如何使用递归

使用递归的核心是编写递归查询语句，也就是含有自引用的查询语句。这里我们主要讲解Oracle SQL中如何使用递归。

Oracle SQL 递归的语法

下面是Oracle SQL中递归查询的语法：

WITH recursive_cte (column1, column2, ..., columnN, level) AS (
    SELECT column1, column2, ..., columnN, 1
    FROM table1
    WHERE condition1

    UNION ALL

    SELECT column1, column2, ..., columnN, level + 1
    FROM table1 t1
    JOIN recursive_cte cte ON t1.join_column = cte.join_column
    WHERE condition2
)
SELECT column1, column2, ..., columnN, level
FROM recursive_cte
WHERE condition3;

其中：

• recursive_cte：递归公共表达式名称。
• column1, column2, ..., columnN：需要查询的列名。
• level：递归层数。
• table1：需要递归的表名。
• condition1：需要递归的初始条件。
• UNION ALL：递归连接符。
• condition2：递归结束条件。
• condition3：查询条件，可选项。

递归查询示例

示例一

下面是一个递归查询组织机构中所有的成员代码：

WITH recursive_cte (member_id, member_name, leader_id, level) AS (
    SELECT member_id, member_name, leader_id, 1
    FROM member
    WHERE leader_id IS NULL

    UNION ALL

    SELECT member.member_id, member.member_name, member.leader_id, recursive_cte.level + 1
    FROM member
    JOIN recursive_cte 
    ON member.leader_id = recursive_cte.member_id
)
SELECT member_name, level
FROM recursive_cte;

该查询会从 member 表中找到领导为null的成员，然后往下递归查询，递归结束的条件是没有下属了，最终返回所有成员的姓名和所在层数。

示例二

下面是一个递归查询公司中各项业务利润的代码：

WITH recursive_cte (business_id, parent_business_id, profit) AS (
    SELECT business_id, parent_business_id, profit
    FROM business
    WHERE business_type = 'PRODUCT'

    UNION ALL

    SELECT business.business_id, business.parent_business_id, 
           business.profit * recursive_cte.profit AS profit
    FROM business
    JOIN recursive_cte ON business.parent_business_id = recursive_cte.business_id
    WHERE business.business_type = 'SUB_PRODUCT'
)
SELECT SUM(profit) as total_profit
FROM recursive_cte;

该查询会从 business 表中找到 business_type 为 PRODUCT 的业务利润，并往下递归查询，直到查询到所有的 business_type 为 SUB_PRODUCT 的业务利润。最终返回所有业务利润的总和。

总结

递归查询是一种非常有用的查询方式，能够解决一些复杂的树状结构或图状结构问题。在使用时，需要注意递归查询的语法，并且避免出现死循环的情况。