我有一个想法,我可以编写一个查询来查找根表的所有后代表,基于外键.

查询看起来像这样：

select level,lpad(' ',2 * (level - 1)) || uc.table_name as "TABLE",uc.constraint_name,uc.r_constraint_name
from all_constraints uc
where uc.constraint_type in ('R','P')
start with uc.table_name = 'ROOT_TAB'
connect by nocycle prior uc.constraint_name = uc.r_constraint_name
order by level asc;

结果我看起来像这样：

        1   ROOT_TAB        XPKROOTTAB  
        1   ROOT_TAB        R_20           XPKPART_TAB
        2     CHILD_TAB_1   R_40           XPKROOTTAB
        2     CHILD_TAB_2   R_115          XPKROOTTAB
        2     CHILD_TAB_3   R_50           XPKROOTTAB

此结果是ROOT_TAB的所有子表,但查询不会递归到CHILD_TAB_1,CHILD_TAB_2或CHILD_TAB_3的子项.

递归查询对我来说是新的,所以我猜我在connect by子句中缺少一些东西,但我在这里画一个空白.实际上是否可以在单个查询中获得ROOT_TAB的完整层次结构,或者我最好在递归过程中包装查询？

解决方法

你想要这样的东西：

select t.table_name,level,2 * (level - 1))||t.table_name 
from user_tables t
join user_constraints c1 
    on (t.table_name = c1.table_name 
    and c1.constraint_type in ('U','P'))
left join user_constraints c2 
    on (t.table_name = c2.table_name 
    and c2.constraint_type='R')
start with t.table_name = 'ROOT_TAB'
connect by prior c1.constraint_name = c2.r_constraint_name

原始查询的问题是子表的uc.constraint_name是外键的名称.这对于将第一个子节点连接到根表是很好的,但是不需要将第二个节点上的子节点连接到第一个节点.这就是为什么你需要连接两次约束 – 一次获取表的主键,一次获取外键.

顺便说一句,如果您要查询all_ *视图而不是user_ *视图,通常需要将它们连接到table_name AND owner,而不仅仅是table_name.如果多个模式具有相同名称的表,则仅加入table_name将得到不正确的结果.