oracle之树状结构的存储与展示（递归查询）

Love The Way You Lie 2021-06-26 16:06 380阅读 0赞

## （一）递归 ##

一个过程或函数在其定义或说明中有直接或间接调用自身的一种方法。

菲波那切数列就是利用递归定义的：

·F0 = 0 ·F1 = 1 ·Fn = Fn – 1 + Fn – 2

## （二）递归查询 ##

使用递归查询，需要确定初始参数和返回值。

`Oracle` 数据库长期以来一直通过专用语法（`CONNECT BY 子句`）支持递归。`Oracle Database 11g` 第 2 版通过子查询分解来支持递归，这就为解决下面的老问题提供了一个更好的新方法：查询层次结构数据。

### 1、基本语法 ###

select * from table [start with condition1] connect by [prior] id=parentid

一般用来查找存在父子关系的数据，也就是树形结构的数据；其返还的数据也能够明确的区分出每一层的数据。

*  `start with condition1`是用来限制第一层的数据，或者叫根节点数据；以这部分数据为基础来查找第二层数据，然后以第二层数据查找第三层数据以此类推。
 *  `connect by [prior] id=parentid`这部分是用来指明`oracle`在查找数据时以怎样的一种关系去查找；比如说查找第二层的数据时用第一层数据的`id`去跟表里面记录的`parentid`字段进行匹配，如果这个条件成立那么查找出来的数据就是第二层数据，同理查找第三层第四层…等等都是按这样去匹配。

`prior`还有一种用法：

select * from table [start with condition1] connect by id= [prior] parentid

*  这种用法就表示从下往上查找数据，可以理解为从叶子节点往上查找父级几点，用第一层数据的`parentid`去跟表记录里面的`id`进行匹配，匹配成功那么查找出来的就是第二层数据；上面的那种就是从父级节点往下查找叶子节点。

## （三）子查询分解 ##

子查询的使用可以进入另一个层面。考虑对视图的查询。从概念上而言，一个视图定义一个可对其执行查询的结果表。假设可以编写一个表达式，从而允许一个名称与结果表相关联。则使用该名称的查询将是一个对该结果表的查询。子查询分解（也称为公用表表达式）正是这一思想的体现。

**`WITH` 子句为子查询块指派一个名称**。之后可以使用指派的名称在某个查询中引用该查询块。

命名子查询包含两个通过 `UNION ALL` 操作组合的查询块。第一个查询块是一个初始化子查询（也称定位点），其编码是非递归的，包括确定调查起始点的种源。系统将首先处理这个子查询。第二个查询块是递归子查询，它根据与结果中已有行的关系向结果添加行。此处的技巧是定义新行与旧行的关联方式。新行是通过将命名查询与定位点确定的原始表进行联接而识别的。`UNION ALL` 将定位点与递归子查询进行组合，确保不从结果中清除重复记录。这两个查询块必须是可兼容合并的；也就是说，两个查询块中必须选择相同的列数。

子查询分解示例

WITH DTOTAL AS
    
    (SELECT CDEPT, SUM(CLABFEE) AS "TOTFEE" FROM COURSE GROUPBY CDEPT) SELECT CDEPTFROM DTOTAL WHERE TOTFEE = (SELECT MAX(TOTFEE) FROM DTOTAL);

## （四）`connect by`详解 ##

`oracle`的递归格式：

select … from tablename start with 条件1 connect by 条件2 where 条件3;

条件1 是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。

条件2 是连接条件，其中用`PRIOR`表示上一条记录，比如 `CONNECT BY PRIOR org_id = parent_id；`就是说上一条记录的`org_id` 是本条记录的`parent_id`，即本记录的父亲是上一条记录。

条件3 是过滤条件，用于对返回的所有记录进行过滤。

简单介绍如下：  
在扫描树结构表时，需要依此访问树结构的每个节点，一个节点只能访问一次，其访问的步骤如下：

> 第一步：从根节点开始；  
> 第二步：访问该节点；  
> 第三步：判断该节点有无未被访问的子节点，若有，则转向它最左侧的未被访问的子节，并执行第二步，否则执行第四步；  
> 第四步：若该节点为根节点，则访问完毕，否则执行第五步；  
> 第五步：返回到该节点的父节点，并执行第三步骤。

