表现形式如下:
TAB1
TAB2
TAB3
TAB4
TAB5
TAB6
...
TABn
说明:通过一个给定的表名(如TAB1),将这个表所有的依赖关系列出来,最近关系的父表与子表通过2个空格缩进,如上图,TAB2引用TAB1,TAB2被定义为TAB1的字表。我考虑用递归来做,被空格缩进搞晕了。各位有好的方案吗?下面附上我的代码:首先将表的依赖关系建成一个视图,命令如下:
execute immediate
'create or replace view v_checktablerelation as ' ||
'select * ' ||
' from (select b.table_name pritab, ' ||
' substr(d.column_name, 1, 127) pricol, ' ||
' a.table_name fortab, ' ||
' substr(c.column_name, 1, 127) forcol ' ||
' from user_constraints a, ' ||
' user_constraints b, ' ||
' user_cons_columns c, ' ||
' user_cons_columns d ' ||
' where a.r_constraint_name = b.constraint_name ' ||
' and a.constraint_type = ''R'' ' ||
' and b.constraint_type = ''P'' ' ||
' and a.r_owner = b.owner ' ||
' and a.constraint_name = c.constraint_name ' ||
' and b.constraint_name = d.constraint_name ' ||
' and a.owner = c.owner ' ||
' and a.table_name = c.table_name ' ||
' and b.owner = d.owner ' ||
' and b.table_name = d.table_name) ' ||
' where pritab <> fortab ' ||
' order by pritab';
TAB1
TAB2
TAB3
TAB4
TAB5
TAB6
...
TABn
说明:通过一个给定的表名(如TAB1),将这个表所有的依赖关系列出来,最近关系的父表与子表通过2个空格缩进,如上图,TAB2引用TAB1,TAB2被定义为TAB1的字表。我考虑用递归来做,被空格缩进搞晕了。各位有好的方案吗?下面附上我的代码:首先将表的依赖关系建成一个视图,命令如下:
execute immediate
'create or replace view v_checktablerelation as ' ||
'select * ' ||
' from (select b.table_name pritab, ' ||
' substr(d.column_name, 1, 127) pricol, ' ||
' a.table_name fortab, ' ||
' substr(c.column_name, 1, 127) forcol ' ||
' from user_constraints a, ' ||
' user_constraints b, ' ||
' user_cons_columns c, ' ||
' user_cons_columns d ' ||
' where a.r_constraint_name = b.constraint_name ' ||
' and a.constraint_type = ''R'' ' ||
' and b.constraint_type = ''P'' ' ||
' and a.r_owner = b.owner ' ||
' and a.constraint_name = c.constraint_name ' ||
' and b.constraint_name = d.constraint_name ' ||
' and a.owner = c.owner ' ||
' and a.table_name = c.table_name ' ||
' and b.owner = d.owner ' ||
' and b.table_name = d.table_name) ' ||
' where pritab <> fortab ' ||
' order by pritab';
create or replace procedure sp_tab_checkrelation
(
itablename in varchar2
)
as
file_handle utl_file.file_type;
v_flag integer :=0;
begin
sp_view_createtabref;
file_handle := utl_file.fopen('/tmp','test.txt','w');
for i in ( select * from v_checktablerelation t where t.pritab = itablename ) loop
if v_flag =0 then
utl_file.put_line(file_handle,i.pritab||'.'||i.pricol);
end if;
--utl_file.put_line(file_handle,' '||i.fortab||'.'||i.forcol);
/*
for j in ( select * from v_checktablerelation t where t.pritab = i.fortab ) loop
if v_flag =0 then
utl_file.put_line(file_handle,' '||j.pritab||'.'||j.pricol);
end if;
utl_file.put_line(file_handle,' '||j.fortab||'.'||j.forcol);
for l in ( select * from v_checktablerelation t where t.pritab = j.fortab ) loop
if v_flag =0 then
utl_file.put_line(file_handle,' '||l.pritab||'.'||l.pricol);
end if;
utl_file.put_line(file_handle,' '||l.fortab||'.'||l.forcol);
end loop;
end loop;
*/
--Call recursive function/procedure to deal N (N>=2) level depth tables references
sp_tab_checkrelation_sub1(i.fortab,file_handle);
v_flag := v_flag + 1 ;
end loop;
utl_file.fclose(file_handle);
null;
end sp_tab_checkrelation;
--------------------------
create or replace procedure sp_tab_checkrelation_sub1
(
itablename in varchar2,
ifile_handle in utl_file.file_type
)
as
v_flag integer := 0 ;
v_spnum integer := 1 ;
v_str varchar2(2000) :=null ;
begin
for j in ( select * from v_checktablerelation t where t.pritab = itablename ) loop
/*
for N in 1..v_spnum loop
v_str := v_str||' ';
end loop;
*/
v_str :=' ';
if v_flag =0 then
utl_file.put_line(ifile_handle,v_str||j.pritab||'.'||j.pricol);
end if;
utl_file.put_line(ifile_handle,' '||v_str||j.fortab||'.'||j.forcol);
v_spnum := v_spnum + 1;
sp_tab_checkrelation_sub1(j.fortab,ifile_handle);
v_flag := v_flag + 1 ;
end loop;
null;
end sp_tab_checkrelation_sub1;
sp_tab_checkrelation_sub1(i.fortab,file_handle);
前加判断,如果在取数据的时,外表存在与往下的记录主表相等时才走递归.
select count(1) into flag from v_checktablerelation t where t.pritab = i.fortab;
if flag>0 then
sp_tab_checkrelation_sub1(i.fortab,file_handle);
end if;哎,没数据库,调不起来;各位有什么更好的方案吗?周末玩累了,过来看看,呵呵.
用sql做可以么我现在建表是
create table r_t1 (id int, name varchar(10));
create table r_t2 (id int, t1_id int, t3_id int, name varchar(10));
create table r_t3 (id int, name varchar(10));
create table r_t4 (id int, t2_id int, name varchar(10));
create table r_t5 (id int, t4_id int, name varchar(10));
create table r_t6 (id int, t5_id int, name varchar(10));alter table r_t1 add constraint RF_PK_1 primary key(id);
alter table r_t2 add constraint RF_PK_2 primary key(id);
alter table r_t3 add constraint RF_PK_3 primary key(id);
alter table r_t4 add constraint RF_PK_4 primary key(id);
alter table r_t5 add constraint RF_PK_5 primary key(id);alter table r_t2 add constraint RF_FK_2_1 foreign key(t1_id) references r_t1(id);
alter table r_t2 add constraint RF_FK_2_2 foreign key(t3_id) references r_t3(id);
alter table r_t4 add constraint RF_FK_4 foreign key(t2_id) references r_t2(id);
alter table r_t5 add constraint RF_FK_5 foreign key(t4_id) references r_t4(id);
alter table r_t6 add constraint RF_FK_6 foreign key(t5_id) references r_t5(id);这里的机构是
t6
t5
t4
t2
t3
t1SQL>with temp as
(
select a.constraint_name name, a.table_name origial_t, b.table_name refer_t
from user_constraints a, user_constraints b
where a.constraint_type = 'R'
and a.r_constraint_name = b.constraint_name
)
select lpad(refer_t, (level) * 5, '*') t_n, origial_t, refer_t
from (select '' name, '' origial_t, 'R_T6' refer_t
from dual
union
select * from temp)
connect by prior refer_t = origial_t
start with origial_t is null;
T_N ORIGIAL_T REFER_T
-------------------------------------------------------------------------------- ------------------------------ ------------------------------
*R_T6 R_T6
******R_T5 R_T6 R_T5
***********R_T4 R_T5 R_T4
****************R_T2 R_T4 R_T2
*********************R_T1 R_T2 R_T1
*********************R_T3 R_T2 R_T3
(
select a.constraint_name name, a.table_name origial_t, b.table_name refer_t
from user_constraints a, user_constraints b
where a.constraint_type = 'R'
and a.r_constraint_name = b.constraint_name
)
select lpad(' ', (level-1) * 2, ' ')||refer_t t_n, origial_t, refer_t
from (select '' name, '' origial_t, 'R_T6' refer_t
from dual
union
select * from temp)
connect by prior refer_t = origial_t
start with origial_t is null;T_N ORIGIAL_T REFER_T
-------------------------------------------------------------------------------- ------------------------------ ------------------------------
R_T6 R_T6
R_T5 R_T6 R_T5
R_T4 R_T5 R_T4
R_T2 R_T4 R_T2
R_T1 R_T2 R_T1
R_T3
兄弟你的响应真快,谢谢;我之所以要用存储过程,考虑到可能在表的外键树里加入到一些其他的数据库统计信息,存储过程比较方便.
select level lev,lpad(' ', (level-1) * 2, ' ')||fortab||'.'||forcol t_n, fortab,forcol,pritab,pricol
from ( select '' pritab, '' pricol, 'r_t1' fortab ,
( select min(t.PRICOL) PRICOL from v_checktablerelation t
where t.PRITAB='r_t1' ) forcol
from dual
union
select * from v_checktablerelation t )
start with pritab is null
connect by prior fortab= pritab ;