代码之家 › 专栏 › 技术社区 › Mark Roddy

使用递归查询生成表依赖关系图

recursive-query recursion oracle sql

Mark Roddy · 技术社区 · 16 年前

我正在尝试基于表之间的外键构建表的依赖关系图。这个图需要以一个任意的表名作为它的根。我可以,给定一个表名,使用“所有约束”视图查找引用它的表,然后查找引用它们的表,依此类推,但这样做效率极低。我编写了一个递归查询,对所有表都执行此操作,但是当我添加:

START WITH Table_Name=:tablename

它不会返回整棵树。

2 回复 | 直到 16 年前

Justin Cave 16 年前

    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

应该可以(当然,替换表名)假设所有内容都在相同的模式中。如果需要处理跨架构依赖关系,请为owner和r_owner列使用数据字典表的DBA版本和条件。进一步考虑,这也不考虑自引用约束(即,管理器列引用EMPNO列的EMP表上的约束),因此如果需要处理自引用约束,则必须修改代码来处理这种情况。

为了测试的目的,我在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.

并验证查询是否返回预期的输出

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

Milan BabuÅ¡kov 16 年前

最简单的方法是将所有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

这就是全部。基本上,首先打印并删除所有不依赖于任何内容的节点。完成之后,其他一些节点将得到空闲,您可以重复这个过程。

另外,确保不要在初始列表中插入自引用表(child=parent)