实现自定义查询的数据库设计及实现（二）
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了实现自定义查询的数据库设计及实现（二），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5439字，纯文字阅读大概需要8分钟。
内容图文
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 描述
本文系统来源：http://www.cnblogs.com/yika/p/7742782.html
内容总结
以上是互联网集市为您收集整理的实现自定义查询的数据库设计及实现（二）全部内容，希望文章能够帮你解决实现自定义查询的数据库设计及实现（二）所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】