查询深入理解Oracle中的树形递归查询（oracle中的树形递归）

深入理解Oracle中的树形递归查询

在Oracle数据库中，树形递归查询是非常常见和重要的功能。它允许我们在一个树形结构的表中，沿着树形结构来递归查询数据。这种数据结构通常出现在组织架构、财务科目等应用场景中。在这篇文章中，我们将深入了解Oracle中的树形递归查询，包括如何创建和使用一张树形表。

创建树形表

在Oracle中，树形表不是一种特别的数据类型，而是一种具有树形结构特征的普通表。我们可以通过在表中添加特定的字段，来表示节点之间的关系。在这里，我们将创建一个简单的树形表，来说明如何表示关系。

我们创建一个名为EMP的基础表，包含员工ID、姓名、经理ID三个字段。其中，经理ID指向该员工的直接经理。

“`sql

CREATE TABLE EMP (

EMP_ID NUMBER PRIMARY KEY,

EMP_NAME VARCHAR2(20),

MANAGER_ID NUMBER

);

接下来，我们需要添加两个额外的字段来表示树形关系。这两个字段分别是：

- PATH：从根节点到当前节点的所有祖先节点的路径
- LEVEL：当前节点在整棵树中所处的深度级别。
```sql
ALTER TABLE EMP 
ADD (PATH VARCHAR2(4000), LEVEL NUMBER);

路径值的格式为“/父节点1/父节点2/当前节点ID”，表示当前节点的父节点集合，以及该节点本身的ID。由于每个节点可以有多个父节点，因此路径值是一个完整的字符串数组。

接下来，我们需要完成两个触发器来维护这两个字段的值。当插入新记录时，触发器将获取该记录的经理ID，并在路径中添加该ID和所有经理的路径。当更新记录时，触发器将检查是否需要更新路径和级别的值。

“`sql

CREATE OR REPLACE TRIGGER EMP_PATH_TRIG

BEFORE INSERT OR UPDATE ON EMP

FOR EACH ROW

DECLARE

curr_mgr NUMBER := :NEW.MANAGER_ID;

new_path VARCHAR2(4000) := ”;

new_lvl NUMBER := :NEW.LEVEL;

BEGIN

WHILE curr_mgr IS NOT NULL

LOOP

SELECT PATH INTO new_path FROM EMP WHERE EMP_ID = curr_mgr ;

new_path := new_path || ‘/’ || curr_mgr;

curr_mgr := (SELECT MANAGER_ID FROM EMP WHERE EMP_ID = curr_mgr);

END LOOP;

:NEW.PATH := new_path || ‘/’ || :NEW.EMP_ID;

:NEW.LEVEL := LENGTH(:NEW.PATH) – LENGTH(REPLACE(:NEW.PATH,’/’,”)) + 1;

END;

上述触发器会递归获取当前记录的所有祖先节点，直到根节点。path 值将这些节点ID串联起来，并加上当前节点ID。LEVEL值等于path中 "/"的数量+1。

使用ANSI SQL进行递归查询

在Oracle中，我们可以使用ANSI SQL和CONNECT BY来进行递归查询。CONNECT BY是一个非标准SQL查询语句，它是Oracle数据库中专门用来操作树形结构的语言元素。它接受一个递归查询，以及递归查询的起始点。

以下是一个使用CONNECT BY进行递归查询的示例：

```sql
SELECT LPAD(' ', LEVEL*4, ' ') || EMP_NAME AS NAME, MANAGER_ID
FROM EMP
START WITH EMP_ID = 1 
CONNECT BY PRIOR EMP_ID = MANAGER_ID;

上述查询使用START WITH语句指定起始点，然后使用CONNECT BY定义递归关系。PRIOR关键字表示当前节点的父节点，EMP_ID和MANAGER_ID表示员工ID和经理ID。查询结果将包括组织结构树的所有节点，以及它们的ID和级别。

使用ANSI SQL进行带参递归查询

有时，我们需要根据条件来生成树形图，例如，我们可能需要提取特定部门下的全部员工。我们可以在CONNECT BY中使用 “WHERE 子句”来过滤表中不相关的数据。如下所示：

“`sql

SELECT LPAD(‘ ‘, LEVEL*4, ‘ ‘) || EMP_NAME AS NAME, MANAGER_ID

FROM EMP

START WITH EMP_ID = 1

CONNECT BY PRIOR EMP_ID = MANAGER_ID

WHERE MANAGER_ID = 1;

在这个查询中，我们使用了WHERE子句来筛选出所有经理ID为1的员工。查询结果会列出该部门下的所有员工对应的树形图。

使用PL/SQL进行递归查询

虽然使用ANSI SQL语句进行递归查询是常见的做法，但这种方法在处理大型树形结构时可能会出现性能问题。在这种情况下，我们可以使用PL/SQL实现递归查询。

使用PL/SQL实现递归查询需要创建一个递归的函数或存储过程。函数将接受一个节点id的参数，并递归获取该节点下的所有子节点。

以下是一个简单的PL/SQL函数，可用于递归获取子节点：

```sql
CREATE OR REPLACE FUNCTION get_child_emp(emp_id IN NUMBER) 
RETURN SYS_REFCURSOR IS 

  TYPE ref_cursor IS REF CURSOR; 
  v_cursor ref_cursor; 
  v_sql VARCHAR2(2000); 

BEGIN 
  v_sql := 'SELECT EMP_ID, EMP_NAME, MANAGER_ID FROM EMP WHERE MANAGER_ID=:EMPID'; 
  OPEN v_cursor FOR v_sql USING emp_id; 
  RETURN v_cursor; 
END;

该函数使用了REF CURSOR作为返回值，因为在PL/SQL中，我们需要使用显式的游标来获取结果集。函数执行后，返回键值对：EMP_ID、EMP_NAME 和 MANAGER_ID，可以在其他 PL/SQL 代码中进行使用。

比较CONNECT BY 和PL/SQL递归

使用ANSI SQL和CONNECT BY递归查询可以简化查询和维护，对于小型和中型树形结构而言非常有效。但是，对于大型、高度分层的树形结构，我们应该考虑使用PL/SQL，因为：

– PL/SQL 递归查询在处理大型树形结构时更有效，因为该方法可以更容易地处理包括超过1000层的树形结构。

– PL/SQL可以使用过程的结果，将其存放在数据表或者其他变量中进行后续处理。

总结

此文简单介绍了如何在Oracle数据库中创建并使用树形表，以及如何使用ANSI SQL和PL/SQL对树形表进行递归查询。我们需要根据实际情况选择合适的方法来查询数据，以便实现更高效的数据查询。