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

（一）递归

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

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

F0 = 0
F1 = 1 
Fn = F(n – 1 )+ F(n – 2)

（二）递归查询

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

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

1、基本语法

select … from tablename

start with 条件1
connect by 条件2
where 条件3;

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

• start with condition1 是用来限制第一层的数据，或者叫根节点数据；以这部分数据为初始来查找第二层数据，然后以第二层数据查找第三层数据以此类推。注意：start with不但可以指定一个根节点，还可以指定多个根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。

• connect by [prior] id=parentid ：表示连接条件，这部分是用来指明oracle在查找数据时以怎样的一种关系去查找；比如说查找第二层的数据时用第一层数据的id去跟表里面记录的parentid字段进行匹配，如果这个条件成立那么查找出来的数据就是第二层数据，同理查找第三层第四层…等等都是按这样去匹配。

• 关键词prior用于指定查询的方向，prior旁边放子节点表示往下遍历；prior旁边放父节点表示往上遍历，即关键词prior旁边放什么字段，就表示往该字段方向遍历。

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

• connect_by_isleaf（伪列）表示递归中遍历的节点是否包含子节点，如果包含，返回0；不包含，返回1。

• level（伪列）表示递归查询中的层级，值越小层级越高，level=1为层级最高节点，从1开始，往后依次递推。

• sys_connect_by_path(column,‘分隔符’)函数表示以指定符号clear描述父节点到子节点的路径。其中column是字符型或能自动转换成字符型的列名，它的主要目的就是将父节点到当前节点的“path”按照指定的模式出现，char可以是单字符也可以是多字符，但不能使用列值中包含的字符，而且这个参数必须是常量，且不允许使用绑定变量，clear不要用逗号。注意：分隔符要用单引号

• sys_connect_by_path函数就是从start with开始的地方开始遍历，并记下其遍历到的节点，start with开始的地方被视为根节点，将遍历到的路径根据函数中的分隔符，组成一个新的字符串。

select title,substr(sys_connect_by_path(id,'->'),3) "id层级" from tb_menu m  connect by prior  m.id =  m.parent start with m.id =12;
注意：substr函数用于去除开头的->符号。

（三）子查询分解 

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

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

1、子查询语法

 with alias_name1 as    (subquery1), 
    [ alias_name2 as    (subQuery2), …… , alias_nameN as (subQueryN)]
 select col1,col2…… col3 from alias_name1,alias_name2……,alias_nameN ;
注意：alias_name1表示别名。

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

子查询分解示例

with tmp as
 (select a.*, substr(sys_connect_by_path(a.id, '/'), 2) cenji, level leaf
    from tb_menu a
   start with a.parent is null
  connect by a.parent = prior a.id)
select * from tmp where leaf = (select leaf from tmp where id = 50);

查询结果：

（四）递归遍历过程：

 1、 遍历流程简单介绍如下：
        在扫描树结构表时，需要依此访问树结构的每个节点，一个节点只能访问一次，其访问的步骤如下：
        第一步：从根节点开始；
        第二步：访问该节点；
        第三步：判断该节点有无未被访问的子节点，若有，则转向它最左侧的未被访问的子节，并执行第二步，否则执行第四步；
        第四步：若该节点为根节点，则访问完毕，否则执行第五步；
        第五步：返回到该节点的父节点，并执行第三步骤。
        总之：扫描整个树结构的过程也即是中序遍历树的过程。

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

例如：从根节点自顶向下：

select empno, mgr, level as lv  from scott.emp a

start with mgr is null

connect by (prior empno) = mgr

order by level;

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

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

 （五）存储过程

1、复杂的查询展示需要使用存储过程来实现，示例如下。

存储过程学习

实例：

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;