使用递归查询构建表依赖图

小编典典

使用递归查询构建表依赖图

sql

我试图基于它们之间的外键建立表的依赖关系图。该图需要以一个任意的表名作为根开始。我可以给定一个表名，然后使用all_constraints视图查找引用该表的表，然后查找引用它们的表，依此类推，但是这样效率很低。我编写了一个对所有表都执行此操作的递归查询，但是当我添加时：

START WITH Table_Name=:tablename

它不会返回整个树。

阅读 219

2021-03-17

共1个答案

小编典典

    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

假定所有内容都在同一模式下，则应该可以正常工作（当然要替换表名）。如果需要处理跨模式依赖关系，请使用数据字典表的DBA_版本和OWNER和R_OWNER列的条件。进一步思考，这也不考虑自我引用约束（即，对EMP表的约束，即MGR列引用EMPNO列），因此，如果需要处理，则必须修改代码以处理这种情况。具有自我指称的约束。

为了进行测试，我向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

2021-03-17