oracle外键反查套件

2015/03/30 37728

清华大佬耗费三个月吐血整理的几百G的资源，免费分享！....>>>

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
-- 以下是安装脚本
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;
/