利用游标+事务批量更新邮件任务预警

利用游标+事务批量更新邮件任务预警
https://david.blog.csdn.net/article/details/108396869

背景
大部分企业一般都有自研或通过系统配置邮件预警系统。涉及到邮件接受人，抄送人，密送人。
存在以下两种情况给运维带来一点压力；

如果预警任务比较多，人员变动比较频繁的时候。手动作业显得效率低下。
企业邮件地址批量变更
传统方式
通过写一个脚本，批量更新。针对单个地址变更OK，如果批量效率也显得比较低

改进作业
总体思路：
(1)设计变更表([EmailUpdate])：[OldEmail],[NewEmail]
(2)制作Web页面支持Excel导入与更新
(3)更新脚本

流程图
开始
上传待更新地址
确认批量更新？
结束
yes
no
SQL脚本：

USE [OA]
GO
/****** Object:  StoredProcedure [dbo].[DZ_EmailUpdate]    Script Date: 2020/09/04 8:13:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================   
-- Author: <David Gong>   
-- Create date: <2020-09-10>   
-- Description: <替换邮件>   
-- ============================================= 
ALTER Procedure [dbo].[DZ_EmailUpdate]
AS 
BEGIN

SET NOCOUNT ON
DECLARE    @result int  --成功 1; 失败 0
DECLARE   @message nvarchar(20) 

DECLARE @Email_Old VARCHAR(50), @Email_New VARCHAR(50)
DECLARE CREmailChange CURSOR FOR SELECT OldEmail,NewEmail FROM EmailUpdate
OPEN CREmailChange  /* 打开游标 */ 
FETCH NEXT FROM CREmailChange INTO @Email_Old, @Email_New
WHILE @@FETCH_STATUS = 0   /* 用WHILE循环控制游标活动*/ 
BEGIN
    --更新邮件预警任务列表中的接收人&CC&BCC
    ---SQL执行计时
    DECLARE @dateStart DATETIME
    DECLARE @dateEnd DATETIME
    DECLARE @Sencond AS INT
    SELECT @dateStart=getdate()
    --测试语句
    WAITFOR DELAY  '00:00:005';

    BEGIN TRY 
    BEGIN TRAN T_Update    --事务开始
        
        IF EXISTS(SELECT COUNT(*) FROM MailTask WHERE  receiver like '%'+@Email_Old+'%')
        BEGIN
            UPDATE MailTask
            SET receiver=REPLACE(receiver,@Email_Old,@Email_New)
            WHERE receiver like '%'+@Email_Old+'%'
        END
        IF EXISTS(SELECT COUNT(*) FROM MailTask WHERE  cc like '%'+@Email_Old+'%')
        BEGIN            
            UPDATE MailTask
            SET cc=REPLACE(receiver,@Email_Old,@Email_New)
                WHERE cc like '%'+@Email_Old+'%'
        END
        IF EXISTS(SELECT COUNT(*) FROM MailTask WHERE  bcc like '%'+@Email_Old+'%')
        BEGIN            
            UPDATE MailTask
            SET bcc=REPLACE(receiver,@Email_Old,@Email_New)
            WHERE bcc like '%'+@Email_Old+'%'
        END
    SET @result=1
    COMMIT TRAN T_Update    --事务提交        
    END TRY   
    BEGIN CATCH
            SET @message= ERROR_MESSAGE() 
            SET @result=0
            ROLLBACK TRAN T_Update    --由于出错，这里回滚到开始    
            print ERROR_MESSAGE()
    END CATCH 
    
    SELECT @dateEnd=getdate()
    SELECT @Sencond=DATEDIFF(millisecond, @dateStart, @dateEnd) 
    --结果是毫秒数

    PRINT CAST(@dateStart AS CHAR)+'开始'+@Email_Old+'替换'
    PRINT CAST(@dateEnd AS CHAR)+'完成'+@Email_New+'替换'+'计：'+RTRIM(CAST(@Sencond AS CHAR))+'毫秒'
    

    FETCH NEXT FROM CREmailChange INTO @Email_Old, @Email_New /* 在循环体内将读取其余行数据 */
END
CLOSE CREmailChange   /* 关闭游标 */ 
DEALLOCATE CREmailChange   /* 删除游标 */ 

SET NOCOUNT OFF;  

--SELECT * FROM dbo.MailTask 
--WHERE receiver LIKE '%it-2%'
--SELECT OldEmail,NewEmail FROM EmailUpdate

END