oracle 表依赖项的递归查询没有像我想要的那样递归
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2203671/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Recursive query for table dependencies is not recursing not as much as I'd like
提问by FrustratedWithFormsDesigner
I had an idea that I could write a query to find all the descendent tables of a root table, based on foreign keys.
我有一个想法，我可以编写一个查询来根据外键查找根表的所有后代表。
Query looks like this:
查询如下所示：
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;
The results I get look like this:
我得到的结果是这样的：
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
This result is all the child tables of ROOT_TAB, but the query does not recurse to the children of CHILD_TAB_1, CHILD_TAB_2, or CHILD_TAB_3.
这个结果是所有的子表ROOT_TAB，但查询不改乘的孩子CHILD_TAB_1，CHILD_TAB_2或CHILD_TAB_3。
Recursive queries are new to me so I'm guessing I'm missing something in the connect byclause, but I'm drawing a blank here. Is it actually possible to get the full hierarchy of ROOT_TABin a single query, or am I better off wrapping the query in a recursive procedure?
递归查询对我来说是新的，所以我猜我在connect by子句中遗漏了一些东西，但我在这里画了一个空白。实际上是否可以ROOT_TAB在单个查询中获得完整的层次结构，或者我最好将查询包装在递归过程中？
回答by Marcel Wolf
You want something like this:
你想要这样的东西：
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
The problem with the original query is that uc.constraint_name for the child table is the name of the foreign key. That is fine for connecting the first child to the root table, but it is not what you need to connect the children on the second level to the first. That is why you need to join against the constraints twice -- once to get the table's primary key, once to get the foreign keys.
原始查询的问题在于子表的 uc.constraint_name 是外键的名称。这对于将第一个孩子连接到根表来说很好，但这不是将第二级的孩子连接到第一个所需的。这就是为什么您需要针对约束加入两次——一次是为了获取表的主键，一次是为了获取外键。
As an aside, if you are going to be querying the all_* views rather than the user_* views, you generally want to join them on table_name AND owner, not just table_name. If multiple schemas have tables with the same name, joining on just table_name will give incorrect results.
顺便说一句，如果您要查询 all_* 视图而不是 user_* 视图，您通常希望在 table_name AND owner 上加入它们，而不仅仅是 table_name。如果多个模式具有相同名称的表，则仅加入 table_name 将给出不正确的结果。
回答by unbob
For the case with multiple schemas and multiple root tables, try something like:
对于具有多个模式和多个根表的情况，请尝试以下操作：
WITH constraining_tables AS (SELECT owner, constraint_name, table_name
FROM all_constraints
WHERE owner LIKE 'ZZZ%' AND constraint_type IN ('U', 'P')),
constrained_tables AS (SELECT owner, constraint_name, table_name, r_owner, r_constraint_name
FROM all_constraints
WHERE owner LIKE 'ZZZ%' AND constraint_type = 'R'),
root_tables AS (SELECT owner, table_name FROM constraining_tables
MINUS
SELECT owner, table_name FROM constrained_tables)
SELECT c1.owner || '.' || c1.table_name, LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || c1.owner || '.' || c1.table_name
FROM constraining_tables c1
LEFT JOIN
constrained_tables c2
ON c1.owner = c2.owner AND c1.table_name = c2.table_name
START WITH c1.owner || '.' || c1.table_name IN (SELECT owner || '.' || table_name FROM root_tables)
CONNECT BY PRIOR c1.constraint_name = c2.r_constraint_name
回答by guritaburongo
After deep deep investigation, I made my own version that processes all tables and retreives the table's max level in hierarchy (it reads all schemas, taking also into account the tables with no parent-child relationship, that will be at level 1 along with root ones). If you have access, use dba_ tables instead of all_ ones.
经过深入调查，我制作了自己的版本来处理所有表并在层次结构中检索表的最大级别（它读取所有模式，还考虑到没有父子关系的表，它将与根一起处于级别 1那些）。如果您有访问权限，请使用 dba_ 表而不是 all_ 表。
WITH hier AS (
SELECT child_table owner_table_name
, LEVEL lvl
, LPAD (' ', 4 * (LEVEL - 1)) || child_table indented_child_table
, sys_connect_by_path( child_table, '|' ) tree
FROM (
/*----------------------------------------------------------------------*/
/* Retrieve all tables. Set them as the Child column, and set their */
/* Parent Column to NULL. This is the root list (first iteration) */
/*----------------------------------------------------------------------*/
SELECT NULL parent_table
, a.owner || '.' || a.table_name child_table
FROM all_tables a
UNION
/*----------------------------------------------------------------------*/
/* List of all possible Parent-Child relations. This table is used as */
/* a link list, to link the current iteration with the next one, from */
/* root to last child (last child is what we are interested to find). */
/*----------------------------------------------------------------------*/
SELECT p.owner || '.' || p.table_name parent_table
, c.owner || '.' || c.table_name child_table
FROM all_constraints p, all_constraints c
WHERE p.owner || '.' || p.constraint_name = c.r_owner || '.' || c.r_constraint_name
AND (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
)
START WITH parent_table IS NULL
/*----------------------------------------------------------------------*/
/* NOCYCLE prevents infinite loops (i.e. self referencing table constr) */
/*----------------------------------------------------------------------*/
CONNECT BY NOCYCLE PRIOR child_table = parent_table
)
SELECT *
FROM hier
WHERE (owner_table_name, lvl) IN ( SELECT owner_table_name
, MAX(lvl)
FROM hier
GROUP BY owner_table_name
);
Edit: There is "kind of" an issue with this query when finding infinite loops.
编辑：查找无限循环时，此查询存在“某种”问题。
If we have this tree:
如果我们有这棵树：
b --> c --> d
b <-- c
it will assign lvl 2 to c as: b --> cand lvl 2 to b as: c --> b
它会将 lvl 2 分配给 c 为： 将 lvl 2 分配b --> c给 b 为：c --> b
for d, it will detect b --> c --> dso it will assign lvl 3
对于 d，它会检测b --> c --> d所以它会分配 lvl 3
So as you can see, the problem is inside the loop, the values from outside will always have its max correct lvl
因此，正如您所看到的，问题出在循环内部，来自外部的值将始终具有最大正确的 lvl
