1.会员表member和车辆表car,更新每个会员下面的车辆数量have_car字段。
DELIMITER $$ USE $$ DROP PROCEDURE IF EXISTS `sp_update_member_have_car`$$ CREATEPROCEDURE `sp_update_member_have_car`() BEGIN DECLARE tmp INT DEFAULT 0; DECLARE done INT DEFAULT -1; /* 声明游标 */ DECLARE myCursor CURSOR FOR SELECT reg_no FROM member WHERE have_car IS NULL; /* 当游标到达尾部时,mysql自动设置done=1 */ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; /* 打开游标 */ OPEN myCursor; /* 循环开始 */ myLoop: LOOP /* 移动游标并赋值 */ FETCH myCursor INTO tmp; IF done = 1 THEN LEAVE myLoop; END IF; /* do something */ UPDATE member SET have_car = (SELECT COUNT(*) FROM car WHERE mem_no= tmp ) WHERE reg_no = tmp; /* 循环结束 */ END LOOP myLoop; /* 关闭游标 */ CLOSE myCursor; END$$ DELIMITER ;
建立执行计划,每天凌晨1点执行存储过程。
DELIMITER $$ ALTER EVENT `E_Update_HaveCar_Field_Event_1` ON SCHEDULE EVERY 1 DAY STARTS ‘2014-09-19 01:00:00‘ ON COMPLETION PRESERVE ENABLE DO BEGIN CALL `sp_update_member_have_car`(); END$$ DELIMITER ;