MySql 中event/job/定时任务 游标 实战详解

这是我在一个项目中碰到的一个问题，客户有一个需求：
是需要在上午十一点钟自动去计算自己公司内部用户申领的物资，然后去对比仓库库存，如果申领数量大于仓库库存就生成一条采购订单交于采购部门经理审核。
思路：

1、存储过程的编写

//是否已经存在这个存储过程，如果存在就删除
DROP PROCEDURE IF EXISTS autoPurchase;
//windows系统下定义变量需要加
DELIMITER
//创建存储过程
CREATE PROCEDURE autoPurchase()
BEGIN  
	//定义参数
	//查询到的物资id
    DECLARE row_goodId INT;
    //查询到的申请物资总数量
    DECLARE row_shuliang INT;
    //仓库物资库存总数量
    DECLARE sumnum INT;
    //申领物资数量和库存的差值
    DECLARE num INT;
    //用来判断是否第一次进入方法体
    DECLARE _count INT;
    //为游标循环定义参数
    DECLARE ergodic INT DEFAULT 1;
    //用来保存生成的采购订单Id
    DECLARE row_purchaseId INT;
    //创建游标：cur。for后面的就是需要使用游标进行取值的内容。
    DECLARE cur CURSOR FOR SELECT goodsId,SUM(countnum) FROM usedetail WHERE useId IN (SELECT useid FROM usec WHERE examineStatus=1) GROUP BY goodsId;
    //如果游标没有下一行就将ergodic设置成0
    DECLARE EXIT HANDLER FOR NOT FOUND SET ergodic:=0;
    //初始化第一次库存不足进入生成采购订单的判断参数
    SET _count:=0;
    //打开游标
    OPEN cur;
    //使用while循环，ergodic=1 表示游标还有下一行，将进入循环
    WHILE ergodic=1 DO
    //将游标的取到的两个值设置到定义的参数里面。注意！定义的变量名不能和查询语句的列名同名！
    FETCH cur INTO row_goodId,row_shuliang;
    //通过从游标中取到的物资id作为条件去仓库中进行查询。如果仓库中不含有这一总物资。这个列就是null 所有将null转换成0赋值到sumnum中
    SELECT (CASE WHEN ISNULL(SUM(shuliang)) THEN 0 ELSE SUM(shuliang) END ) INTO sumnum FROM wu WHERE goodId=row_goodId;
    //求出申领物资的数量和仓库物资的数量的差值
    SET num=(sumnum-row_shuliang);
    //如果差值小于0就代表仓库的库存不足就会去生成采购订单
    IF num<0 THEN
    		//通过_count是否等于0来判断是否第一个物资存在缺货。
   			 IF _count=0 THEN
   			 		//生成一个采购的大订单。
					INSERT INTO purchase(filltime,fillperson,purchaseType,purchaseAuditState) VALUES(NOW(),0,0,1);
					//并且将刚刚生成的订单id存入设置的参数row_purchaseId中
					SELECT MAX(purchaseId) INTO row_purchaseId FROM purchase;
					END IF;
    	//生成一条采购订单的详情记录。并且将刚刚获取到的采购大id设置到采购详情订单中
	   INSERT INTO purchaseInfo(purchaseId,goodsId,num,userid,addnum) VALUES(row_purchaseId ,row_goodId,(-num),0,0);
	   COMMIT;
	   //完成一条数据的添加以后将_count+1 第二次进if代码块的时候就不会再去生成大的采购订单了。
	   SET _count=_count+1;
    END IF;
    //结束循环
    END WHILE;
    //关闭游标
    CLOSE cur;
END;

2. 创建event对存储过程进行调用

//创建一个event
CREATE EVENT `event_autopurchase` 
//计划任务标识
ON SCHEDULE 
//这里有两个标识  every和at  at表示只执行一次。every表示每过多久执行一次
EVERY 1 DAY 
//什么时候开始 后面也可以接Ends 设置结束时间
STARTS  TIMESTAMP '2019-10-10 11:00:00'
//可以不写这句话，不写默认是ON COMPLETION  NOT PRESERVE，就是在执行完以后自动删除这个event
ON COMPLETION PRESERVE
//do后面写代码块
DO
//执行存储过程
CALL autoPurchase()

//查看mysql的event是否开启
SELECT @@event_scheduler;

//如果是off就需要打开mysql的event
SET GLOBAL event_scheduler=ON;
//查看是否存在刚刚创建的event
SELECT * FROM mysql.event;

这个需求就这样完美的解决啦，如有什么问题麻烦在评论区留言我。