用于查询我引用谁，谁引用我的问题 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;
<style> .CodeEntity .code_pieces ul.piece_anchor{width:25px;position:absolute;top:25px;left:-30px;z-index:1000;} .CodeEntity .code_pieces ul.piece_anchor li{width:25px;background: #efe;margin-bottom:2px;} .CodeEntity .code_pieces ul.piece_anchor li{border-left:3px #40AA63 solid;border-right:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li:hover{border-right:3px #40AA63 solid;border-left:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li a{color: #333;padding: 3px 10px;} .CodeEntity .code_pieces .jump_to_code{visibility:hidden;position:relative;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code{visibility:visible;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code a{text-decoration:none;} .CodeEntity .code_pieces h2 i{float:right;font-style:normal;font-weight:normal;} .CodeEntity .code_pieces h2 i a{font-size:9pt;background: #FFFFFF;color:#00A;padding: 2px 5px;text-decoration:none;} </style> <!—ecms -ecms
- –> <!—ecms -ecms
- $velocityCount
–> <!—ecms -ecms
–>
-- 以下是安装脚本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_co<b style="color:transparent">本文来源gao@!dai!ma.com搞$$代^@码网*</b>unt; -- 获得所有子表对符合条件的某些记录的引用条数 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;/
转载请注明原文链接：oracle外键反查套件
