P_CalcPlan
来源:互联网 发布:点o是什么文件 linux 编辑:程序博客网 时间:2024/06/14 02:51
alter proc P_CalcPlan
(
@JobID int
)
/*
--计算Job执行周期
*/
as
begin
set nocount on;
declare @LastTime datetime,@cycle varchar(50),@CycleNum int,@Freq int,@RunGroupID int,@ns nvarchar(4000)
,@interval varchar(50),@interval_num int,@StartTime datetime,@StartTimeCur datetime,@EndTime datetime,@EndTimeCur datetime,@ID int;
select @cycle=
(case PlanCycleName
when '年' then 'yy'
when '季' then 'qq'
when '月' then 'mm'
when '周' then 'wk'
when '日' then 'dd'
when '时' then 'hh'
end )
,@interval=
(case PlanCycleName
when '年' then 'dd'
when '季' then 'dd'
when '月' then 'dd'
when '周' then 'dd'
when '日' then 'hh'
when '时' then 'mi'
end )
,@interval_num=
(case PlanCycleName
when '年' then isnull(PlanCycleNum,1)*365/(case when PlanFreqName='次' then PlanFreqNum else 1 end)
when '季' then isnull(PlanCycleNum,1)*90/(case when PlanFreqName='次' then PlanFreqNum else 1 end)
when '月' then isnull(PlanCycleNum,1)*30/(case when PlanFreqName='次' then PlanFreqNum else 1 end)
when '周' then isnull(PlanCycleNum,1)*7/(case when PlanFreqName='次' then PlanFreqNum else 1 end)
when '日' then isnull(PlanCycleNum,1)*24/(case when PlanFreqName='次' then PlanFreqNum else 1 end)
when '时' then isnull(PlanCycleNum,1)*60/(case when PlanFreqName='次' then PlanFreqNum else 1 end)
end )
,@CycleNum=isnull(PlanCycleNum,1)
,@Freq=(case when PlanFreqName='次' then PlanFreqNum else 1 end)
from Task_Plan
where JobID=@JobID ;
--每个执行组最大设为10个串行任务
set @RunGroupID=
isnull(
(select RunGroupID from (select max(RunGroupID)RunGroupID from Task_PlanCalc where JobID=@JobID) aa
where RunGroupID in (select RunGroupID from Task_PlanCalc group by RunGroupID having count(distinct JobID)<10))
,
(select isnull(max(RunGroupID),1) from Task_PlanCalc)
) ;
--定义表暂存但周期内本Job的所有执行信息
declare @t table (id int identity(1,1),RunGroupID int, JobID int , PlanRunTime datetime, PlanEndTime datetime, IsV bit);
--初始开始时间
select @StartTime=PlanStartTime from Task_Plan where JobID=@JobID;
--初始截止时间
select @EndTime=PlanEndTime from Task_Plan where JobID=@JobID;
--周期内的开始和结束时间
set @ns='
set @StartTimeCur=@StartTime;
set @EndTimeCur=dateadd('+@cycle+','+convert(varchar,@CycleNum)+',@StartTime);
while not(@StartTimeCur<=''2016-11-07'' and @EndTimeCur>''2016-11-07'')
begin
set @StartTimeCur=dateadd('+@cycle+','+convert(varchar,@CycleNum)+',@StartTimeCur);
set @EndTimeCur=dateadd('+@cycle+','+convert(varchar,@CycleNum)+',@EndTimeCur);
end
';
exec sp_executesql @ns,
N'@StartTime datetime,@EndTime datetime,@StartTimeCur datetime output,@EndTimeCur datetime output'
,@StartTime,@EndTime,@StartTimeCur output,@EndTimeCur output;
--修复结束时间
select @EndTimeCur=min(t) from (select @EndTime as t union all select @EndTimeCur as t) as aa;
set @LastTime=@StartTimeCur;
--将每次的信息写入
while @Freq>0 and @LastTime<@EndTimeCur
begin
--设置当前
insert into @t(RunGroupID, JobID, PlanRunTime, IsV)
select @RunGroupID, @JobID, @LastTime, 1;
select @ID=@@identity;
--设置上次
update @t set PlanEndTime=isnull(@LastTime,@EndTime) where RunGroupID=@RunGroupID and ID=@ID-1;
--条件更新
set @ns=N'select @LastTime=dateadd('+@interval+','+convert(varchar,@interval_num)+','''+convert(varchar,@LastTime)+''')
,@EndTime=dateadd('+@interval+','+convert(varchar,@interval_num+1)+','''+convert(varchar,@LastTime)+''') ';
exec sp_executesql @ns,N'@LastTime datetime output,@EndTime datetime output',@LastTime output,@EndTime output;
select @EndTime=min(t) from (select @EndTimeCur as t union all select @EndTime as t) as aa;
set @Freq=@Freq-1;
end
update @t set PlanEndTime=@EndTime where RunGroupID=@RunGroupID and ID=@ID;
merge into Task_PlanCalc m
using
(
select RunGroupID, @JobID as JobID, PlanRunTime, PlanEndTime from @t
) as s
on m.JobID=s.JobID and m.PlanRunTime=s.PlanRunTime
when matched
then
update set m.PlanEndTime=s.PlanEndTime,m.RunGroupID=s.RunGroupID
when not matched
then
insert(RunGroupID, JobID, PlanRunTime, PlanEndTime, IsV)
values(s.RunGroupID, s.JobID, s.PlanRunTime, s.PlanEndTime, 1)
when not matched by source and m.JobID=@JobID --限定JobID防止多删除
then delete;
end
(
@JobID int
)
/*
--计算Job执行周期
*/
as
begin
set nocount on;
declare @LastTime datetime,@cycle varchar(50),@CycleNum int,@Freq int,@RunGroupID int,@ns nvarchar(4000)
,@interval varchar(50),@interval_num int,@StartTime datetime,@StartTimeCur datetime,@EndTime datetime,@EndTimeCur datetime,@ID int;
select @cycle=
(case PlanCycleName
when '年' then 'yy'
when '季' then 'qq'
when '月' then 'mm'
when '周' then 'wk'
when '日' then 'dd'
when '时' then 'hh'
end )
,@interval=
(case PlanCycleName
when '年' then 'dd'
when '季' then 'dd'
when '月' then 'dd'
when '周' then 'dd'
when '日' then 'hh'
when '时' then 'mi'
end )
,@interval_num=
(case PlanCycleName
when '年' then isnull(PlanCycleNum,1)*365/(case when PlanFreqName='次' then PlanFreqNum else 1 end)
when '季' then isnull(PlanCycleNum,1)*90/(case when PlanFreqName='次' then PlanFreqNum else 1 end)
when '月' then isnull(PlanCycleNum,1)*30/(case when PlanFreqName='次' then PlanFreqNum else 1 end)
when '周' then isnull(PlanCycleNum,1)*7/(case when PlanFreqName='次' then PlanFreqNum else 1 end)
when '日' then isnull(PlanCycleNum,1)*24/(case when PlanFreqName='次' then PlanFreqNum else 1 end)
when '时' then isnull(PlanCycleNum,1)*60/(case when PlanFreqName='次' then PlanFreqNum else 1 end)
end )
,@CycleNum=isnull(PlanCycleNum,1)
,@Freq=(case when PlanFreqName='次' then PlanFreqNum else 1 end)
from Task_Plan
where JobID=@JobID ;
--每个执行组最大设为10个串行任务
set @RunGroupID=
isnull(
(select RunGroupID from (select max(RunGroupID)RunGroupID from Task_PlanCalc where JobID=@JobID) aa
where RunGroupID in (select RunGroupID from Task_PlanCalc group by RunGroupID having count(distinct JobID)<10))
,
(select isnull(max(RunGroupID),1) from Task_PlanCalc)
) ;
--定义表暂存但周期内本Job的所有执行信息
declare @t table (id int identity(1,1),RunGroupID int, JobID int , PlanRunTime datetime, PlanEndTime datetime, IsV bit);
--初始开始时间
select @StartTime=PlanStartTime from Task_Plan where JobID=@JobID;
--初始截止时间
select @EndTime=PlanEndTime from Task_Plan where JobID=@JobID;
--周期内的开始和结束时间
set @ns='
set @StartTimeCur=@StartTime;
set @EndTimeCur=dateadd('+@cycle+','+convert(varchar,@CycleNum)+',@StartTime);
while not(@StartTimeCur<=''2016-11-07'' and @EndTimeCur>''2016-11-07'')
begin
set @StartTimeCur=dateadd('+@cycle+','+convert(varchar,@CycleNum)+',@StartTimeCur);
set @EndTimeCur=dateadd('+@cycle+','+convert(varchar,@CycleNum)+',@EndTimeCur);
end
';
exec sp_executesql @ns,
N'@StartTime datetime,@EndTime datetime,@StartTimeCur datetime output,@EndTimeCur datetime output'
,@StartTime,@EndTime,@StartTimeCur output,@EndTimeCur output;
--修复结束时间
select @EndTimeCur=min(t) from (select @EndTime as t union all select @EndTimeCur as t) as aa;
set @LastTime=@StartTimeCur;
--将每次的信息写入
while @Freq>0 and @LastTime<@EndTimeCur
begin
--设置当前
insert into @t(RunGroupID, JobID, PlanRunTime, IsV)
select @RunGroupID, @JobID, @LastTime, 1;
select @ID=@@identity;
--设置上次
update @t set PlanEndTime=isnull(@LastTime,@EndTime) where RunGroupID=@RunGroupID and ID=@ID-1;
--条件更新
set @ns=N'select @LastTime=dateadd('+@interval+','+convert(varchar,@interval_num)+','''+convert(varchar,@LastTime)+''')
,@EndTime=dateadd('+@interval+','+convert(varchar,@interval_num+1)+','''+convert(varchar,@LastTime)+''') ';
exec sp_executesql @ns,N'@LastTime datetime output,@EndTime datetime output',@LastTime output,@EndTime output;
select @EndTime=min(t) from (select @EndTimeCur as t union all select @EndTime as t) as aa;
set @Freq=@Freq-1;
end
update @t set PlanEndTime=@EndTime where RunGroupID=@RunGroupID and ID=@ID;
merge into Task_PlanCalc m
using
(
select RunGroupID, @JobID as JobID, PlanRunTime, PlanEndTime from @t
) as s
on m.JobID=s.JobID and m.PlanRunTime=s.PlanRunTime
when matched
then
update set m.PlanEndTime=s.PlanEndTime,m.RunGroupID=s.RunGroupID
when not matched
then
insert(RunGroupID, JobID, PlanRunTime, PlanEndTime, IsV)
values(s.RunGroupID, s.JobID, s.PlanRunTime, s.PlanEndTime, 1)
when not matched by source and m.JobID=@JobID --限定JobID防止多删除
then delete;
end
0 0