Oracle递归函数查询树形结构数据-connect by ,level的使用

1.数据组成

PS:文末有insert语句，注意最后一列的列名为leval

2.基本查询

2.1.查询某节点及该节点下的所有子孙节点

SELECT   * 

     FROM QIANCODE.TREE_TABLE_BASIC T

  START WITH T.ID='111'

CONNECT BY PRIOR  T.ID=T.PID

结果如下所示：

 

注意：若prior关键字缺省：则只能查询到符合条件的起始行，并不进行递归查询；
SELECT  *

    FROM QIANCODE.TREE_TABLE_BASIC T

  START WITH T.ID='111'

CONNECT BY  T.ID=T.PID

结果如下所示：

2.查询某节点及该节点上的所有祖先节点

SELECT   *

     FROM QIANCODE.TREE_TABLE_BASIC T

  START WITH T.ID='111'

CONNECT BY PRIOR T.PID=T.ID;

结果如下图所示：

3.按层级展示某节点下的所有子节点(LEVEL的使用；注意where条件放在start with之前，order by 放最后)

/*在树形结构节点很多的情况一下，一般会采用异步刷新的方式进行，在默认加载的情况下，会展开到某个层级。
这种情况下，不但要获取某个节点的祖先节点，还需要获取祖先节点的兄弟节点，在这种情况下可以通过level进行*/

SELECT   id,name,pid,leval,level 

    FROM  bianli T
 WHERE LEVEL >= 1
 START WITH T.ID = '111'
CONNECT BY PRIOR T.ID = T.PID
 ORDER BY LEVEL, ID;

显式结果如下：

 

注意：level显式的是伪列，是按当前查询出来的结果进行层级排序。

          所以这里在原数据中层级为4的班主任在当前查询中level为2.

 4.显式出树的级别查询

4.1.RPAD()和LPAD()函数的使用

RPAD(string,length,[pad_string])、LPAD(string,length,[pad_string])：从左或往右使用指定的字符串pad_string对string进行填充；

                                                                                                                       pad_string可省略，默认使用空格填充；

                                                                                                                       length表示字符最终返回的总长度。

如下查询：

select   rpad('aabbcc',2,'hh')      from   dual;    --返回 ‘aa’
select   rpad('aabbcc',12,'hh')    from   dual;    --返回 ‘aabbcchhhhhh’

4.2.区别函数lengthb(string)和length(string)

lengthb(string)：计算string所占的字节长度：返回字符串的长度，单位是字节。

length(string)：计算string所占的字符长度：返回字符串的长度，单位是字符。

 

如下查询：

SELECT LENGTHB('中国') FROM DUAL;   -- 返回 6
SELECT LENGTH('中国') FROM DUAL;     -- 返回 2

对于单字节字符,LENGTHB和LENGTH是一样的.可以用length(‘string’)=lengthb(‘string’)判断字符串是否含有中文。
注：一个汉字在Oracle数据库里占多少字节跟数据库的字符集有关，UTF８时，长度为三。

select t.id,LENGTHB(name),4*(leval-1),Lpad(t.name,LENGTHB(name)+4*(leval-1)),
Lpad(t.name,leval*7),RPAD('  ',4*(leval-1)）||name，
t.pid,t.leval from BIANLI t START WITH pID='-99999' CONNECT BY PRIOR ID=PID

上面的语句RPAD('  ',4*(leval-1)）中注意是'   '而不是'' 

4.3.巧妙利用函数RPAD(),展示更整齐

 从上面的效果看最后第三列的显示更好看

5.其他常用

SELECT   T.ID
                ,T.NAME
                ,T.PID
                ,CONNECT_BY_ISLEAF                                LEAF               -- 判断是否为叶结点,O否1是
                ,SYS_CONNECT_BY_PATH(T.NAME,'|')       PATH              -- 遍历的路径
                ,CONNECT_BY_ROOT(T.NAME)                   ROOT             -- 遍历根结点         
                 --,CONNECT_BY_ISCYCLE                          ISCYCLE         -- 查询树是否有环路【使用connect_by_iscycle时，必须加上nocycle关键字】
                 ,level                                                               LEVELS           -- 结点所属树的层数
       FROM QIANCODE.TREE_TABLE_BASIC T
 START WITH T.ID='1'
CONNECT BY PRIOR T.ID=T.PID;

 

查询结果如下：

 

特别说明：connect_by_iscycle：伪列，验证这个数是否有环
                  适用情景：验证配置树是否有环，并查出是哪个结点
                 1.修改表数据，使得表数据出现环路
                    update QIANCODE.TREE_TABLE_BASIC T set t.pid='11111' where t.id='1';commit;
                  2.再执行以上connect_by_root()查询语句报错   ——>   ora-01436：“用户数据中的connect by 循环”
出现环路时问题解决如下：

                3.1.检查是哪个结点出现问题【使用connect_by_iscycle时，必须加上nocycle关键字】
                    SELECT    t.id

                                     ,t.name

                                     ,t.pid,connect_by_iscycle
                         FROM QIANCODE.TREE_TABLE_BASIC T
                       START WITH T.ID='1'
                   CONNECT BY nocycle PRIOR t.id=t.pid;

                   查询结果如下：（向下查出id=‘1111’的节点出现环路）

                   

                3.2.根据上面查出的节点id，向上遍历找到问题结点

                   SELECT     t.id

                                     ,t.name

                                     ,t.pid,connect_by_iscycle
                        FROM QIANCODE.TREE_TABLE_BASIC T
                      START WITH T.ID='11111'
                   CONNECT BY nocycle PRIOR t.pid=t.id;

                    查询结果如下：

                                  即可得出环路出现在id=‘1’和id=‘1111’首尾两个节点
             4.恢复数据
                update QIANCODE.TREE_TABLE_BASIC T set t.pid='-99999' where t.id='1';commit;

 

原文：https://www.cnblogs.com/zhoudaqianhaha/p/9776619.html

 

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('1', '校长', '-99999', '1');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11', '副校长1', '1', '2');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('111', '-年级教导主任', '11', '3');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('1111', '一班班主任', '111', '4');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11111', '一班语文老师', '1111', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11112', '一班数学老师', '1111', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('1112', '二班班主任', '111', '4');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11113', '二班语文老师', '1112', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11114', '二班数学老师', '1112', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('112', '二年级教导主任', '11', '3');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('1113', '三班班主任', '112', '4');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11115', '三班语文老师', '1113', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11116', '三班数学老师', '1113', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('1114', '四班班主任', '112', '4');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11117', '四班语文老师', '1114', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11118', '四班数学老师', '1114', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('12', '副校长2', '1', '2');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('113', '三年级教导主任', '12', '3');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('1115', '五班班主任', '113', '4');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11119', '五班语文老师', '1115', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11120', '五班数学老师', '1115', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('1116', '六班班主任', '113', '4');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11121', '六班语文老师', '1116', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11122', '六班数学老师', '1116', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('114', '四年级教导主任', '12', '3');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('1117', '七班班主任', '114', '4');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11123', '七班语文老师', '1117', '4');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11124', '七班数学老师', '1117', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('1118', '八班班主任', '114', '4');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11125', '八班语文老师', '1118', '5');

insert into BIANLI (ID, NAME, PID, LEVAL)
values ('11126', '八班数学老师', '1118', '5');