Oracle递归查询

发布时间：2023-05-20

Oracle递归查询概述

Oracle递归查询是指在Oracle SQL中使用CONNECT BY语句实现递归查询。递归查询可以处理对自身表的查询，也可以处理对其他表的查询。使用递归查询可以让我们轻松地查询树形结构数据，如组织架构、商品分类等，从而简化了查询的复杂度。

Oracle递归查询的语法

使用Oracle递归查询需要用到CONNECT BY语句，它的语法结构为：

SELECT ...
FROM table_name
[WHERE condition]
START WITH condition
CONNECT BY [NOCYCLE] condition
[ORDER SIBLINGS BY column_name [, column_name ...]]

其中：

  • table_name：要查询的表名。
  • condition：连结表的条件，显然需要会做基本的SQL查询。
  • START WITH：从指定的记录开始递归查询。
  • CONNECT BY：连结的条件，控制递归的执行过程。
  • NOCYCLE：防止出现环形递归。
  • ORDER SIBLINGS BY：兄弟节点排序，这只有在使用指定列顺序显示子节点的时候才有用。

Oracle递归查询的案例分析

Case 1：查询树形结构数据

需求：有一个公司组织架构，其中涉及到多个部门和员工。现在需要查询每个部门的所有员工及其子部门的员工。 假设有以下表结构：departments表保存部门信息，其中DEPARTMENT_ID为部门编号，DEPARTMENT_NAME为部门名称，PARENT_ID为其上级部门的编号；employees表保存员工信息，其中EMPLOYEE_ID为员工编号，EMPLOYEE_NAME为员工名称，DEPARTMENT_ID为其所在部门的ID。

CREATE TABLE departments (
    department_id NUMBER(10),
    department_name VARCHAR2(100),
    parent_id NUMBER(10)
);
INSERT INTO departments VALUES (1, '总经理办公室', NULL);
INSERT INTO departments VALUES (10, '市场部', 1);
INSERT INTO departments VALUES (11, '销售部', 10);
INSERT INTO departments VALUES (12, '运营部', 10);
INSERT INTO departments VALUES (20, '技术部', 1);
INSERT INTO departments VALUES (21, '研发部', 20);
INSERT INTO departments VALUES (22, '测试部', 20);
INSERT INTO departments VALUES (30, '行政部', 1);
INSERT INTO departments VALUES (31, '人事部', 30);
INSERT INTO departments VALUES (32, '财务部', 30);
CREATE TABLE employees (
    employee_id NUMBER(10),
    employee_name VARCHAR2(100),
    department_id NUMBER(10)
);
INSERT INTO employees VALUES (1001, '张三', 31);
INSERT INTO employees VALUES (1002, '李四', 31);
INSERT INTO employees VALUES (2001, '王五', 21);
INSERT INTO employees VALUES (2002, '赵六', 21);
INSERT INTO employees VALUES (11001, '小明', 11);
INSERT INTO employees VALUES (11002, '小红', 11);
INSERT INTO employees VALUES (21001, '小李', 21);
INSERT INTO employees VALUES (21002, '小刘', 21);
INSERT INTO employees VALUES (22001, '小白', 22);
INSERT INTO employees VALUES (22002, '小黑', 22);

现在我们可以编写SQL语句来实现递归查询员工信息：

SELECT d.department_name,
       e.employee_id,
       e.employee_name,
       LEVEL
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
START WITH d.department_id = 1
CONNECT BY PRIOR d.department_id = d.parent_id
ORDER SIBLINGS BY d.department_id, e.employee_id;

这个SQL语句由以下几部分组成：

  • 首先是SELECT子句，用于选择要返回的列。
  • 然后是FROM子句，联结了departments表和employees表，在后续的递归查询中用到。
  • 接着是START WITH关键字，表示从哪个节点开始递归查询，这里从根节点1开始。
  • 然后是CONNECT BY关键字，表示查询条件，即两张表之间的连结条件。
  • PRIOR关键字是一个占位符，指代上一级关系的行。
  • ORDER SIBLINGS BY关键字用于根据指定列对同一父节点的子节点进行排序。 查询结果如下：
DEPARTMENT_NAME    EMPLOYEE_ID   EMPLOYEE_NAME    LEVEL
总经理办公室       NULL         NULL              1
人事部             1001         张三              2
人事部             1002         李四              2
市场部             NULL         NULL              2
市场部             11001        小明              3
市场部             11002        小红              3
运营部             NULL         NULL              3
技术部             NULL         NULL              2
研发部             2001         王五              3
研发部             2002         赵六              3
测试部             NULL         NULL              3
行政部             NULL         NULL              2
财务部             NULL         NULL              2

Case 2：查询备选关系

需求：有一批供应商有备选关系，即可用B替代A、C替代B、D替代C，现在需要查询所有备选关系。 假设有以下备选关系表:

CREATE TABLE supplier_relationship (
    id NUMBER(10),
    src_id NUMBER(10),
    tgt_id NUMBER(10)
);
INSERT INTO supplier_relationship VALUES (1, 1, 2);
INSERT INTO supplier_relationship VALUES (2, 2, 3);
INSERT INTO supplier_relationship VALUES (3, 3, 4);

现在我们可以编写SQL语句来实现递归查询备选关系信息：

WITH supplier_cte(id, src_id, tgt_id, recursion_level) AS (
  SELECT id, src_id, tgt_id, 0 FROM supplier_relationship 
  UNION ALL 
  SELECT s.id, s.src_id, cte.tgt_id, cte.recursion_level + 1 
	FROM supplier_cte cte JOIN supplier_relationship s ON cte.src_id = s.tgt_id
	WHERE cte.recursion_level < 3 
) 
SELECT DISTINCT src_id, tgt_id FROM supplier_cte;

这个SQL语句由以下几部分组成：

  • 使用WITH关键字创建CTE，supplier_cte是递归查询的占位符。
  • 在初始查询中，我们选择了所有行，并把它们的Recursion_Level设为0。
  • UNION ALL连接了两个SELECT语句。在第二个SELECT语句中，我们添加了更深一层的层次，并将Recursion_Level加1。
  • WHERE子句中的cte.recursion_level < 3表示递归深度小于3时才进行下一次递归。 查询结果如下：
SRC_ID  TGT_ID
1       2
2       3
3       4
1       3
2       4
1       4

Oracle递归查询的注意事项

  1. 递归查询存在性能问题。在大量数据的情况下，递归查询可能会出现性能问题，建议在执行查询时限定递归的深度。
  2. 避免环形递归。在编写递归查询时，应该注意依赖关系的循环问题，防止出现环形递归。
  3. 使用时应该根据需求优化SQL语句。Oracle递归查询虽然功能强大，但我们在使用的时候一定要根据需求对SQL语句进行优化，保证查询效率。

总结

Oracle递归查询可以轻松地查询树形结构数据，从而简化了查询的复杂度。在编写递归查询时，我们应该注意依赖关系的循环问题，防止出现环形递归，并对SQL语句进行优化，保证查询效率。同时，我们也可以将递归查询用于查询备选关系，这也是Oracle递归查询的一种十分实用的应用场景。

推荐文章