oracle - 表依赖项的递归查询没有像我想要的那样递归

Question

我有一个想法，我可以编写一个查询来根据外键查找根表的所有后代表。

查询如下所示:

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 的完整层次结构？在单个查询中，还是我最好将查询包装在递归过程中？

Answer 1

你想要这样的东西:

select t.table_name, level,lpad(' ', 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 是外键的名称。这对于将第一个 child 连接到根表来说很好，但这不是将第二级上的 child 连接到第一个所需的。这就是为什么您需要针对约束加入两次——一次是为了获得表的主键，一次是为了获得外键。

顺便说一句，如果您要查询 all_* View 而不是 user_* View ，您通常希望在 table_name AND owner 上加入它们，而不仅仅是 table_name。如果多个模式具有相同名称的表，则仅加入 table_name 将给出不正确的结果。