当前位置:Gxlcms >
数据库问题 >
USE [EPPM] [dbo].[REFRDEL_CLEANUP]
USE [EPPM] [dbo].[REFRDEL_CLEANUP]
时间:2021-07-01 10:21:17
帮助过:4人阅读
USE [EPPM]
2 GO
3 /****** Object: StoredProcedure [dbo].[REFRDEL_CLEANUP] Script Date: 2016/4/2 16:32:29 ******/
4 SET ANSI_NULLS
ON
5 GO
6 SET QUOTED_IDENTIFIER
ON
7 GO
8 ALTER PROCEDURE [dbo].
[REFRDEL_CLEANUP]
9 (
@pret_val integer OUTPUT,
@pret_msg varchar(
1000) OUTPUT )
AS
10 declare @curr_time datetime
11 declare @vdiff integer
12 declare @vinterval_date datetime
13 declare @vset varchar(
20)
14 declare @vsetnum integer
15 declare @min_refrdel_date datetime
16 declare @delete_target datetime
17 declare @max_minutes_to_sweep integer
18 declare @interval_step integer
19 declare @cnt integer
20 declare @i integer
21 declare @total_cleared integer
22 begin try
23 set @curr_time=getdate()
24 set @cnt=0
25 set @i=1
26 set @pret_val=0
27 set @total_cleared=0
28 exec settings_read_number
@max_minutes_to_sweep OUTPUT,
‘database.cleanup.Refrdel‘,
‘DaysToDelete‘,
1
29 exec settings_read_number
@interval_step OUTPUT,
‘database.cleanup.Refrdel‘,
‘IntervalStep‘,
15
30 exec settings_read_string
@vset OUTPUT,
‘database.cleanup.Refrdel‘,
‘KeepInterval‘,
‘5d‘
31 set @max_minutes_to_sweep=@max_minutes_to_sweep*1440
32 exec get_interval_difference
@vset,
@vdiff OUTPUT
33 set @vinterval_date = @curr_time-@vdiff
34 print ‘Keep Date: ‘ + convert(
varchar(
30),
@vinterval_date)
35 select @min_refrdel_date=min(delete_date)
from refrdel
36 print ‘Oldest Refrdel: ‘ + convert(
varchar(
30),
@min_refrdel_date)
37 set @delete_target = @min_refrdel_date
38 if @min_refrdel_date is not null
39 begin
40 set @total_cleared=0
41 while (
@i <= @max_minutes_to_sweep and @delete_target < @vinterval_date )
42 begin
43 set @delete_target=dateadd(mi,
@interval_step,
@delete_target)
44 delete from refrdel
where delete_date
< @delete_target
45 set @total_cleared= @total_cleared +@@rowcount
46 set @i=@i+@interval_step
47 end
48 end
49 else
50 print ‘Nothing to Delete‘
51 set @pret_val =@total_cleared
52 set @pret_msg=‘Cleared: ‘ + ltrim(
str(
@pret_val))
+ ‘ records from ‘ + convert(
varchar(
30),
@min_refrdel_date)
+‘ to ‘ + convert(
varchar(
30),
@delete_target)
53 print @pret_msg
54 end try
55 Begin Catch
56 set @pret_val=error_number()
57 set @pret_msg=error_message()
58 End Catch
USE [EPPM] [dbo].[REFRDEL_CLEANUP]
标签: