删除Web日志存储过程-优快云博客

本文介绍了一个用于删除特定用户Web日志记录的SQL存储过程。该过程通过计算时间使用率来更新用户的历史在线时间，并从数据库中移除超过一定时间阈值的Web地址日志。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create procedure deletemibowebtime(@id varchar(50))
as
declare @total_time float,
            @changetime float,
            @rate float,
            @webaddress varchar(200),
            @total_timemax float

declare web_cur cursor for
select web_add,total_time    from
OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=10.112.65.6;User ID=LogDel;Password=LogDel'
         ).WEB_Log.dbo.WEB_DAY_ADDRESS
WHERE (network_acc ='UNIMICRON/'+@id+'')
AND (datediff(month,   LOGIN_DATE,GETDATE())=0) and (datediff(year,   LOGIN_DATE,GETDATE())=0)
and total_time>=3*60*1000
open web_cur

Fetch next from web_cur into @webaddress,@total_timemax

print @webaddress

while @@Fetch_status=0
begin

select @changetime=sum(total_time)   from
OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=10.112.65.6;User ID=LogDel;Password=LogDel'
         ).WEB_Log.dbo.WEB_DAY_ADDRESS
WHERE (network_acc ='UNIMICRON/'+@id+'')
AND (datediff(month,   LOGIN_DATE,GETDATE())=0) and (datediff(year,   LOGIN_DATE,GETDATE())=0)
and web_add in (''+@webaddress+'')

select @total_time=sum(total_time)   from
OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=10.112.65.6;User ID=LogDel;Password=LogDel'
         ).WEB_Log.dbo.WEB_DAY_ADDRESS
WHERE (network_acc ='UNIMICRON/'+@id+'')
AND (datediff(month,   LOGIN_DATE,GETDATE())=0) and (datediff(year,   LOGIN_DATE,GETDATE())=0)

set @rate= (@total_time-@changetime)/@total_time

if @rate>0
begin
update    OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=10.112.65.6;User ID=LogDel;Password=LogDel'
         ).WEB_Log.dbo.WB_HOURS

set H_00=H_00*@rate,H_01=H_01*@rate,
H_02=H_02*@rate,H_03=H_03*@rate,H_04=H_04*@rate,
H_05=H_05*@rate,H_06=H_06*@rate,H_07=H_07*@rate,
H_08=H_08*@rate,H_09=H_09*@rate,H_10=H_10*@rate,
H_11=H_11*@rate,H_12=H_12*@rate,H_13=H_13*@rate,
H_14=H_14*@rate,H_15=H_15*@rate,H_16=H_16*@rate,
H_17=H_17*@rate,H_18=H_18*@rate,H_19=H_19*@rate,
H_20=H_20*@rate,H_21=H_21*@rate,H_22=H_22*@rate,
H_23=H_23*@rate, total_time = total_time*@rate
WHERE (network_acc ='UNIMICRON/'+@id+'')
AND (datediff(month, LOGIN_DATE,GETDATE())=0)
and (datediff(year, LOGIN_DATE,GETDATE())=0)

delete from OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=10.112.65.6;User ID=LogDel;Password=LogDel'
         ).WEB_Log.dbo.WEB_DAY_ADDRESS
WHERE (network_acc ='UNIMICRON/'+@id+'')
AND (datediff(month,   LOGIN_DATE,GETDATE())=0) and (datediff(year,   LOGIN_DATE,GETDATE())=0)
and web_add in (''+@webaddress+'')
end

Fetch next from web_cur into @webaddress,@total_timemax
end

close web_cur
deallocate web_cur