用于查询我引用谁，谁引用我的问题 examples: 查询我引用谁 select*fromtable(fk_util.get_refering_stats(table_a)); 查询谁引用我 select*fromtable(fk_util.get_refered_stats(table_a)); 查询id为的某条记录的被引用计数 select*fromtable(fk_util.get_re
用于查询我引用谁，谁引用我的问题
examples:
查询我引用谁
select * from table(fk_util.get_refering_stats('table_a'));
查询谁引用我
select * from table(fk_util.get_refered_stats('table_a'));
查询id为的某条记录的被引用计数
select * from table(fk_util.get_refered_count('table_a', id));
查询某种条件下的被引用计数
-- 查询code为1的某条记录的被引用计数
select * from table(fk_util.get_refered_count_cond('xb_std_types', 'code', '1'));
查询某表在某种条件下的被引用情况，并且附带出更详细的信息
select table_a.id, table_a.column1, table_a.column2, ..., stats.child_table, stats.refer_count
from table_a
join table(fk_util.get_refered_count_cond('table_a', 'column', 'value')) stats
on stats.parent_id=table_a.id;
  -- 以下是安装脚本create or replace type fk_stats_row as object ( child_table varchar2(32), child_table_fk_col varchar2(32), parent_table varchar2(32), parent_table_pk_col varchar2(32));/ create or replace type fk_stats as table of fk_stats_row;/ create or replace type fk_refered_count_row as object ( child_table varchar2(32), parent_id number(19), refer_count number(19));/ create or replace type fk_refered_count as table of fk_refered_count_row;/ create or replace type id_array as table of number(19);/ create or replace package fk_utilis -- 获得我所引用的表 function get_refering_stats(v_table_name varchar2) return fk_stats; -- 获得所有子表及外键列 function get_refered_stats(v_table_name varchar2) return fk_stats; -- 获得所有子表对某个id的引用条数 function get_refered_count(v_parent_table varchar2, v_parent_id number) return fk_refered_count; -- 获得所有子表对符合条件的某些记录的引用条数 function get_refered_count_cond(v_parent_table varchar2, v_cond_col varchar2, v_cond varchar2) return fk_refered_count;end fk_util;/ create or replace package body fk_utilis -- 获得我所引用的表 function get_refering_stats(v_table_name varchar2) return fk_stats is v_ret fk_stats := fk_stats(); begin select cast( multiset( select a.table_name 从表, a.column_name 外键列, b.table_name 主表, b.column_name 被引用列 from ( select uc.table_name, ucc.column_name, uc.r_constraint_name from user_constraints uc join user_cons_columns ucc on uc.constraint_name = ucc.constraint_name where uc.constraint_type='r' ) a, ( select uc.table_name, ucc.column_name, uc.constraint_name from user_constraints uc join user_cons_columns ucc on uc.constraint_name = ucc.constraint_name ) b where a.r_constraint_name = b.constraint_name and a.table_name = upper(v_table_name) ) as fk_stats ) into v_ret from dual; return v_ret; end get_refering_stats; -- 获得所有子表及外键列 function get_refered_stats(v_table_name varchar2) return fk_stats is v_ret fk_stats := fk_stats(); begin select cast( multiset( select a.table_name 从表, a.column_name 外键列, b.table_name 主表, b.column_name 被引用列 from ( select uc.table_name, ucc.column_name, uc.r_constraint_name from user_constraints uc join user_cons_columns ucc on uc.constraint_name = ucc.constraint_name where uc.constraint_type='r' ) a, ( select uc.table_name, ucc.column_name, uc.constraint_name from user_constraints uc join user_cons_columns ucc on uc.constraint_name = ucc.constraint_name ) b where a.r_constraint_name = b.constraint_name and b.table_name = upper(v_table_name) ) as fk_stats ) into v_ret from dual; return v_ret; end get_refered_stats; -- 获得所有子表对某个id的引用条数 function get_refered_count(v_parent_table varchar2, v_parent_id number) return fk_refered_count is v_ret fk_refered_count := fk_refered_count(); v_count number := 0; v_sql varchar2(2000) := ''; begin for v_row in (select * from table(get_refered_stats(v_parent_table))) loop v_sql := 'select count(*) from '|| v_row.child_table ||' where ' || v_row.child_table_fk_col || ' = ' || v_parent_id; execute immediate v_sql into v_count; v_ret.extend(1); v_ret(v_ret.count) := fk_refered_count_row(v_row.child_table, v_parent_id, v_count); end loop; return v_ret; end get_refered_count; -- 获得所有子表对符合条件的某些记录的引用条数 function get_refered_count_cond(v_parent_table varchar2, v_cond_col varchar2, v_cond varchar2) return fk_refered_count is v_ret fk_refered_count := fk_refered_count(); v_id_array id_array := id_array(); v_sql varchar2(2000) := ''; begin if upper(v_cond_col) like '%id' then v_sql := 'select cast(multiset(select id from ' || v_parent_table || ' where '|| v_cond_col ||'=' || v_cond || ') as id_array) from dual'; else v_sql := 'select cast(multiset(select id from ' || v_parent_table || ' where '|| v_cond_col ||'=''' || v_cond || ''') as id_array) from dual'; end if; execute immediate v_sql into v_id_array; for id_row in (select * from table(v_id_array)) loop for count_row in (select * from table(get_refered_count(v_parent_table, id_row.column_value))) loop v_ret.extend(1); v_ret(v_ret.count) := fk_refered_count_row(count_row.child_table, count_row.parent_id, count_row.refer_count); end loop; end loop; return v_ret; end get_refered_count_cond;end fk_util;/