--查找看当前节点属于第几层
--Oracle 9i 开始,可以通过
--SYS_CONNECT_BY_PATH
--函数实现将从父节点到当前行内容
--以“path”或者层次元素列表的形式显示出来
select level, sys_connect_by_path(sdc.class_name, '/') path
from std_download_class sdc
start with sdc.parent_class_id = 'ROOT'
connect by prior sdc.ID = SDC.PARENT_CLASS_ID;
--查找最底层节点(最低层节点)
select connect_by_isleaf,sys_connect_by_path(sdc.class_name,'/') path
from std_download_class SDC
start with sdc.parent_class_id = 'ROOT' connect by prior sdc.ID = SDC.PARENT_CLASS_ID;
--查找当前节点的最根节点
select connect_by_root sdc.class_name 根节点名称 ,sys_connect_by_path(sdc.class_name,'/') path
from std_download_class SDC
start with sdc.parent_class_id = 'ROOT' connect by prior sdc.ID = SDC.PARENT_CLASS_ID;
--使用START WITH . . . CONNECT BY . . .
--从句可将父级地区与子级地区连接起来
--,并将其层次等级显示出来。
select level,lpad(' ',level*3)||sdc.class_name class_name from std_download_class SDC
start with sdc.parent_class_id = 'ROOT' connect by prior sdc.ID = SDC.PARENT_CLASS_ID;
---防止死循环引用树结构类型
--在Oracle 10g 之前的版本中,如果在你的树中出现了环状循环
--(如一个孩子节点引用一个父亲节点),Oracle 就会报出一个错误提示:
--“ ORA-01436: CONNECT BY loop in user data”。如果不删掉对父亲的引
--用就无法执行查询操作。而在 Oracle 10g 中,只要指定“NOCYCLE”就可以
--进行任意的查询操作。与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE,
--如果在当前行中引用了某个父亲节点的内容并在树中出现了循环,那么该行的伪
--列中就会显示“1”,否则就显示“0”。而这个功能就可以帮助我们校验一直让我们头
--疼不已的BOM结构死循环(大多由于数据导入形成的),用例:
--表结构:
--create table tree2(parent number,child number);
--insert into tree2 values(null,1);
--insert into tree2 values(1,2);
--insert into tree2 values(2,3);
--insert into tree2 values(3,1);
select connect_by_iscycle,sys_connect_by_path(child,'/') path from tree2 t
start with parent is null connect by nocycle prior child = parent;
select connect_by_iscycle , sys_connect_by_path(t.child,'/') from
tree2 t start with t.parent is null connect by nocycle prior
t.child = t.parent ;
--
select connect_by_iscycle,
sys_connect_by_path(sdc.class_name, '/') path from
std_download_class SDC start with sdc.parent_class_id = 'ROOT' connect by nocycle
prior sdc.ID = SDC.PARENT_CLASS_ID;
--Oracle 9i 开始,可以通过
--SYS_CONNECT_BY_PATH
--函数实现将从父节点到当前行内容
--以“path”或者层次元素列表的形式显示出来
select level, sys_connect_by_path(sdc.class_name, '/') path
from std_download_class sdc
start with sdc.parent_class_id = 'ROOT'
connect by prior sdc.ID = SDC.PARENT_CLASS_ID;
--查找最底层节点(最低层节点)
select connect_by_isleaf,sys_connect_by_path(sdc.class_name,'/') path
from std_download_class SDC
start with sdc.parent_class_id = 'ROOT' connect by prior sdc.ID = SDC.PARENT_CLASS_ID;
--查找当前节点的最根节点
select connect_by_root sdc.class_name 根节点名称 ,sys_connect_by_path(sdc.class_name,'/') path
from std_download_class SDC
start with sdc.parent_class_id = 'ROOT' connect by prior sdc.ID = SDC.PARENT_CLASS_ID;
--使用START WITH . . . CONNECT BY . . .
--从句可将父级地区与子级地区连接起来
--,并将其层次等级显示出来。
select level,lpad(' ',level*3)||sdc.class_name class_name from std_download_class SDC
start with sdc.parent_class_id = 'ROOT' connect by prior sdc.ID = SDC.PARENT_CLASS_ID;
---防止死循环引用树结构类型
--在Oracle 10g 之前的版本中,如果在你的树中出现了环状循环
--(如一个孩子节点引用一个父亲节点),Oracle 就会报出一个错误提示:
--“ ORA-01436: CONNECT BY loop in user data”。如果不删掉对父亲的引
--用就无法执行查询操作。而在 Oracle 10g 中,只要指定“NOCYCLE”就可以
--进行任意的查询操作。与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE,
--如果在当前行中引用了某个父亲节点的内容并在树中出现了循环,那么该行的伪
--列中就会显示“1”,否则就显示“0”。而这个功能就可以帮助我们校验一直让我们头
--疼不已的BOM结构死循环(大多由于数据导入形成的),用例:
--表结构:
--create table tree2(parent number,child number);
--insert into tree2 values(null,1);
--insert into tree2 values(1,2);
--insert into tree2 values(2,3);
--insert into tree2 values(3,1);
select connect_by_iscycle,sys_connect_by_path(child,'/') path from tree2 t
start with parent is null connect by nocycle prior child = parent;
select connect_by_iscycle , sys_connect_by_path(t.child,'/') from
tree2 t start with t.parent is null connect by nocycle prior
t.child = t.parent ;
--
select connect_by_iscycle,
sys_connect_by_path(sdc.class_name, '/') path from
std_download_class SDC start with sdc.parent_class_id = 'ROOT' connect by nocycle
prior sdc.ID = SDC.PARENT_CLASS_ID;