Oracle递归查询树形数据实例代码

下面我将为你详细讲解Oracle递归查询树形数据的实例代码。

一、什么是Oracle递归查询树形数据？

在Oracle中，递归查询树形数据是指通过递归查询从一个或多个父节点到它们的子节点一直深入到树的底部，直到找到所有子节点，然后将它们展示成一个树状结构的数据模型。

这种查询在管理关系型数据库系统中经常用于组织机构、目录结构，或其他包含层级关系的数据模型。Oracle提供了几种实现递归查询树形数据的方法，其中最常用的是使用WITH...CONNECT BY语句结合递归子查询来实现。

二、示例说明

1. 查询部门和员工的树形关系

下面是一个基本的示例，查询一家公司的所有部门和员工的树形关系：

WITH department_tree (department_id, department_name, parent_id, employee_id, employee_name) AS (
  SELECT department_id, department_name, parent_id, employee_id, employee_name
  FROM department
  LEFT JOIN employee ON department.department_id = employee.department_id AND employee.is_manager <> 1
  UNION ALL
  SELECT d.department_id, d.department_name, d.parent_id, e.employee_id, e.employee_name
  FROM department d
  INNER JOIN employee e ON d.department_id = e.department_id AND e.is_manager <> 1
  INNER JOIN department_tree dt ON d.parent_id = dt.department_id
)
SELECT RPAD('-', (LEVEL - 1) * 2, '-') || department_name AS department_name, employee_name 
FROM department_tree
WHERE parent_id IS NULL
CONNECT BY PRIOR department_id = parent_id
START WITH parent_id IS NULL;

在这个例子中，我们创建了一个递归公用表表达式（CTE）department_tree，并使用UNION ALL的方式连接了所有部门和员工的信息，并递归查询每个部门的子部门和员工。最后的结果会展示所有的部门和员工，其中部门通过“-”来标识层级的关系，并给出了每个员工的名字。

1. 查询商品树形结构

再举一个例子，查询一个拥有多个子节点的商品树形结构：

WITH product_tree (product_id, product_name, parent_product_id, level_no, seq_no) AS (
  SELECT product_id, product_name, parent_product_id, 1 AS level_no, LPAD(ROWNUM, 10, '0') AS seq_no
  FROM product
  WHERE parent_product_id IS NULL
  UNION ALL
  SELECT p.product_id, p.product_name, p.parent_product_id, level_no + 1, seq_no || '.' || LPAD(ROWNUM, 10, '0')
  FROM product p
  INNER JOIN product_tree pt ON pt.product_id = p.parent_product_id
)
SELECT RPAD('-', (level_no - 1) * 2, '-') || product_name AS product_name 
FROM product_tree
CONNECT BY PRIOR product_id = parent_product_id
START WITH parent_product_id IS NULL;

在这个例子中，我们同样使用了递归公用表表达式（CTE）product_tree来展示商品的树形层级结构。在这个实例中，每个商品都通过level_no来标识它们在整个层级中的层数，并用seq_no来记录每个商品在同一级别中的顺序。与第一个例子类似地，我们同样使用CONNECT BY语句来完成基于树形层级结构的查询。

通过以上两个例子，相信你已经学会了如何使用 Oracle 来查询树形层级结构的数据模型。

阅读剩余 23%