添加事件调用存储过程

一、demo脚本

DROP PROCEDURE IF EXISTS `ldcrm_reset_billapprovinfo`;
delimiter ||
CREATE  PROCEDURE `ldcrm_reset_billapprovinfo`(
    tmp_tablename VARCHAR(100),
    tmp_tablekey VARCHAR(100),
    tmp_datetime datetime
)
BEGIN

    SET @this_billapprovinfo_01="UPDATE tmp_tablename a
        SET a.approvestatus=(
            CASE
                WHEN NOT EXISTS (SELECT 1 FROM ld_approvelog WHERE crmid=a.salesorderid) THEN '未提交'
                ELSE
                    CASE
                        WHEN (SELECT approveresult FROM ld_approvelog WHERE crmid=a.salesorderid ORDER BY id DESC LIMIT 1)=2 THEN '已批准'
                        WHEN (SELECT approveresult FROM ld_approvelog WHERE crmid=a.salesorderid ORDER BY id DESC LIMIT 1)=3 THEN '已拒绝'
                        WHEN (SELECT approveresult FROM ld_approvelog WHERE crmid=a.salesorderid ORDER BY id DESC LIMIT 1)=4 THEN '已驳回'
                        WHEN (SELECT approveresult FROM ld_approvelog WHERE crmid=a.salesorderid ORDER BY id DESC LIMIT 1)=1 THEN '审批中'
                        WHEN (SELECT approveresult FROM ld_approvelog WHERE crmid=a.salesorderid ORDER BY id DESC LIMIT 1)=10
                            AND (SELECT nextstepapproveby FROM ld_approvelog WHERE crmid=a.salesorderid ORDER BY id DESC LIMIT 1)=0 THEN '已批准'
                        WHEN (SELECT approveresult FROM ld_approvelog WHERE crmid=a.salesorderid ORDER BY id DESC LIMIT 1)=10
                            AND (SELECT nextstepapproveby FROM ld_approvelog WHERE crmid=a.salesorderid ORDER BY id DESC LIMIT 1)=-1 THEN '审批中'
                    END
            END)
        WHERE a.deleted=0
            AND IFNULL(a.approvestatus,'')<>IFNULL((
            CASE
                WHEN NOT EXISTS (SELECT 1 FROM ld_approvelog WHERE crmid=a.salesorderid) THEN '未提交'
                WHEN EXISTS (SELECT 1 FROM ld_approvelog WHERE crmid=a.salesorderid AND isfinished='no') THEN '审批中'
                ELSE
                    CASE
                        WHEN (SELECT approveresult FROM ld_approvelog WHERE crmid=a.salesorderid ORDER BY id DESC LIMIT 1)=2 THEN '已批准'
                        WHEN (SELECT approveresult FROM ld_approvelog WHERE crmid=a.salesorderid ORDER BY id DESC LIMIT 1)=3 THEN '已拒绝'
                        WHEN (SELECT approveresult FROM ld_approvelog WHERE crmid=a.salesorderid ORDER BY id DESC LIMIT 1)=4 THEN '已驳回'
                        WHEN (SELECT approveresult FROM ld_approvelog WHERE crmid=a.salesorderid ORDER BY id DESC LIMIT 1)=1 THEN '审批中'
                        WHEN (SELECT approveresult FROM ld_approvelog WHERE crmid=a.salesorderid ORDER BY id DESC LIMIT 1)=10
                            AND (SELECT nextstepapproveby FROM ld_approvelog WHERE crmid=a.salesorderid ORDER BY id DESC LIMIT 1)=0 THEN '已批准'
                        WHEN (SELECT approveresult FROM ld_approvelog WHERE crmid=a.salesorderid ORDER BY id DESC LIMIT 1)=10
                            AND (SELECT nextstepapproveby FROM ld_approvelog WHERE crmid=a.salesorderid ORDER BY id DESC LIMIT 1)=-1 THEN '审批中'
                    END
            END),'')
            AND (
                CASE
                    WHEN 'tmp_datetime'>'1900-01-01' THEN a.createdtime>='tmp_datetime'
                    ELSE 1=1
                END)";
            

    SET @this_billapprovinfo_01=REPLACE(@this_billapprovinfo_01,'tmp_tablename',tmp_tablename);
    SET @this_billapprovinfo_01=REPLACE(@this_billapprovinfo_01,'tmp_tablekey',tmp_tablekey);
    SET @this_billapprovinfo_01=REPLACE(@this_billapprovinfo_01,'tmp_datetime',IFNULL(tmp_datetime,''));

    SELECT @this_billapprovinfo_01;
    

    SET @disable_triggers=1;
    PREPARE this_billapprovinfo_stmt FROM @this_billapprovinfo_01;
    EXECUTE this_billapprovinfo_stmt;
    DEALLOCATE PREPARE this_billapprovinfo_stmt;
    SET @disable_triggers=NULL;

end ||
delimiter;

CALL ldcrm_reset_billapprovinfo('ld_cashbill','cashbillid','');

SET GLOBAL event_scheduler=ON;
DROP EVENT IF EXISTS `ldcrm_reset_billapprovinfo_every_second`;
delimiter ||
CREATE  EVENT `ldcrm_reset_billapprovinfo_every_second` ON SCHEDULE EVERY 60 SECOND STARTS '2021-03-05 15:00:00' ON COMPLETION NOT PRESERVE ENABLE DO 
    CALL ldcrm_reset_billapprovinfo('ld_cashbill','cashbillid',DATE_SUB(NOW(),INTERVAL 70 SECOND));
||
delimiter;

 

DROP PROCEDURE IF EXISTS `hager_reset_quotes_status`;
delimiter ||
CREATE  PROCEDURE `hager_reset_quotes_status`(
)
BEGIN

    SET @disable_triggers='aaaa';
    UPDATE ld_quotes SET available_status='失效' WHERE deleted=0 AND approvestatus='已批准' AND validtill BETWEEN '2000-01-01' AND CURDATE();
    SET @disable_triggers=NULL;

end ||
delimiter;

DROP EVENT IF EXISTS `hager_reset_quotes_status_everyday`;
delimiter ||
CREATE  EVENT `hager_reset_quotes_status_everyday` ON SCHEDULE EVERY 1 DAY STARTS '2021-10-09 00:00:01' ON COMPLETION NOT PRESERVE ENABLE DO 
CALL hager_reset_quotes_status();
||
delimiter;

添加事件调用存储过程.sql