oracle 查询fk,oracle外键反查套件

经常和oracle打交道的人都应该遇到会遇到过这么一类需求：

哪些表引用了这个表

这条数据被引用了几次

这张表引用了哪些表，引用的字段是什么

本人也经常遇到这些问题，特别是当删除一条数据总是被告知被引用的时候。于是写了以下外键反查套件：

注： 本套件只支持表中主键列名为ID，ID为NUMBER类型，如果不符合你的需求可自行修改。

用法

查询我引用谁

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;

安装

执行下列sql语句，安装本套件

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_UTIL

IS

-- 获得我所引用的表

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_UTIL

IS

-- 获得我所引用的表

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;

/