ORACLE的递归查询

前一段时间，因为工作老大要求显示表之间的外键关系，研究了一下递归的方法。主要是新系统中，外键约束用的太多，经常会有数据不同步的现象发生，等到手工做同步的时候，就会报出外键不匹配的错误来。

 

写了一个脚本，会把当前schema中，所有的父表和字表按照顺序打印出来。因为不同的父子之间会有相同的表，同一个父表，被不同字表引用的深度又不同。所有，这个输出，只是做到父表在前，子表在后，前后紧跟的两张表，可能是同级别的，不保证前面的一定是后面的直接父表。

 

这个逻辑，也没有把握，希望大家给出一点意见。不过在本地，我已经手工验证了，没有发现错误。

 

脚本如下：

 

set serveroutput on;

 

-- Created on 2009-1-22 by EDWIN
declare
  -- Local variables here
  i integer;
  type table_level_type is table of pls_integer index by varchar2(30);
  v_table_list table_level_type;
  type table_list_type is table of varchar2(30) index by pls_integer;
  v_out_table_list table_list_type;
  v_table_name varchar2(30) := '';
  v_leve pls_integer := 0;
  cursor c_get_refered_tables is
    select distinct t.table_name
    from sys.user_constraints t
    where t.constraint_type = 'R'
    order by t.table_name;
  cursor c_get_parent_tables(p_table_name varchar2) is
    select t.parent_table_name, level
    from (
      select
        distinct
        a.table_name table_name,
        b.table_name parent_table_name
      from
        user_constraints a,
        user_constraints b
      where
        a.owner = b.owner
      and
        a.r_constraint_name = b.constraint_name
      and
        a.constraint_type = 'R'
      order by
        a.table_name) t
    connect by NOCYCLE t.table_name = PRIOR t.parent_table_name
    start with t.table_name = p_table_name
    order by level desc;
 
begin
  -- Test statements here
  i := 0;
  for c_tables in c_get_refered_tables loop
    v_table_name := c_tables.table_name;
   
    for c_parent_tables in c_get_parent_tables(v_table_name) loop
      if (false = v_table_list.exists(c_parent_tables.parent_table_name)) then
        i := i + 1;
        v_table_list(c_parent_tables.parent_table_name) := c_parent_tables.level;
        v_out_table_list(i) := c_parent_tables.parent_table_name;
      end if;
    end loop; -- end loop of parent tables.
   
    -- check itself.
    if (false = v_table_list.exists(v_table_name)) then
      i := i + 1;
      v_table_list(v_table_name) := 1;
      v_out_table_list(i) := v_table_name;
    end if;
   
  end loop; -- end loop of refered table list.
 
  -- print the table list.
  for i in v_out_table_list.first .. v_out_table_list.last loop
    dbms_output.put_line(v_out_table_list(i));
  end loop;
end;

/

 

 

加上一个查看指定表的所有子表的查询：

 

select level, lpad('  ',(level-1)*4) || t.table_name table_name
from (
  select
    distinct
    a.table_name table_name,
    b.table_name parent_table_name
  from
    user_constraints a,
    user_constraints b
  where
    a.owner = b.owner
  and
    a.r_constraint_name = b.constraint_name
  and
    a.constraint_type = 'R'
  order by
    a.table_name) t
connect by NOCYCLE PRIOR t.table_name = t.parent_table_name
start with t.parent_table_name = 'XXXX'
order by level asc;