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

wenqiangyang 2003-10-16 10:22:10
Sql语言编程:数据表更新问题,急,在线等
目前做一个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

...全文
40 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
zjcxc 2003-10-16
  • 打赏
  • 举报
回复
--例子
create table visittimes(vtime datetime,ip varchar(200),ipnum int)
insert into visittimes(ip)
select '192.168.0.1'
union all select '202.12.123.1'

go
create function f_getip(@ip varchar(200))
returns decimal(38,0)
as
begin
declare @i int,@re decimal(38,0),@ii int
select @re=0,@i=3,@ii=charindex('.',@ip)-1
while @ii>0
select @re=@re+power(256,@i)*cast(left(@ip,@ii) as bigint)
,@ip=substring(@ip,@ii+2,200)
,@ii=charindex('.',@ip)-1
,@i=@i-1
set @re=@re+power(256,@i)*cast(@ip as bigint)
return(@re)
end
go

select dbo.f_getip('202.96.128.68')

select *,dbo.f_getip(ip) from visittimes

drop table visittimes
drop function f_getip



zjcxc 2003-10-16
  • 打赏
  • 举报
回复
--用个自定义函数来处理就行了:
create function f_getip(@ip varchar(200))
returns decimal(38,0)
as
begin
declare @i int,@re decimal(38,0),@ii int
select @re=0,@i=3,@ii=charindex('.',@ip)-1
while @ii>0
select @re=@re+power(256,@i)*cast(left(@ip,@ii) as bigint)
,@ip=substring(@ip,@ii+2,200)
,@ii=charindex('.',@ip)-1
,@i=@i-1
set @re=@re+power(256,@i)*cast(@ip as bigint)
return(@re)
end
go
txlicenhe 2003-10-16
  • 打赏
  • 举报
回复
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

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