存储过程一例

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

 

posted @ 2012-12-14 17:36  宁静.致远  阅读(570)  评论(0)  编辑  收藏  举报