Oracle foreign key lookup suite

Used to query who I reference and who I reference Examples: queries who I reference SELECT * FROMTABLE (FK_UTIL.get_refering_stats (TABLE_A )); query who references me SELECT * FROMTABLE (FK_UTIL.get_refered_stats (TABLE_A); query the reference count of a record with id select * FROMTABLE (FK_UTIL.get_re

Used to query who I reference and who I reference Examples: queries who I reference SELECT * FROMTABLE (FK_UTIL.get_refering_stats (TABLE_A )); query who references me SELECT * FROMTABLE (FK_UTIL.get_refered_stats (TABLE_A); query the reference count of a record with id select * FROMTABLE (FK_UTIL.get_re

Used to query who I reference and who I reference

Examples:

Query who I Reference
SELECT * from table (FK_UTIL.get_refering_stats ('table _ '));

Query who cited me
SELECT * from table (FK_UTIL.get_refered_stats ('table _ '));

Reference count of a record with a query ID
SELECT * from table (FK_UTIL.get_refered_count ('table _ a', ID ));

Queries the reference count under a certain condition.
-- Query the reference count of a record whose code is 1
SELECT * from table (FK_UTIL.get_refered_count_cond ('xb _ std_types ', 'code', '1 '));

Queries the referenced conditions of a table under certain conditions and provides more detailed information.
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;
<无>

-- The following installation script is 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 -- get the FUNCTION get_refering_stats (v_table_name varchar2) RETURN fk_stats; -- get all the sub-tables and foreign key columns FUNCTION compute (v_table_name varchar2) RETURN fk_stats; -- obtain the NUMBER of references of all sub-tables to an ID. FUNCTION get_refered_count (v_parent_table varchar2, v_parent_id NUMBER) RETURN fk_refered_count; -- obtain the number of reference records of all sub-tables that meet the conditions. 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 -- get the FUNCTION get_refering_stats (v_table_name varchar2) RETURN fk_stats IS v_ret fk_stats: = fk_stats (); begin select cast (multiset (SELECT. TABLE_NAME from table,. column_name foreign key column, B. TABLE_NAME primary table, B. column_name referenced column 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. r_constraint_name = B. constraint_name AND. TABLE_NAME = UPPER (v_table_name) AS fk_stats) INTO v_ret FROM dual; RETURN v_ret; END get_refering_stats; -- get all sub-tables and foreign key columns FUNCTION get_refered_stats (v_table_name varchar2) RETURN fk_stats IS v_ret fk_stats: = fk_stats (); begin select cast (multiset (SELECT. TABLE_NAME from table,. column_name foreign key column, B. TABLE_NAME primary table, B. column_name referenced column 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. 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; -- get the number of references of all subtables to an id function get_refered_count (v_parent_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; -- obtain the number of reference records of all subtables that meet the conditions. FUNCTION (v_parent_table varchar2, v_cond_col varchar2, v_cond varchar2) RETURN response 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 (values (v_parent_table, id_row.column_value) loop v_ret.extend (1); v_ret (v_ret.COUNT): = count (count_row.child_table, limit, limit ); END loop; RETURN v_ret; END get_refered_count_cond; END FK_UTIL ;/