Oracle 如何检索给定表的双向父子依赖关系

你好Oracle大师，

我正在尝试使用给定表的主键/外键关系检索列表依赖关系，但使用另一个表作为stop子句。我一直在尝试这里提到的递归子查询分解方法https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5822332300346315797,但不幸的是没有太大的成功 :(

假设我拥有链接的LiveSQL方案的表和测试值，我的目标是遍历所有依赖项，直到我到达表A。
前提条件: 所有表都以一种或另一种方式与表A相关。有些处于直接的父子关系中，有些处于映射表中。

然后，我只想看看将我引向那里的路径 (其原因将在稍后解释)。

现在，当执行语句 #33，而不是实现的结果，我会寻找这样的东西 (前三个cols省略):

LVL TABLE_NAME1 COLUMN_NAME1 TABLE_NAME2 COLUMN_NAME2
1 C C_B_ID B B_ID
2 B B_A_ID A A_ID
复制

不幸的是，我无法走那么远。

同样适用于语句 #34，映射不会使它变得更容易，我希望结果是这样的:

LVL TABLE_NAME1 COLUMN_NAME1 TABLE_NAME2 COLUMN_NAME2
1 D D_ID E E_D_ID
2 E E_A_ID A A_ID
复制

稍后我计划创建动态更新语句，使用结果作为我的where子句，因为更新将在A_ID值上。

是像我正在寻找实际上可能使用递归子查询分解方法还是我使用了错误的方法？

希望你能帮助我 =)

亲切的问候，
勒内

好的。所以这里的部分问题是当你有一个表链C -> B -> a。

当你从C到B跟随FK时，你会到达B_PK。然后进入A，您需要重新加入B的约束以找到其FKs。

所以实际上你的选择在递归需要得到源约束 (PK或FK) 和目标 (FK或PK)。我还包含了递归查询中的列。

这给出了一个更复杂的基本查询，如下所示:

select uc.constraint_name src,  
         nvl( ruc.constraint_name , uc.r_constraint_name)  dst,   
         uc.table_name src_tab,  
         ucc2.table_name dst_tab,  
         0 lvl , 
         uc.constraint_type tp,
         ucc1.column_name, 
         ucc2.column_name,
         least(uc.table_name , ucc2.table_name) cyc_tab, 
         ucc2.table_name path
  from   user_constraints uc  
  join   user_cons_columns ucc1 
  on     ucc1.constraint_name = uc.constraint_name
  left join user_constraints ruc
  on     ruc.r_constraint_name = uc.constraint_name
  join   user_cons_columns ucc2 
  on     ucc2.constraint_name = case 
    when uc.constraint_type = 'R' then uc.r_constraint_name
    when uc.constraint_type = 'P' then ruc.constraint_name 
  end
  where  uc.table_name = :tab 
  and    uc.constraint_type in ('R', 'P')
复制

和递归分支中的一个类似的。但是，不是在约束名称上加入，而是在目标表上加入user_constraints。

要在到达A时停止处理，请检查目标表不是a。这给出了一个查询，如:

with constr ( src, dst, src_tab, dst_tab, lev, tp, col1, col2, cyc_tab, path) as (
  select uc.constraint_name src,  
         nvl( ruc.constraint_name , uc.r_constraint_name)  dst,   
         uc.table_name src_tab,  
         ucc2.table_name dst_tab,  
         0 lvl , 
         uc.constraint_type tp,
         ucc1.column_name, 
         ucc2.column_name,
         least(uc.table_name , ucc2.table_name) cyc_tab, 
         ucc2.table_name path
  from   user_constraints uc  
  join   user_cons_columns ucc1 
  on     ucc1.constraint_name = uc.constraint_name
  left join user_constraints ruc
  on     ruc.r_constraint_name = uc.constraint_name
  join   user_cons_columns ucc2 
  on     ucc2.constraint_name = case 
    when uc.constraint_type = 'R' then uc.r_constraint_name
    when uc.constraint_type = 'P' then ruc.constraint_name 
  end
  where  uc.table_name = :tab 
  and    uc.constraint_type in ('R', 'P')
  union all
  select uc.constraint_name src,  
         nvl( ruc.constraint_name , uc.r_constraint_name) dst,   
         uc.table_name src_tab,  
         ucc2.table_name dst_tab,  
         lev + 1, 
         uc.constraint_type tp,
         ucc1.column_name, 
         ucc2.column_name,
         least(uc.table_name , ucc2.table_name) cyc_tab, 
         path || ',' || ucc2.table_name path
  from   constr c 
  join user_constraints uc
  on     uc.table_name = c.dst_tab
  and    uc.constraint_type in ('R', 'P')
  and    uc.table_name <> 'A'
  join   user_cons_columns ucc1 
  on     ucc1.constraint_name = uc.constraint_name
  left join user_constraints ruc
  on     ruc.r_constraint_name = uc.constraint_name
  join   user_cons_columns ucc2 
  on     ucc2.constraint_name = case 
    when uc.constraint_type = 'R' then uc.r_constraint_name
    when uc.constraint_type = 'P' then ruc.constraint_name 
  end
) search depth first by src_tab set tab_order
  cycle cyc_tab set cycle to 1 default 0
  select c.*, 
         case when lev < lead(lev) over (order by tab_order) then null
           else path
         end is_leaf
  from   constr c
  where  cycle = 0;
