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