mysql 游标性能_MySQL 存储过程，一个 4 层的游标，用于规则比对，不知道性能如何。...

1 BEGIN

2 #Routine body goes here...3

4 --现可以限制 卡口及号牌

5

6 --//定义变量

7

8

9 declare insertedDatetime TIMESTAMP;10 declare licenseNumber varchar(50) default '';11 declare pointNumber varchar(50) default '';12

13 declare cursorSurveil CURSOR FOR select t.insertedDatetime, t.licenseNumber, t.pointNumber from t_surveil as t WHERE t.matchRuleIds is null;14 declare CONTINUE HANDLER FOR SQLSTATE '02000' SET insertedDatetime = null;15

16

17 DROP TEMPORARY TABLE IF EXISTStmpLog ;18

19 CREATE temporary table `tmpLog`( `insertedDatetime` timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,20 `log` VARCHAR(2000) not null ) ENGINE=MEMORY ;21

22

23 INSERT tmpLog(log) VALUES ('以下是日志内容');24

25 --SELECT * from tmpLog ;

26

27 INSERT tmpLog(log) VALUES ( concat( '监控数据开始') );28

29

30

31 --监控数据

32 OPENcursorSurveil ;33 FETCH cursorSurveil INTOinsertedDatetime, licenseNumber , pointNumber ;34 WHILE( insertedDatetime is not null) DO35

36 --SELECT 't_surveil' title, `insertedDatetime` ;

37

38 INSERT tmpLog(log) VALUES ( concat( '监控数据 t_surveil, insertedDatetime =', insertedDatetime ) );39 INSERT tmpLog(log) VALUES ( concat( '监控数据 t_surveil, licenseNumber =', licenseNumber ) );40 INSERT tmpLog(log) VALUES ( concat( '监控数据 t_surveil, pointNumber =', pointNumber ) );41

42 begin

43

44 --匹配的规则 ID

45 DECLARE matchRuleIds varchar(2000) DEFAULT '0';46

47 declare ruleId INT DEFAULT 0;48 declare cursorRule CURSOR FOR select id from t_rule as t WHERE t.activeStatus = 1;49 declare CONTINUE HANDLER FOR SQLSTATE '02000' SET ruleId = null;50

51

52 INSERT tmpLog(log) VALUES ( concat( '规则开始') );53

54 --//打开游标

55 OPENcursorRule;56 --//遍历游标

57 FETCH cursorRule INTOruleId ;58 --//把游标查询出的 name 都加起并用 ; 号隔开

59 WHILE( ruleId is not null) DO60 --SELECT ruleId ;

61 --SELECT 't_rule' title, ruleId ;

62

63 INSERT tmpLog(log) VALUES ( concat( 't_rule, ruleId =', ruleId ) );64

65 --规则项

66 --规则项之间是 AND 的关系。即要全部成立才认为匹配成功

67

68 BEGIN

69

70 declare isThisItemMatch int default 0 ; --本项是否匹配项

71 DECLARE isAllItemMatch int default 1 ; --是否所有的项目都匹配？

72 DECLARE itemCount INT DEFAULT 0 ; --项数量。是否存在至少一个项？

73

74 declare ruleItemId INT DEFAULT 0;75 DECLARE fieldName VARCHAR(50) ;76

77 declare cursorRuleItem CURSOR FOR select id, t.fieldName from t_rule_item as t WHERE t.ruleId =ruleId ;78 declare CONTINUE HANDLER FOR SQLSTATE '02000' SET ruleItemId = null;79

80 INSERT tmpLog(log) VALUES ( concat( '规则项开始') );81

82 OPENcursorRuleItem ;83 FETCH cursorRuleItem INTOruleItemId, fieldName ;84 WHILE( ruleItemId is not null) DO85

86 --SELECT 't_rule_item' title, ruleItemId ;

87

88

89 INSERT tmpLog(log) VALUES ( concat( 't_rule_item, ruleItemId =', ruleItemId ) );90

91 INSERT tmpLog(log) VALUES ( concat( 't_rule_item, fieldName =', fieldName ) );92

93 SET itemCount = itemCount + 1 ; --项目数

94

95 INSERT tmpLog(log) VALUES ( concat( 't_rule_item, itemCount =', itemCount ) );96

97 SET fieldName = CASE

98 WHEN fieldName = 'licenseNumber' THENfieldName99 WHEN fieldName = 'pointNumber' THENfieldName100 END;101

102

103 BEGIN

104

105 --规则项值

106 --规则项值之间是 OR 的关系，即有一条成立，即认为匹配成功。

107 --一但找到匹配，就退出本层游标

108

109 declare hasMatchedValue int default 0 ; --是否匹配值

110

111 declare ruleItemValueId INT DEFAULT 0;112 declare comparisonType varchar(50) DEFAULT '';113 declare itemValue varchar(200) DEFAULT '';114 declare valueMinmum varchar(200) DEFAULT '';115 declare valueMaximun varchar(200) DEFAULT '';116

