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 EXISTS tmpLog ; 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 OPEN cursorSurveil ; 33 FETCH cursorSurveil INTO insertedDatetime, licenseNumber , pointNumber ; 34 WHILE( insertedDatetime is not null) DO 35 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 OPEN cursorRule; 56 -- //遍历游标 57 FETCH cursorRule INTO ruleId ; 58 -- //把游标查询出的 name 都加起并用 ; 号隔开 59 WHILE( ruleId is not null) DO 60 -- 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 OPEN cursorRuleItem ; 83 FETCH cursorRuleItem INTO ruleItemId, fieldName ; 84 WHILE( ruleItemId is not null) DO 85 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' THEN fieldName 99 WHEN fieldName = 'pointNumber' THEN fieldName 100 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.valueMaximun 120 from 121 t_rule_item_value as t 122 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 OPEN cursorRuleItemValue ; 132 FETCH cursorRuleItemValue INTO ruleItemValueId, comparisonType , itemValue , valueMinmum , valueMaximun ; 133 WHILE( ruleItemValueId is not null ) DO 134 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 INTO ruleItemValueId, comparisonType , itemValue , valueMinmum , valueMaximun ; 188 end if ; 189 190 END WHILE ; -- 规则项值 191 192 CLOSE cursorRuleItemValue ; 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 INTO ruleItemId, fieldName ; 205 else 206 set isAllItemMatch = 0 ; 207 SET ruleItemId = null ; 208 end if ; 209 210 END WHILE ; 211 212 CLOSE cursorRuleItem ; 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 INTO ruleId ; 226 227 END WHILE; 228 CLOSE cursorRule; 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 INTO insertedDatetime, licenseNumber , pointNumber ; 240 241 END WHILE ; 242 243 CLOSE cursorSurveil ; 244 245 INSERT tmpLog(log) VALUES ('监控数据循环结束'); 246 247 248 SELECT * from tmpLog ; 249 250 DROP TEMPORARY TABLE IF EXISTS tmpLog ; 251 252 END