复制

然后，这将使关系上下变化。所以，如果你开始在一个没有导致a (如B) 的孩子的表，你会得到额外的行:

SRC     DST     SRC_TAB  DST_TAB  LEV  TP  COL1    COL2    CYC_TAB  PATH  TAB_ORDER  CYCLE  IS_LEAF  
B_A_FK  A_PK    B        A        0    R   B_A_ID  A_ID    A        A     1          0      A        
B_PK    C_B_FK  B        C        0    P   B_ID    C_B_ID  B        C     2          0      C 
复制

要消除这些，你需要检查你的路径是否有一个作为叶子。

我通过返回目标表 (如果当前行是叶子) 来完成此操作。然后使用last_value “填充” 这片叶子，向后走树。

然后，您只返回其中的结果是:

with constr ( src, dst, src_tab, dst_tab, lev, tp, col1, col2, cyc_tab, path) as (
  select uc.constraint_name src,  
         nvl( ruc.constraint_name , uc.r_constraint_name)  dst,   
         uc.table_name src_tab,  
         ucc2.table_name dst_tab,  
         0 lvl , 
         uc.constraint_type tp,
         ucc1.column_name, 
         ucc2.column_name,
         least(uc.table_name , ucc2.table_name) cyc_tab, 
         ucc2.table_name path
  from   user_constraints uc  
  join   user_cons_columns ucc1 
  on     ucc1.constraint_name = uc.constraint_name
  left join user_constraints ruc
  on     ruc.r_constraint_name = uc.constraint_name
  join   user_cons_columns ucc2 
  on     ucc2.constraint_name = case 
    when uc.constraint_type = 'R' then uc.r_constraint_name
    when uc.constraint_type = 'P' then ruc.constraint_name 
  end
  where  uc.table_name = :tab 
  and    uc.constraint_type in ('R', 'P')
  union all
  select uc.constraint_name src,  
         nvl( ruc.constraint_name , uc.r_constraint_name) dst,   
         uc.table_name src_tab,  
         ucc2.table_name dst_tab,  
         lev + 1, 
         uc.constraint_type tp,
         ucc1.column_name, 
         ucc2.column_name,
         least(uc.table_name , ucc2.table_name) cyc_tab, 
         path || ',' || ucc2.table_name path
  from   constr c 
  join user_constraints uc
  on     uc.table_name = c.dst_tab
  and    uc.constraint_type in ('R', 'P')
  and    uc.table_name <> 'A'
  join   user_cons_columns ucc1 
  on     ucc1.constraint_name = uc.constraint_name
  left join user_constraints ruc
  on     ruc.r_constraint_name = uc.constraint_name
  join   user_cons_columns ucc2 
  on     ucc2.constraint_name = case 
    when uc.constraint_type = 'R' then uc.r_constraint_name
    when uc.constraint_type = 'P' then ruc.constraint_name 
  end
) search depth first by src_tab set tab_order
  cycle cyc_tab set cycle to 1 default 0
  select c.*, 
         case when lev < lead(lev) over (order by tab_order) then null
           else path
         end is_leaf
  from   constr c
  where  cycle = 0;

