165.作业使用案例

ゞ浴缸里的玫瑰 2024-02-19 19:16 7阅读 0赞

## 案例1 ##

/*--作业处理实例
    
    	根据sendTab的SendTime定制作业
    	并且在该作业完成时,可以自动删除作业
    
    --示例
    
    --测试表
    create table sendTab(ID int identity(1,1),Name varchar(10)
    	,SendTime datetime,AcceptUnit varchar(10)
    	,SendUnit varchar(10),Content varchar(8000))
    
    create table accepteTab(ID int identity(1,1),Name varchar(10)
    	,SendUnit varchar(10),AcceptUnit varchar(10),Content varchar(8000))
    go
    
    --创建处理的存储过程
    create proc p_JobSet
    @id int,			--要处理的sendTab的id
    @is_delete bit=0	--是否仅删除,为0则否,为1则是
    as
    declare @dbname sysname,@jobname sysname
    	,@date int,@time int
    
    select @jobname='定时发送作业_'+cast(@id as varchar)
    	,@date=convert(varchar,SendTime,112)
    	,@time=replace(convert(varchar,SendTime,108),':','')
    from sendTab where id=@id
    
    if exists(select 1 from msdb..sysjobs where name=@jobname)
    	exec msdb..sp_delete_job @job_name=@jobname 
    
    if @is_delete=1 return
    
    --创建作业
    exec msdb..sp_add_job @job_name=@jobname,@delete_level=1
    
    --创建作业步骤
    declare @sql varchar(800)
    select @sql='insert accepteTab(name,SendUnit,AcceptUnit,Content)
    		select name,AcceptUnit,SendUnit,Content from sendTab where id='
    		+cast(@id as varchar)
    	,@dbname=db_name()
    
    exec msdb..sp_add_jobstep @job_name=@jobname,
    	@step_name = '发送处理步骤',
    	@subsystem = 'TSQL',
    	@database_name=@dbname,
    	@command = @sql,
    	@retry_attempts = 5, 		--重试次数
    	@retry_interval = 5  		--重试间隔
    
    --创建调度
    EXEC msdb..sp_add_jobschedule @job_name = @jobname, 
    	@name = '时间安排',
    	@enabled = 1, 
    	@freq_type = 1, 
    	@active_start_date = @date,
    	@active_start_time = @time
    
    -- 添加目标服务器
    EXEC msdb.dbo.sp_add_jobserver 
    	@job_name = @jobname ,
    	@server_name = N'(local)' 
    go
    
    --创建处理的触发器(新增/修改)
    create trigger tr_insert_update on sendTab
    for insert,update
    as
    declare @id int
    declare tb cursor local for select id from inserted
    open tb
    fetch next from tb into @id
    while @@fetch_status=0
    begin
    	exec p_JobSet @id
    	fetch next from tb into @id
    end
    close tb
    deallocate tb
    go
    
    --创建处理的触发器(删除)
    create trigger tr_delete on sendTab
    for delete
    as
    declare @id int
    declare tb cursor local for select id from deleted
    open tb
    fetch next from tb into @id
    while @@fetch_status=0
    begin
    	exec p_JobSet @id,1
    	fetch next from tb into @id
    end
    close tb
    deallocate tb
    go
    
    --测试
    
    --插入数据
    insert sendTab
    select '文书1','2004/5/1 12:00:00','UnitA','UnitB','txt'
    union all select '文书2','2004/5/12 12:00:00','UnitA','UnitB','txt'
    union all select '文书3','2004/5/21 12:00:00','UnitA','UnitB','txt'
    
    --修改
    update sendTab set name='档案1',SendTime='2004/5/1 15:00:00'
    where id=1
    
    --删除
    delete sendtab where id=3
    go
    --删除测试
    drop table sendTab,accepteTab
    drop proc p_JobSet

## 案例2（创建定时调用存储过程的作业） ##

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_JobSet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    	drop procedure [dbo].[p_JobSet]
    GO
    
    /*--指定时间调用存储过程
    
    	创建一个在指定时间,调用指定存储过程的作业
    	作业执行完成后会自动删除
    
    /*--调用示例
    
    	declare @dt datetime
    	set @dt=dateadd(minute,1,getdate())	--当前时间1分钟后执行
    	exec p_JobSet 'master.dbo.xp_cmdshell ''dir c:\*.*''',@dt
    --*/
    create proc p_JobSet
    @prorcname sysname,	--要调用定时调用的存储过程名,如果不在当前库中,则用:库名.所有者名.存储过程名
    @job_date datetime	--存储过程的执行时间(包括时间信息)
    with encryption
    as
    declare @dbname sysname,@jobname sysname
    	,@date int,@time int
    
    select @jobname='临时作业_'+cast(newid() as varchar(36))
    	,@date=convert(varchar,@job_date,112)
    	,@time=replace(convert(varchar,@job_date,108),':','')
    
    if exists(select 1 from msdb..sysjobs where name=@jobname)
    	exec msdb..sp_delete_job @job_name=@jobname 
    
    --创建作业
    exec msdb..sp_add_job @job_name=@jobname,@delete_level=1
    
    --创建作业步骤
    declare @sql varchar(800)
    select @sql='exec '+@prorcname
    	,@dbname=db_name()
    
    exec msdb..sp_add_jobstep @job_name=@jobname,
    	@step_name = '处理步骤',
    	@subsystem = 'TSQL',
    	@database_name=@dbname,
    	@command = @sql,
    	@retry_attempts = 5, 		--重试次数
    	@retry_interval = 5  		--重试间隔
    
    --创建调度
    EXEC msdb..sp_add_jobschedule @job_name = @jobname, 
    	@name = '时间安排',
    	@enabled = 1, 
    	@freq_type = 1, 
    	@active_start_date = @date,
    	@active_start_time = @time
    
    -- 添加目标服务器
    EXEC msdb.dbo.sp_add_jobserver 
    	@job_name = @jobname ,
    	@server_name = N'(local)' 
    go