create procedure bas_getrelatablepublic_sp
2
3 (
4
5 p_idtable
int -- 表ID
6
7 )
8
9 begin
10
11 declare v_levelcnt
int;
12
13 declare v_idparent
int;
14
15 declare v_tableasparent
varchar(
200);
16
17
18
19
20
21 set v_levelcnt
= 1;
22
23
24
25 -- 使用表ID作为 cnn ，判断计算过了，就不再计算，减少计算次数
26
27 -- 如果新增关联表，需要根据表ID把此表的数据删除
28
29
30
31 if not exists (
select 1 from bas_table_temp_tb f
where f.cnnid
= p_idtable )
then
32
33 select id_tabrela,tableas_name
into v_idparent,v_tableasparent
34
35 from bas_table_relate_tb
36
37 where id_table
= p_idtable
38
39 and id_table_rela
= p_idtable
40
41 and column_name
= column_name_rela ;
42
43
44
45 insert into bas_table_temp_tb ( cnnid,where_list,
46
47 table_name,level_cnt,id_table,tableas_name,end_flag,id_tabrela,main_flag,tableas_cnname,
48
49 id_tabtemp_parent,table_asname_parent,column_name,column_name_parent,id_tabrela_parent )
50
51 select distinct p_idtable,concat("
from ",c.tablereg_name," ",b.tableas_name ) whsql,
52
53 c.tablereg_name,v_levelcnt,c.id_table,b.tableas_name,"
1",b.id_tabrela,"
1",b.column_cnname,
54
55 v_idparent,v_tableasparent,"","",
0
56
57 from bas_table_relate_tb b,bas_table_tb c
58
59 where b.id_table
= p_idtable
60
61 and b.id_table
= b.id_table_rela
62
63 and b.id_table
= c.id_table
64
65 and b.column_name
= c.prk_name;
66
67
68
69 set v_tableasparent
= concat("A",last_insert_id(),"A");
70
71
72
73 insert into bas_table_temp_tb ( cnnid,where_list,
74
75 table_name,level_cnt,id_table,tableas_name,end_flag,id_tabrela,main_flag,tableas_cnname,
76
77 id_tabtemp_parent,table_asname_parent,column_name,column_name_parent,id_tabrela_parent )
78
79 select distinct p_idtable,concat("
left join ",c.tablereg_name," ",b.tableas_name,"
on ",b.tableas_name,".",b.column_name_rela,"
= ",h.tableas_name,".",b.column_name ) whsql,
80
81 c.tablereg_name,v_levelcnt,c.id_table,b.tableas_name,"
1",b.id_tabrela,"
0",concat(b.relatable_name,"
[",b.column_cnname,"]"),
82
83 v_idparent,v_tableasparent,b.column_name_rela,b.column_name,v_idparent
84
85 from bas_table_relate_tb b,bas_table_tb c,bas_table_relate_tb h
86
87 where b.id_table
= p_idtable
88
89 and b.id_table_rela
= c.id_table
90
91 and b.id_table
= h.id_table
92
93 and b.id_table
= h.id_table_rela
94
95 and h.id_table
= h.id_table_rela
96
97 and h.column_name
= h.column_name_rela
98
99 and b.id_table
<> b.id_table_rela;
100
101
102
103 while row_count()
> 0 do
104
105 insert into bas_table_temp_tb ( cnnid,where_list,
106
107 table_name,level_cnt,id_table,tableas_name,end_flag,id_tabrela,main_flag,tableas_cnname,
108
109 id_tabtemp_parent,column_name,column_name_parent,id_tabrela_parent )
110
111 select distinct p_idtable,concat("
left join ",c.tablereg_name," ",e.tableas_name,b.tableas_name,"
on ",e.tableas_name,b.tableas_name,".",b.column_name_rela,"
= ",e.tableas_name,".",b.column_name ) whsql,
112
113 c.tablereg_name,v_levelcnt
+ 1,c.id_table,concat(e.tableas_name,b.tableas_name),"
1",b.id_tabrela,"
0",concat(e.tableas_cnname,"
|",b.relatable_name,"
[",b.column_cnname,"]"),
114
115 e.id_tabtemp,b.column_name_rela,b.column_name,e.id_tabrela
116
117 from bas_table_relate_tb b,bas_table_tb c,bas_table_temp_tb e
118
119 where e.cnnid
= p_idtable
120
121 and e.id_table
= b.id_table
122
123 and b.id_table_rela
= c.id_table
124
125 and b.id_table
<> b.id_table_rela
126
127 and e.level_cnt
= v_levelcnt
128
129 and not exists (
select 1 from bas_table_temp_tb f
where f.cnnid
= p_idtable
and f.id_table
= c.id_table );
130
131
132
133 set v_levelcnt
= v_levelcnt
+ 1;
134
135 end while;
136
137
138
139 end if;
140
141
142
143 update bas_table_temp_tb
set table_asname
= concat("A",id_tabtemp,"A")
144
145 where cnnid
= p_idtable
146
147 and table_asname
is null;
148
149
150
151 update bas_table_temp_tb
set table_asname_parent
= concat("A",id_tabtemp_parent,"A")
152
153 where cnnid
= p_idtable
154
155 and table_asname_parent
is null;
156
157
158
159 update bas_table_temp_tb
set from_list
= concat("
left join ",table_name," ",table_asname,"
on ",table_asname,".",column_name,"
= ",table_asname_parent,".",column_name_parent)
160
161 where cnnid
= p_idtable
162
163 and main_flag
= "
0";
164
165
166
167 update bas_table_temp_tb
set from_list
= concat("
from ",table_name," ",table_asname)
168
169 where cnnid
= p_idtable
170
171 and main_flag
= "
1";
172
173 end;
实现自定义查询的数据库设计及实现（二）
标签：过程 mys lis declare 字段 实现 count table 描述
