存储过程一例
View Code
1 CREATE PROCEDURE dbo.Pr_SplitData 2 3 AS 4 5 DECLARE @Result INT 6 DECLARE @Res INT 7 8 --用于保存TMCountTemp表中的数据 9 DECLARE @UU INT 10 DECLARE @TempUU VARCHAR(8) 11 DECLARE @ComUU INT 12 DECLARE @Time datetime 13 DECLARE @IP VARCHAR(15) 14 DECLARE @IsComeBack INT 15 DECLARE @InfoTitle VARCHAR(100) 16 DECLARE @ComeHeadInfo VARCHAR(15) 17 DECLARE @CountTP VARCHAR(15) 18 DECLARE @CountID INT 19 DECLARE @ComeWeb VARCHAR(15) 20 DECLARE @ComeKeyWord VARCHAR(100) 21 DECLARE @NowPage VARCHAR(600) 22 DECLARE @SpeTP VARCHAR(15) 23 DECLARE @SpeID INT 24 DECLARE @SpeKeyA VARCHAR(15) 25 DECLARE @SpeKeyB VARCHAR(15) 26 DECLARE @SpeKeyC VARCHAR(15) 27 DECLARE @SpeCountry VARCHAR(15) 28 DECLARE @SpeSheng VARCHAR(15) 29 DECLARE @SpeShi VARCHAR(15) 30 DECLARE @InTourMall VARCHAR(15) 31 DECLARE @InTourunion VARCHAR(15) 32 33 --用户保存TMCount中的原始数据 34 DECLARE @TMCountID INT 35 DECLARE @TotalClick INT 36 37 --用户保存TMCountMore中的原始数据 38 DECLARE @TempCountID INT 39 DECLARE @TcmID INT 40 DECLARE @StrComeWeb NVARCHAR(50) 41 DECLARE @CountDate DATETIME 42 DECLARE @NameSheng VARCHAR(20) 43 44 --声明游标变量 45 DECLARE temp_cursor CURSOR FOR 46 SELECT [ID] FROM TMCountTemp 47 48 DECLARE @Field INT 49 50 --打开游标 51 OPEN temp_cursor 52 53 --将实际ID赋给变量 54 FETCH NEXT from temp_cursor INTO @Field 55 56 WHILE @@FETCH_STATUS=0 57 BEGIN 58 --取出一条记录,存入临时变量 59 SELECT @UU = UU, 60 @Time = [Time], 61 @TempUU = TempUU, 62 @ComUU = ComUU, 63 @IP = IP, 64 @IsComeBack = IsComeBack, 65 @InfoTitle = InfoTitle, 66 @ComeHeadInfo = ComeHeadInfo, 67 @CountTP = CountTP, 68 @CountID = CountID, 69 @ComeWeb = ComeWeb, 70 @ComeKeyWord = ComeKeyWord, 71 @NowPage = NowPage, 72 @SpeTP = SpeTP, 73 @SpeID = SpeID, 74 @SpeKeyA = SpeKeyA, 75 @SpeKeyB = SpeKeyB, 76 @SpeKeyC = SpeKeyC, 77 @InTourMall = InTourMall, 78 @InTourunion = InTourunion 79 FROM [TMCountTemp] WHERE [ID] = @Field 80 81 IF @CountTP IS NOT NULL AND @InfoTitle IS NOT NULL AND @CountID IS NOT NULL 82 BEGIN 83 --查询该板块是否存在 84 SELECT @Result = COUNT(*) FROM [TMCount] WHERE InfoID = @CountID AND InfoType = @CountTP AND InfoTitle = @InfoTitle 85 END 86 87 ELSE 88 BEGIN 89 SET @InfoTitle = '' 90 SET @CountTP = '' 91 SET @CountID = 0 92 END 93 94 95 96 --如果TMCount已存在此板块,就查出其CountID,然后更新 97 IF @Result = 1 98 BEGIN 99 SELECT @TMCountID = CountID FROM [TMCount] WHERE InfoID = @CountID AND InfoType = @CountTP AND InfoTitle = @InfoTitle 100 UPDATE [TMCount] SET TotaleClick = TotaleClick + 1 WHERE InfoID = @CountID AND InfoType = @CountTP AND InfoTitle = @InfoTitle 101 END 102 103 --如果TMCount没有此板块,就插入一条新纪录,并查询其CountID 104 IF @Result = 0 105 BEGIN 106 INSERT INTO [TMCount] (InfoType,InfoID,InfoTitle,TotaleClick) VALUES (@CountTP,@CountID,@InfoTitle,1) 107 SELECT @TMCountID = CountID FROM [TMCount] WHERE InfoID = @CountID AND InfoType = @CountTP AND InfoTitle = @InfoTitle 108 END 109 110 --查询TMCountMore是否已有该板块 111 SELECT @Res = COUNT(*) FROM [TMCountMore] WHERE TempCountID = @TMCountID AND DATEDIFF(month ,@Time,CountDate) = 0 112 113 SELECT @CountDate = CountDate,@TcmID = CountID FROM [TMCountMore] WHERE TempCountID = @TMCountID AND DATEDIFF(month ,@Time,CountDate) = 0 114 115 116 --根据网址判断来自于哪个搜索引擎 117 118 IF dbo.RegularMatch(@ComeWeb, '[baidu]{5}', 1) = 1 119 BEGIN 120 SET @StrComeWeb = 'baidu' 121 END 122 IF dbo.RegularMatch(@ComeWeb, '[yahoo]{5}', 1) = 1 123 BEGIN 124 SET @StrComeWeb = 'yahoo' 125 END 126 IF dbo.RegularMatch(@ComeWeb, '[soso]{4}', 1) = 1 127 BEGIN 128 SET @StrComeWeb = 'soso' 129 END 130 IF dbo.RegularMatch(@ComeWeb, '[google]{6}', 1) = 1 131 BEGIN 132 SET @StrComeWeb = 'google' 133 END 134 IF dbo.RegularMatch(@ComeWeb, '[sohu]{4}', 1) = 1 135 BEGIN 136 SET @StrComeWeb = 'sohu' 137 END 138 IF dbo.RegularMatch(@ComeWeb, '[sina]{4}', 1) = 1 139 BEGIN 140 SET @StrComeWeb = 'sina' 141 END 142 IF dbo.RegularMatch(@ComeWeb, '[163]{3}', 1) = 1 143 BEGIN 144 SET @StrComeWeb = '163' 145 END 146 IF dbo.RegularMatch(@ComeWeb, '[tom]{3}', 1) = 1 147 BEGIN 148 SET @StrComeWeb = 'tom' 149 END 150 IF dbo.RegularMatch(@ComeWeb, '[sogou]{5}', 1) = 1 151 BEGIN 152 SET @StrComeWeb = 'sogou' 153 END 154 155 --解析IP地址,判断属于哪个省市 156 SELECT @SpeSheng = dbo.MatchIPAdress(@IP) 157 158 IF @SpeSheng = '重庆' 159 BEGIN 160 SET @NameSheng = 'ChongQin' 161 END 162 ELSE IF @SpeSheng = '浙江' 163 BEGIN 164 SET @NameSheng = 'ZheJiang' 165 END 166 ELSE IF @SpeSheng = '云南' 167 BEGIN 168 SET @NameSheng = 'YunNan' 169 END 170 ELSE IF @SpeSheng = '新疆' 171 BEGIN 172 SET @NameSheng = 'XinJiang' 173 END 174 ELSE IF @SpeSheng = '西藏' 175 BEGIN 176 SET @NameSheng = 'XiZang' 177 END 178 ELSE IF @SpeSheng = '天津' 179 BEGIN 180 SET @NameSheng = 'TianJin' 181 END 182 ELSE IF @SpeSheng = '台湾' 183 BEGIN 184 SET @NameSheng = 'TaiWan' 185 END 186 ELSE IF @SpeSheng = '四川' 187 BEGIN 188 SET @NameSheng = 'SiChuan' 189 END 190 ELSE IF @SpeSheng = '上海' 191 BEGIN 192 SET @NameSheng = 'ShangHai' 193 END 194 ELSE IF @SpeSheng = '山西' 195 BEGIN 196 SET @NameSheng = 'ShaanXi' 197 END 198 ELSE IF @SpeSheng = '陕西' 199 BEGIN 200 SET @NameSheng = 'ShanXi' 201 END 202 ELSE IF @SpeSheng = '山东' 203 BEGIN 204 SET @NameSheng = 'ShanDong' 205 END 206 ELSE IF @SpeSheng = '青海' 207 BEGIN 208 SET @NameSheng = 'QingHai' 209 END 210 ELSE IF @SpeSheng = '宁夏' 211 BEGIN 212 SET @NameSheng = 'NingXia' 213 END 214 ELSE IF @SpeSheng = '内蒙古' 215 BEGIN 216 SET @NameSheng = 'NeiMengGu' 217 END 218 ELSE IF @SpeSheng = '辽宁' 219 BEGIN 220 SET @NameSheng = 'LiaoNin' 221 END 222 ELSE IF @SpeSheng = '江西' 223 BEGIN 224 SET @NameSheng = 'JiangXi' 225 END 226 ELSE IF @SpeSheng = '江苏' 227 BEGIN 228 SET @NameSheng = 'JiangSu' 229 END 230 ELSE IF @SpeSheng = '吉林' 231 BEGIN 232 SET @NameSheng = 'JiLin' 233 END 234 ELSE IF @SpeSheng = '湖南' 235 BEGIN 236 SET @NameSheng = 'HuNan' 237 END 238 ELSE IF @SpeSheng = '湖北' 239 BEGIN 240 SET @NameSheng = 'HuBei' 241 END 242 ELSE IF @SpeSheng = '黑龙江' 243 BEGIN 244 SET @NameSheng = 'HeiLongJiang' 245 END 246 ELSE IF @SpeSheng = '河南' 247 BEGIN 248 SET @NameSheng = 'HeNan' 249 END 250 ELSE IF @SpeSheng = '河北' 251 BEGIN 252 SET @NameSheng = 'HeBei' 253 END 254 ELSE IF @SpeSheng = '海南' 255 BEGIN 256 SET @NameSheng = 'HaiNan' 257 END 258 ELSE IF @SpeSheng = '贵州' 259 BEGIN 260 SET @NameSheng = 'GuiZhou' 261 END 262 ELSE IF @SpeSheng = '广西' 263 BEGIN 264 SET @NameSheng = 'GuangXi' 265 END 266 ELSE IF @SpeSheng = '广东' 267 BEGIN 268 SET @NameSheng = 'GuangDong' 269 END 270 ELSE IF @SpeSheng = '甘肃' 271 BEGIN 272 SET @NameSheng = 'GanSu' 273 END 274 ELSE IF @SpeSheng = '福建' 275 BEGIN 276 SET @NameSheng = 'FuJian' 277 END 278 ELSE IF @SpeSheng = '北京' 279 BEGIN 280 SET @NameSheng = 'BeiJing' 281 END 282 ELSE IF @SpeSheng = '澳门' 283 BEGIN 284 SET @NameSheng = 'AoMen' 285 END 286 ELSE IF @SpeSheng = '安徽' 287 BEGIN 288 SET @NameSheng = 'AnHui' 289 END 290 ELSE 291 BEGIN 292 SET @NameSheng = 'GuoWai' 293 END 294 295 IF @NowPage IS NULL 296 BEGIN 297 SET @NowPage = '' 298 END 299 IF @StrComeWeb IS NULL 300 BEGIN 301 PRINT('解析搜索引擎失败') 302 END 303 IF @NameSheng IS NULL 304 BEGIN 305 PRINT('解析IP地址失败') 306 END 307 308 --如果TMCountMore已有该板块 309 IF @Res = 1 310 BEGIN 311 --比较月份,若相等进行更新 312 IF DATEDIFF(month ,@Time,@CountDate) = 0 313 BEGIN 314 DECLARE @up_sql NVARCHAR (1000) 315 SET @up_sql = 'UPDATE [TMCountMore] SET MonthClick = MonthClick + 1,LastHttp = '''+@NowPage+''','+@StrComeWeb+' = '+@StrComeWeb+'+1,'+@NameSheng+' = '+@NameSheng+'+1 WHERE CountID = '+CONVERT(VARCHAR,@TcmID)+'' 316 PRINT(@up_sql) 317 EXEC(@up_sql) 318 END 319 ELSE 320 BEGIN 321 DECLARE @in_sql NVARCHAR (1000) 322 SET @in_sql='INSERT INTO [TMCountMore] (TempCountID ,CountDate,LastHttp,MonthClick,'+@NameSheng+','+@StrComeWeb+') VALUES ('''+CONVERT(VARCHAR,@TMCountID)+''','''+CONVERT(VARCHAR,@Time,23)+''','+@NowPage+',1,1,1)' 323 PRINT(@in_sql) 324 EXEC(@in_sql) 325 END 326 END 327 328 --若TMCountMore不存在该板块,则插入一条新纪录 329 IF @Res = 0 330 BEGIN 331 DECLARE @sql NVARCHAR (1000) 332 SET @sql='INSERT INTO [TMCountMore] (TempCountID ,CountDate,LastHttp,MonthClick,'+@NameSheng+','+@StrComeWeb+') VALUES ('''+CONVERT(VARCHAR,@TMCountID)+''','''+CONVERT(VARCHAR,@Time,23)+''','+@NowPage+',1,1,1)' 333 PRINT(@sql) 334 EXEC(@sql) 335 END 336 337 --删除本条数据 338 --DELETE FROM [TMCoutnTemp] WHERE [ID] = @Field 339 340 341 --游标跳到下一个ID 342 FETCH NEXT FROM temp_cursor INTO @Field 343 END 344 345 346 --关闭游标 347 CLOSE temp_cursor 348 349 --删除游标 350 DEALLOCATE temp_cursor
工欲善其事,必先利其器。