117 declare cursorRuleItemValue CURSOR FOR

118 select

119 t.id, t.comparisonType, t.itemValue, t.valueMinmum, t.valueMaximun120 from

121 t_rule_item_value ast122 WHERE

123 t.ruleItemId =ruleItemId ;124

125 declare CONTINUE HANDLER FOR SQLSTATE '02000' SET ruleItemValueId = null;126

127 --SET hasMatchedValue = 0 ;

128

129 INSERT tmpLog(log) VALUES ( concat( '项值开始') );130

131 OPENcursorRuleItemValue ;132 FETCH cursorRuleItemValue INTOruleItemValueId, comparisonType , itemValue , valueMinmum , valueMaximun ;133 WHILE( ruleItemValueId is not null) DO134

135 --SELECT 't_rule_item_value' title, ruleItemValueId ;

136

137 INSERT tmpLog(log) VALUES ( concat( 't_rule_item_value, ruleItemValueId =', ruleItemValueId ) );138 INSERT tmpLog(log) VALUES ( concat( 't_rule_item_value, comparisonType =', comparisonType ) );139 INSERT tmpLog(log) VALUES ( concat( 't_rule_item_value, itemValue =', itemValue ) );140

141 --比较 - 等于

142 IF comparisonType = '等于' OR comparisonType = 'equal' then

143

144 INSERT tmpLog(log) VALUES ( concat( '等于') );145

146

147 if fieldName = 'licenseNumber' then

148 IF licenseNumber = itemValue then

149 SET hasMatchedValue = 1;150 INSERT tmpLog(log) VALUES ( concat( 't_rule_item_value, 找到匹配值！！！ licenseNumber') );151 END IF;152 end if;153

154 if fieldName = 'pointNumber' then

155 IF pointNumber = itemValue then

156 SET hasMatchedValue = 1;157 INSERT tmpLog(log) VALUES ( concat( 't_rule_item_value, 找到匹配值！！！ pointNumber') );158 END IF;159 end if;160

161 END IF;162

163 --比较 - 区间

164 IF comparisonType = '区间' or comparisonType = 'between' then

165

166 if fieldName = 'licenseNumber' then

167 IF licenseNumber >= valueMinmum AND licenseNumber <= valueMaximun then

168 SET hasMatchedValue = 1;169 END IF;170 end if;171

172 if fieldName = 'pointNumber' then

173 IF pointNumber >= valueMinmum AND pointNumber <= valueMaximun then

174 SET hasMatchedValue = 1;175 END IF;176 end if;177

178 END IF;179

180 --select hasMatchedValue ;

181

182 IF hasMatchedValue = 1 THEN

183 SET isThisItemMatch = 1 ; --本项匹配 ！！

184 SET ruleItemValueId = null ; --结束本层循环

185 INSERT tmpLog(log) VALUES ('存在匹配的项值，本项匹配，退出项值循环。');186 ELSE

187 FETCH cursorRuleItemValue INTOruleItemValueId, comparisonType , itemValue , valueMinmum , valueMaximun ;188 end if;189

190 END WHILE ; --规则项值

191

192 CLOSEcursorRuleItemValue ;193

194 --SELECT 'rule item value over ' msg ;

195

196

197 INSERT tmpLog(log) VALUES ('项值结束');198

199

200 END;201

202

203 IF isThisItemMatch = 1 THEN

204 FETCH cursorRuleItem INTOruleItemId, fieldName ;205 else

206 set isAllItemMatch = 0;207 SET ruleItemId = null;208 end if;209

210 END WHILE;211

212 CLOSEcursorRuleItem ;213

214 INSERT tmpLog(log) VALUES ('项结束');215

216 --！！！！ 如果全部匹配 ！！！！

217 IF isAllItemMatch = 1 AND itemCount > 0 THEN

218 --这个规则符合 ！！！

219 set matchRuleIds = concat( matchRuleIds , ',', ruleId ) ;220 end if;221

222 END;223

224 --SHOW VARIABLES ruleId ;

225 FETCH cursorRule INTOruleId ;226

227 END WHILE;228 CLOSEcursorRule;229 --select mycursor;

230 INSERT tmpLog(log) VALUES ('规则结束');231

232 --写入匹配结果

233 --UPDATE t_surveil as t SET t.matchRuleIds = matchRuleIds WHERE t.insertedDatetime = insertedDatetime ;

234 --SELECT matchRuleIds, insertedDatetime ;

235 INSERT tmpLog(log) VALUES ( concat( 'matchRuleIds =', matchRuleIds ,', insertedDatetime =', insertedDatetime ) );236

237 end;238

239 FETCH cursorSurveil INTOinsertedDatetime, licenseNumber , pointNumber ;240

241 END WHILE;242

243 CLOSEcursorSurveil ;244

245 INSERT tmpLog(log) VALUES ('监控数据循环结束');246

247

248 SELECT * fromtmpLog ;249

250 DROP TEMPORARY TABLE IF EXISTStmpLog ;251

252 END