oracle 使用递归查询构建表依赖关系图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/87877/
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
Building a Table Dependency Graph With A Recursive Query
提问by Mark Roddy
I am trying to build a dependency graph of tables based on the foreign keys between them. This graph needs to start with an arbitrary table name as its root. I could, given a table name look up the tables that reference it using the all_constraints view, then look up the tables that reference them, and so on, but this would be horrible inefficient. I wrote a recursive query that does this for all tables, but when I add:
我正在尝试根据表之间的外键构建表的依赖关系图。该图需要以任意表名作为根开始。我可以，给定一个表名，使用 all_constraints 视图查找引用它的表，然后查找引用它们的表，依此类推，但这将是非常低效的。我编写了一个递归查询，对所有表执行此操作，但是当我添加时：
START WITH Table_Name=:tablename
It doesn't return the entire tree.
它不会返回整个树。
回答by Justin Cave
select parent, child, level from (
select parent_table.table_name parent, child_table.table_name child
from user_tables parent_table,
user_constraints parent_constraint,
user_constraints child_constraint,
user_tables child_table
where parent_table.table_name = parent_constraint.table_name
and parent_constraint.constraint_type IN( 'P', 'U' )
and child_constraint.r_constraint_name = parent_constraint.constraint_name
and child_constraint.constraint_type = 'R'
and child_table.table_name = child_constraint.table_name
and child_table.table_name != parent_table.table_name
)
start with parent = 'DEPT'
connect by prior child = parent
should work (replace the table name, of course) assuming that everything is in the same schema. Use the DBA_ versions of the data dictionary tables and conditions for the OWNER and R_OWNER columns if you need to handle cross-schema dependencies. On further reflection, this does not account for self-referential constraints (i.e. a constraint on the EMP table that the MGR column references the EMPNO column) either, so you'd have to modify the code to handle that case if you need to deal with self-referential constraints.
假设一切都在相同的模式中，应该可以工作（当然，替换表名）。如果您需要处理跨模式依赖关系，请使用 DBA_ 版本的数据字典表和 OWNER 和 R_OWNER 列的条件。进一步思考，这也没有考虑自引用约束（即 MGR 列引用 EMPNO 列的 EMP 表上的约束），因此如果您需要处理这种情况，您必须修改代码来处理这种情况具有自我参照约束。
For testing purposes, I added a few new tables to the SCOTT schema that also reference the DEPT table (including a grandchild dependency)
出于测试目的，我在 SCOTT 模式中添加了一些新表，这些表也引用了 DEPT 表（包括孙子依赖项）
SQL> create table dept_child2 (
2 deptno number references dept( deptno )
3 );
Table created.
SQL> create table dept_child3 (
2 dept_child3_no number primary key,
3 deptno number references dept( deptno )
4 );
Table created.
SQL> create table dept_grandchild (
2 dept_child3_no number references dept_child3( dept_child3_no )
3 );
Table created.
and verified that the query returned the expected output
并验证查询返回了预期的输出
SQL> ed
Wrote file afiedt.buf
1 select parent, child, level from (
2 select parent_table.table_name parent, child_table.table_name child
3 from user_tables parent_table,
4 user_constraints parent_constraint,
5 user_constraints child_constraint,
6 user_tables child_table
7 where parent_table.table_name = parent_constraint.table_name
8 and parent_constraint.constraint_type IN( 'P', 'U' )
9 and child_constraint.r_constraint_name = parent_constraint.constraint_name
10 and child_constraint.constraint_type = 'R'
11 and child_table.table_name = child_constraint.table_name
12 and child_table.table_name != parent_table.table_name
13 )
14 start with parent = 'DEPT'
15* connect by prior child = parent
SQL> /
PARENT CHILD LEVEL
------------------------------ ------------------------------ ----------
DEPT DEPT_CHILD3 1
DEPT_CHILD3 DEPT_GRANDCHILD 2
DEPT DEPT_CHILD2 1
DEPT EMP 1
回答by Milan Babu?kov
Simplest way to do this is to copy all the FK info into a simple, 2-column (parent,child) table, and then use the following algorithm:
最简单的方法是将所有 FK 信息复制到一个简单的 2 列（父、子）表中，然后使用以下算法：
while (rows left in that table)
list = rows where table name exists in child but not in parent
print list
remove list from rows
that's all. Basically, you first print and remove all the nodes that don't depend on anything. After that being done, some other nodes will get free and you can repeat process.
就这样。基本上，您首先打印并删除所有不依赖任何东西的节点。完成后，其他一些节点将获得自由，您可以重复该过程。
P.S. Make sure you don't insert self-referencing tables in the initial list (child=parent)
PS 确保不要在初始列表中插入自引用表（child=parent）
