Sql语言编程：数据表更新问题，急，在线等

[wenqiangyang]

目前做一个ip地址归属的统计，涉及如下问题：
表名:visittimes
字段：vtime(datetime访问时间)，ip(varchar(200)访问者ip地址)，ipnum（int，ip数，后来添加）
要求：将ip字段数据，如192。186。100。1，通过192*256*256*256+168*256*256+100*256+1的操作添加到ipnum字段。
我的方法是：但是CURSOR不支持变量操作。请DX指教！
DECLARE @tip varchar(200)


DECLARE abc CURSOR LOCAL FOR
SELECT @tip=ip FROM visittimes

OPEN abc

FETCH abc
DECLARE @ip4 INT
DECLARE @ip3 INT
DECLARE @ip2 INT
DECLARE @ip1 INT


DECLARE @sip4 varchar(200)
DECLARE @sip3 varchar(200)
DECLARE @sip2 varchar(200)
DECLARE @sip1 varchar(200)


set @ip4=0
set @ip3=0
set @ip2=0
set @ip1=0

set @sip4=substring(@tip,1,charindex('.',@tip,0)-1)
set @tip=substring(@tip,charindex('.',@tip,0)+1,len(@tip)-charindex('.',@tip,0))
set @sip3=substring(@tip,1,charindex('.',@tip,0)-1)
set @tip=substring(@tip,charindex('.',@tip,0)+1,len(@tip)-charindex('.',@tip,0))
set @sip2=substring(@tip,1,charindex('.',@tip,0)-1)
set @tip=substring(@tip,charindex('.',@tip,0)+1,len(@tip)-charindex('.',@tip,0))
set @sip1=substring(@tip,1,charindex('.',@tip,0)-1)
set @tip=substring(@tip,charindex('.',@tip,0)+1,len(@tip)-charindex('.',@tip,0))

set @ip4=cast(@sip4 as int)
set @ip3=cast(@sip3 as int)
set @ip2=cast(@sip2 as int)
set @ip1=cast(@sip1 as int)

UPDATE visitimes SET ipnum =@ip4*256*256*256+@ip3*256*256+@ip2*256+@ip1
FROM visitimes
WHERE CURRENT OF abc
GO

[txlicenhe]

1:好象没必要用游标
2：
如果你喜欢用游标，如下用:
DECLARE @tip varchar(200)
DECLARE abc CURSOR LOCAL FOR
SELECT ip FROM visittimes
open abc
fetch next from abc into @tip
DECLARE @ip4 INT
DECLARE @ip3 INT
DECLARE @ip2 INT
DECLARE @ip1 INT


DECLARE @sip4 varchar(200)
DECLARE @sip3 varchar(200)
DECLARE @sip2 varchar(200)
DECLARE @sip1 varchar(200)

while @@fetch_status = 0
begin
set @ip4=0
set @ip3=0
set @ip2=0
set @ip1=0

set @sip4=substring(@tip,1,charindex('.',@tip,0)-1)
set @tip=substring(@tip,charindex('.',@tip,0)+1,len(@tip)-charindex('.',@tip,0))
set @sip3=substring(@tip,1,charindex('.',@tip,0)-1)
set @tip=substring(@tip,charindex('.',@tip,0)+1,len(@tip)-charindex('.',@tip,0))
set @sip2=substring(@tip,1,charindex('.',@tip,0)-1)
set @tip=substring(@tip,charindex('.',@tip,0)+1,len(@tip)-charindex('.',@tip,0))
set @sip1=substring(@tip,1,charindex('.',@tip,0)-1)
set @tip=substring(@tip,charindex('.',@tip,0)+1,len(@tip)-charindex('.',@tip,0))

set @ip4=cast(@sip4 as int)
set @ip3=cast(@sip3 as int)
set @ip2=cast(@sip2 as int)
set @ip1=cast(@sip1 as int)

UPDATE visitimes SET ipnum =@ip4*256*256*256+@ip3*256*256+@ip2*256+@ip1
FROM visitimes where ip = @tip
fetch next from abc into @tip
end
close abc
deallocate abc
GO

[wenqiangyang]

谢谢！
还有别的方法吗？

[wenqiangyang]

还有，我的ip地址不是唯一关键字！

[verybigmouthz]

update visittimes
set ipnum = func_ip(ip)

再写一用户自定义函数 func_ip(string)返回值类型为int
实现功能将你表中例如"127.0.0.1"转换为127*256*256*256 + 0*256*256 + 0*256+ 1