Oracle递归查询简单示例

1. 概念与语法

Oracle递归查询即使用“WITH RECURSIVE”语句进行的递归查询。其基本语法如下所示：

WITH RECURSIVE subquery_name [(column_name[, column_name]...)] AS (
  initial_query
  UNION [ALL|DISTINCT]
  recursive_query
) SELECT * FROM subquery_name;

所述WITH RECURSIVE子句语法中：

• subquery_name：递归查询子句名；
• column_name：递归查询返回的列名；
• initial_query：初始查询，即递归查询的第一项；
• UNION [ALL|DISTINCT]：递归查询的联合部分，用于衔接前后两个查询，可选参数ALL和DISTINCT分别表示包含或不包含重复值；
• recursive_query：递归查询公式，用于向前递归查询；

• SELECT：最上层查询，用于指定递归查询返回的结果列。

• 示例1： Fibonacci数列

我们以Fibonacci数列作为递归查询的示例。Fibonacci数列的规则为f(n)=f(n-1)+f(n-2)，其中f(0)=0、f(1)=1。我们可以通过递归查询的方式来计算Fibonacci数列。

WITH RECURSIVE fibonacci(n, a, b) AS (
  SELECT 0, 0, 1
  UNION ALL
  SELECT n+1, b, a+b FROM fibonacci WHERE n<9
) SELECT a FROM fibonacci;

上述递归查询语句中，我们定义了名为fibonacci的递归查询，具体说明如下：

• n：当前项的编号；
• a：当前项的值；
• b：下一项的值。

在初始查询中，我们定义了初始值为f(0)=0和f(1)=1，即a=0，b=1。在递归查询中，我们定义了递归公式为a+b，并对n进行了累加，同时设置了递归条件n<9，即递归查询9次后停止。最终我们通过SELECT语句返回了所有的a值。

1. 示例2：组织结构递归

我们以一个组织结构递归查询为例，说明递归查询在实际运用中的应用。假设我们有以下一张组织结构表org：

dept_id	dept_name	parent_dept_id
1	total company	NULL
2	HR department	1
3	Finance dept	1
4	IT department	1
5	Operations	1
6	Recruiting	2
7	Payroll	3

我们需要查询所有部门的下级部门，包括子孙级别。

WITH RECURSIVE subdept(dept_id, dept_name, parent_dept_id, level) AS (
  SELECT dept_id, dept_name, parent_dept_id, 1 FROM org WHERE dept_id=1
  UNION ALL
  SELECT org.dept_id, org.dept_name, org.parent_dept_id, subdept.level+1 FROM org 
    JOIN subdept ON org.parent_dept_id=subdept.dept_id
) SELECT dept_id, dept_name, parent_dept_id, level FROM subdept ORDER BY level, dept_id;

上述递归查询语句中，我们同样定义了名为subdept的递归查询，具体说明如下：

• dept_id：部门编号；
• dept_name：部门名称；
• parent_dept_id：上级部门编号；
• level：部门层级。

在初始查询中，我们指定了初始点为总公司，即dept_id=1。在递归查询中，我们使用JOIN连接org表和subdept表，通过parent_dept_id来进行递归查询，同时对level进行累加。最终通过SELECT语句返回了所有部门的编号、名称、上级部门编号和部门层级，并对结果进行了排序。

1. 总结

递归查询可以用于解决许多需要对数据进行分级处理的场合，通过WITH RECURSIVE语句可以简单地实现递归查询，并且易于阅读和维护。