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; ——————————————-========================更多内容持续更新中=====================================————————————–