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