oracle外键反查套件

以下代码由PHP站长网 52php.cn收集自互联网

现在PHP站长网小编把它分享给大家，仅供参考

-- 以下是安装脚本
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,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 从表,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 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;
/

以上内容由PHP站长网【52php.cn】收集整理供大家参考研究

如果以上内容对您有帮助，欢迎收藏、点赞、推荐、分享。

（编辑：衡阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!