Oracle树形结构数据-相关知识总结

oracle树形结构数据--基本知识

1.数据组成

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  t.id

              , t.pid

              , t. name, level
    from qiancode.tree_table_basic 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.显式出树的级别查询

select    t.id

                 ,lpad(t.name,lengthb(t.name)+(level-1)*4)

                 ,t.pid

                 ,level
     from qiancode.tree_table_basic t
  start with t.pid='-99999'
connect by prior t.id=t.pid;

查询结果如下图所示：

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８时，长度为三。

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

select   t.id

                ,rpad(' ',(level-1)*4)||t.name   as   name

                ,t.pid,level
    from  qiancode.tree_table_basic t
 start with t.pid='-99999'
connect by prior t.id=t.pid;

查询结果如下：

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;

 

-------------------------------------------========================更多内容持续更新中=====================================--------------------------------------