MySQL stored procedures and events

1. member table member and Vehicle table car, update the number of vehicles under each member Have_car field.

DELIMITER $$ Use $$DROP PROCEDURE IF EXISTS' Sp_update_member_have_car ' $$CREATEPROCEDURE' Sp_update_member_have_car ' ()BEGIN    DECLARETmpINT DEFAULT 0; DECLAREDoneINT DEFAULT -1; /*declaring Cursors*/      DECLAREMyCursorCURSOR  for SELECTReg_no fromMemberWHEREHave_car is NULL; /*when the cursor reaches the tail, MySQL automatically sets the done=1*/         DECLARE CONTINUEHANDLER for  notFOUNDSETDone=1; /*Open Cursor*/      OPENMyCursor; /*Cycle Start*/Myloop:loop/*move a cursor and assign a value*/          FETCHMyCursor intotmp; IFDone= 1  ThenLEAVE Myloop; END IF; /*Do something*/          UPDATEMemberSETHave_car=(SELECT COUNT(*) fromCarWHEREMem_no=TMP)WHEREReg_no=tmp; /*End of Cycle*/      ENDLOOP Myloop; /*Close Cursors*/      CLOSEMyCursor; END$ $DELIMITER;

Create an execution plan that executes the stored procedure 1 o'clock in the morning every day.

DELIMITER $$ ALTER  on 1  Day ' 2014-09-19 01:00:00 '  on BEGIN Call         ' Sp_update_member_have_car ' ();       END $ $DELIMITER;

MySQL stored procedures and events