简单实用SQL脚本Part:游标模板
游标是很常使用的SQL,所以这里给出一个模板,这里需要注意变量的命名和书写格式。
--模板
DECLARE @ID int, @GUID varchar(50),@LoginName varchar(50)
DECLARE @itemCur CURSOR
SET @itemCur = CURSOR FOR
SELECT ID,GUID,LoginName FROM [TableName]
WHERE LoginName = ''
OPEN @itemCur
FETCH NEXT FROM @itemCur INTO @ID,@GUID,@LoginName
WHILE @@FETCH_STATUS=0
BEGIN
--逻辑处理
FETCH NEXT FROM @itemCur INTO @ID,@GUID,@LoginName
END
CLOSE @itemCur
DEALLOCATE @itemCur
DECLARE @ID int, @GUID varchar(50),@LoginName varchar(50)
DECLARE @itemCur CURSOR
SET @itemCur = CURSOR FOR
SELECT ID,GUID,LoginName FROM [TableName]
WHERE LoginName = ''
OPEN @itemCur
FETCH NEXT FROM @itemCur INTO @ID,@GUID,@LoginName
WHILE @@FETCH_STATUS=0
BEGIN
--逻辑处理
FETCH NEXT FROM @itemCur INTO @ID,@GUID,@LoginName
END
CLOSE @itemCur
DEALLOCATE @itemCur
下面是一个游标的使用模板,里面的逻辑处理形如:PosSet:[573,1103,2010-09-03 22:32:35],[864,110,2010-09-03 22:32:57]这样字符串的分割,这个字段表示用户点击页面的坐标和时间。注意当字符串是PosSet:[573,1103,2010-09-03 22:32:35]时候的情况,因为这个时候就没有分隔字符在字符串了。关于这个问题的详细记录见:简单但有用的SQL脚本Part
--实例
DECLARE @ID int, @GUID varchar(50),@LoginName varchar(50),@PosSet varchar(2000), @Resolution varchar(20)
DECLARE @splitlen int
DECLARE @split nvarchar(10)
DECLARE @Temp varchar(100)
DECLARE @PosSet_x int
DECLARE @PosSet_y int
--set @split = '],'
set @split = ']'
DECLARE @itemCur CURSOR
SET @itemCur = CURSOR FOR
SELECT ID,GUID,LoginName,PosSet,Resolution FROM [VisiteLog_Statistical_Temp]
WHERE PosSet <> ''
OPEN @itemCur
FETCH NEXT FROM @itemCur INTO @ID,@GUID,@LoginName,@PosSet,@Resolution
WHILE @@FETCH_STATUS=0
BEGIN
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@PosSet)>0
BEGIN
SET @Temp = LEFT(@PosSet,CHARINDEX(@split,@PosSet)-1)
SET @Temp = replace(@Temp,'[','')
SET @PosSet_x = dbo.Get_StrArrayStrOfIndex(@Temp,',',1)
SET @PosSet_y = dbo.Get_StrArrayStrOfIndex(@Temp,',',2)
INSERT t_Guid VALUES(@ID,@GUID,@LoginName,@Resolution,@PosSet_x,@PosSet_y)
SET @PosSet=STUFF(@PosSet,1,CHARINDEX(@split,@PosSet)+@splitlen,'')
END
IF NOT EXISTS(SELECT ID FROM t_Guid WHERE ID = @ID)--只有一个坐标
BEGIN
SET @Temp = replace(replace(@PosSet,'[',''),']','')
SET @PosSet_x = dbo.Get_StrArrayStrOfIndex(@Temp,',',1)
SET @PosSet_y = dbo.Get_StrArrayStrOfIndex(@Temp,',',2)
INSERT t_Guid VALUES(@ID,@GUID,@LoginName,@Resolution,@PosSet_x,@PosSet_y)
--print @GUID
END
FETCH NEXT FROM @itemCur INTO @ID,@GUID,@LoginName,@PosSet,@Resolution
END
CLOSE @itemCur
DEALLOCATE @itemCur
DECLARE @ID int, @GUID varchar(50),@LoginName varchar(50),@PosSet varchar(2000), @Resolution varchar(20)
DECLARE @splitlen int
DECLARE @split nvarchar(10)
DECLARE @Temp varchar(100)
DECLARE @PosSet_x int
DECLARE @PosSet_y int
--set @split = '],'
set @split = ']'
DECLARE @itemCur CURSOR
SET @itemCur = CURSOR FOR
SELECT ID,GUID,LoginName,PosSet,Resolution FROM [VisiteLog_Statistical_Temp]
WHERE PosSet <> ''
OPEN @itemCur
FETCH NEXT FROM @itemCur INTO @ID,@GUID,@LoginName,@PosSet,@Resolution
WHILE @@FETCH_STATUS=0
BEGIN
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@PosSet)>0
BEGIN
SET @Temp = LEFT(@PosSet,CHARINDEX(@split,@PosSet)-1)
SET @Temp = replace(@Temp,'[','')
SET @PosSet_x = dbo.Get_StrArrayStrOfIndex(@Temp,',',1)
SET @PosSet_y = dbo.Get_StrArrayStrOfIndex(@Temp,',',2)
INSERT t_Guid VALUES(@ID,@GUID,@LoginName,@Resolution,@PosSet_x,@PosSet_y)
SET @PosSet=STUFF(@PosSet,1,CHARINDEX(@split,@PosSet)+@splitlen,'')
END
IF NOT EXISTS(SELECT ID FROM t_Guid WHERE ID = @ID)--只有一个坐标
BEGIN
SET @Temp = replace(replace(@PosSet,'[',''),']','')
SET @PosSet_x = dbo.Get_StrArrayStrOfIndex(@Temp,',',1)
SET @PosSet_y = dbo.Get_StrArrayStrOfIndex(@Temp,',',2)
INSERT t_Guid VALUES(@ID,@GUID,@LoginName,@Resolution,@PosSet_x,@PosSet_y)
--print @GUID
END
FETCH NEXT FROM @itemCur INTO @ID,@GUID,@LoginName,@PosSet,@Resolution
END
CLOSE @itemCur
DEALLOCATE @itemCur