mysql存储过程和事件

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 ;

mysql存储过程和事件

推荐阅读