自定义mysql数据库设计_实现自定义查询的数据库设计及实现（二）

1 create procedurebas_getrelatablepublic_sp2

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 intov_idparent,v_tableasparent34

35 frombas_table_relate_tb36

37 where id_table =p_idtable38

39 and id_table_rela =p_idtable40

41 and column_name =column_name_rela ;42

43

44

45 insert intobas_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 frombas_table_relate_tb b,bas_table_tb c58

59 where b.id_table =p_idtable60

61 and b.id_table =b.id_table_rela62

63 and b.id_table =c.id_table64

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 intobas_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_idparent84

85 frombas_table_relate_tb b,bas_table_tb c,bas_table_relate_tb h86

87 where b.id_table =p_idtable88

89 and b.id_table_rela =c.id_table90

91 and b.id_table =h.id_table92

93 and b.id_table =h.id_table_rela94

95 and h.id_table =h.id_table_rela96

97 and h.column_name =h.column_name_rela98

99 and b.id_table <>b.id_table_rela;100

101

102

103 while row_count() > 0do104

105 insert intobas_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_tabrela116

117 frombas_table_relate_tb b,bas_table_tb c,bas_table_temp_tb e118

119 where e.cnnid =p_idtable120

121 and e.id_table =b.id_table122

123 and b.id_table_rela =c.id_table124

125 and b.id_table <>b.id_table_rela126

127 and e.level_cnt =v_levelcnt128

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_idtable146

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_idtable154

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_idtable162

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_idtable170

171 and main_flag = "1";172

173 end;