oracle外键反查套件代码,PLSQL代码库,德仔网

代码语言
代码分类
【PLSQL】 oracle外键反查套件
作者:jarchan / 发布于2014/11/7/ 819

	-- 以下是安装脚本
	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;
	/
试试其它关键字
　外键反查　
同语言下
可能有用的
jarchan贡献的其它代码(1)
.oracle外键反查套件