总之：扫描整个树结构的过程也即是中序遍历树的过程。

### 1．树结构的描述 ###

树结构的数据存放在表中，数据之间的层次关系即父子关系，通过表中的列与列间的关系来描述，如`EMP`表中的`EMPNO`和`MGR`。`EMPNO`表示该雇员的编号，`MGR`表示领导该雇员的人的编号，即子节点的`MGR`值等于父节点的`EMPNO`值。在表的每一行中都有一个表示父节点的`MGR`（除根节点外），通过每个节点的父节点，就可以确定整个树结构。

在`SELECT`命令中使用`CONNECT BY` 和`START WITH` 子句可以查询表中的树型结构关系。其命令格式如下：

SELECT . . . CONNECT BY { PRIOR 列名1=列名2|列名1=PRIOR 裂名2} [START WITH]；

其中：`CONNECT BY`子句说明每行数据将是按层次顺序检索，并规定将表中的数据连入树型结构的关系中。`PRIOR`运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系，`PRIOR`运算符在一侧表示父节点，在另一侧表示子节点，从而确定查找树结构是的顺序是自顶向下还是自底向上。

在连接关系中，除了可以使用列名外，还允许使用列表达式。`START WITH` 子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。

`START WITH`：不但可以指定一个根节点，还可以指定多个根节点。

### 2．关于`PRIOR` ###

运算符`PRIOR`被放置于等号前后的位置，决定着查询时的检索顺序。  
`PRIOR`被置于`CONNECT BY`子句中等号的前面时，则强制从根节点到叶节点的顺序检索，即由父节点向子节点方向通过树结构，我们称之为自顶向下的方式。如：

CONNECT BY PRIOR EMPNO=MGR

`PIROR`运算符被置于`CONNECT BY` 子句中等号的后面时，则强制从叶节点到根节点的顺序检索，即由子节点向父节点方向通过树结构，我们称之为自底向上的方式。例如：

CONNECT BY EMPNO=PRIOR MGR

在这种方式中也应指定一个开始的节点。

### 3．定义查找起始节点 ###

在自顶向下查询树结构时，不但可以从根节点开始，还可以定义任何节点为起始节点，以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。

### 4．使用`LEVEL` ###

在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为1，根节点的子节点为2， 依此类推。图1.2就表示了树结构的层次。

### 5．节点和分支的裁剪 ###

在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支，使用`WHERE`子句来限定树型结构中的单个节点，以去掉树中的单个节点，但它却不影响其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。

### 6．排序显示 ###

象在其它查询中一样，在树结构查询中也可以使用`ORDER BY` 子句，改变查询结果的显示顺序，而不必按照遍历树结构的顺序。  
实例：

create table article ( id number primary key, cont varchar2(4000), pid number, isleaf number(1), --0代表非叶子节点，1代表叶子节点 alevel number(2) )

--------------------

insert into article values (1, '蚂蚁大战大象', 0, 0, 0);
    insert into article values (2, '大象被打趴下了', 1, 0, 1);
    insert into article values (3, '蚂蚁也不好过', 2, 1, 2);
    insert into article values (4, '瞎说', 2, 0, 2);
    insert into article values (5, '没有瞎说', 4, 1, 3);
    insert into article values (6, '怎么可能', 1, 0, 1);
    insert into article values (7, '怎么没可能', 6, 1, 2);
    insert into article values (8, '可能性是很大的', 6, 1, 2);
    insert into article values (9, '大象进医院了', 2, 0, 2);
    insert into article values (10, '护士是蚂蚁', 9, 1, 3);
    commit;

--------------------

蚂蚁大战大象
       大象被打趴下了
          蚂蚁也不好过
          瞎说
             没有瞎说
          大象进医院了
             护士是蚂蚁
       怎么可能
             怎么不可能
             可能性是很大的

--------------------

create or replace procedure p (v_pid article.pid%type, v_level binary_integer) is cursor c is select * from article where pid = v_pid;
      v_preStr varchar2(1024) := '';
    begin
      for i in 1..v_level loop
        v_preStr := v_preStr || '****';
      end loop;
      for v_article in c loop
        dbms_output.put_line(v_preStr || v_article.cont);
      if (v_article.isleaf = 0)
    then
        p (v_article.id, v_level + 1);
      end if;
      end loop;
    end;