mysql預存程序和事件

標籤：style   blog   color   io   os   ar   for   2014   div   

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預存程序和事件