with constr ( src, dst, src_tab, dst_tab, lev, tp, col1, col2, cyc_tab, path) as (
  select uc.constraint_name src,  
         nvl( ruc.constraint_name , uc.r_constraint_name)  dst,   
         uc.table_name src_tab,  
         ucc2.table_name dst_tab,  
         0 lvl , 
         uc.constraint_type tp,
         ucc1.column_name, 
         ucc2.column_name,
         least(uc.table_name , ucc2.table_name) cyc_tab, 
         ucc2.table_name path
  from   user_constraints uc  
  join   user_cons_columns ucc1 
  on     ucc1.constraint_name = uc.constraint_name
  left join user_constraints ruc
  on     ruc.r_constraint_name = uc.constraint_name
  join   user_cons_columns ucc2 
  on     ucc2.constraint_name = case 
    when uc.constraint_type = 'R' then uc.r_constraint_name
    when uc.constraint_type = 'P' then ruc.constraint_name 
  end
  where  uc.table_name = :tab 
  and    uc.constraint_type in ('R', 'P')
  union all
  select uc.constraint_name src,  
         nvl( ruc.constraint_name , uc.r_constraint_name) dst,   
         uc.table_name src_tab,  
         ucc2.table_name dst_tab,  
         lev + 1, 
         uc.constraint_type tp,
         ucc1.column_name, 
         ucc2.column_name,
         least(uc.table_name , ucc2.table_name) cyc_tab, 
         path || ',' || ucc2.table_name path
  from   constr c 
  join user_constraints uc
  on     uc.table_name = c.dst_tab
  and    uc.constraint_type in ('R', 'P')
  and    uc.table_name <> 'A'
  join   user_cons_columns ucc1 
  on     ucc1.constraint_name = uc.constraint_name
  left join user_constraints ruc
  on     ruc.r_constraint_name = uc.constraint_name
  join   user_cons_columns ucc2 
  on     ucc2.constraint_name = case 
    when uc.constraint_type = 'R' then uc.r_constraint_name
    when uc.constraint_type = 'P' then ruc.constraint_name 
  end
) search depth first by src_tab set tab_order
  cycle cyc_tab set cycle to 1 default 0, 
  leaves as (
  select c.*, 
         case when lev < lead(lev) over (order by tab_order) then null
           else dst_tab
         end is_leaf
  from   constr c
  where  cycle = 0
), rng as (
  select l.*,
         last_value(is_leaf) ignore nulls over (order by tab_order desc) lv
  from   leaves l
)
  select * from rng
  where  lv = 'A'
  order  by lev;
复制

它给出了以下结果:

-- for D
SRC     DST     SRC_TAB  DST_TAB  LEV  TP  COL1    COL2    CYC_TAB  PATH  TAB_ORDER  CYCLE  IS_LEAF  LV  
D_PK    E_D_FK  D        E        0    P   D_ID    E_D_ID  D        E     1          0               A   
E_A_FK  A_PK    E        A        1    R   E_A_ID  A_ID    A        E,A   2          0      A        A 

-- for C
SRC     DST   SRC_TAB  DST_TAB  LEV  TP  COL1    COL2  CYC_TAB  PATH  TAB_ORDER  CYCLE  IS_LEAF  LV  
C_B_FK  B_PK  C        B        0    R   C_B_ID  B_ID  B        B     1          0               A   
B_A_FK  A_PK  B        A        1    R   B_A_ID  A_ID  A        B,A   2          0      A        A   

-- for B
SRC     DST   SRC_TAB  DST_TAB  LEV  TP  COL1    COL2  CYC_TAB  PATH  TAB_ORDER  CYCLE  IS_LEAF  LV  
B_A_FK  A_PK  B        A        0    R   B_A_ID  A_ID  A        A     1          0      A        A 
复制

有关如何使用递归查找叶子的解释，请阅读:http://www.orafaq.com/node/2996

请注意，这包括您可能需要调整的几个假设:

-您没有跨模式约束。如果这样做，则需要使用所有 * 视图并将所有者包含在表中
-所有fk都指向pk，没有指向唯一约束
-您没有复合pk (或者至少没有fk引用它们!)