oracle递归查询语法

Oracle中的递归查询通常使用公共表表达式（CTE，Common Table Expressions）来实现，特别是在处理具有层次结构的数据时，如组织结构图、文件系统目录结构等。以下是Oracle递归查询的语法示例和详细解释：

递归查询语法

WITH recursive_cte_name (column_list) AS (
    -- 初始化部分（非递归部分）
    SELECT ... FROM ... WHERE ...
    UNION ALL
    -- 递归部分
    SELECT ... FROM recursive_cte_name, ... WHERE ...
)
SELECT * FROM recursive_cte_name;

示例

示例1：生成1到10的数字序列

WITH t(n) AS (
    SELECT 1 FROM dual   -- 初始化部分，生成数字1
    UNION ALL
    SELECT n + 1 FROM t WHERE n < 10  -- 递归部分，每次迭代增加1，直到n不再小于10
)
SELECT * FROM t;

这个查询会生成一个包含数字1到10的序列。

示例2：查询组织结构图中的员工路径

假设有一个员工表（employee），其中包含员工编号（emp_id）、员工姓名（emp_name）和经理编号（manager_id）。

WITH employee_path(emp_id, emp_name, path) AS (
    SELECT emp_id, emp_name, emp_name AS path
    FROM employee
    WHERE manager_id IS NULL  -- 初始化部分，从顶级员工（没有经理）开始
    UNION ALL
    SELECT e.emp_id, e.emp_name, ep.path || ' -> ' || e.emp_name
    FROM employee e
    JOIN employee_path ep ON e.manager_id = ep.emp_id  -- 递归部分，连接到上级员工
)
SELECT * FROM employee_path;

这个查询会生成一个包含每个员工及其从顶级员工到该员工的路径的列表。

总结

• 递归CTE名称：在WITH子句中定义递归CTE的名称（如上述示例中的t和employee_path）。
• 初始化部分：非递归查询，用于生成初始结果集。
• 递归部分：对CTE的引用（自身引用），用于生成后续的结果集。
• 终止条件：在递归部分中通过WHERE子句定义，当不再满足该条件时，递归将终止。

通过合理地定义初始化和递归部分，你可以使用Oracle的递归查询来解决各种具有层次结构数据的问题。