詳細講解mysql的存儲過程、游標 、事務

DELIMITER?$$ DROP?PROCEDURE?IF?EXISTS?`transferEmailTempData`$$  CREATE?PROCEDURE?transferEmailTempData(IN?jobId?VARCHAR(24)) BEGIN ??DECLARE?idval?VARCHAR(24)?DEFAULT?''; ??DECLARE?taskIdval?VARCHAR(24)?DEFAULT?''; ??DECLARE?groupIdval?VARCHAR(24)?DEFAULT?''; ??DECLARE?emailval?VARCHAR(50)?DEFAULT?''; ?? ??/*標識正式表是否存在一條相同數據，即：groupId、email相同*/ ??DECLARE?infoId?VARCHAR(24)?DEFAULT?''; ?? ??/*標識事務錯誤*/ ??DECLARE?err?INT?DEFAULT?0; ?? ??/*達到一定數量就進行提交，計數器*/ ??DECLARE?counts?INT?DEFAULT?0; ?? ??/*標識是否回滾過*/ ??DECLARE?isrollback?INT?DEFAULT?0; ?? ??/*游標遍歷時，作為判斷是否遍歷完全部記錄的標記*/ ??DECLARE?done?INTEGER?DEFAULT?0; ?? ??/*獲取臨時表該任務的數據*/ ??DECLARE?cur?CURSOR?FOR?SELECT?id,taskId,groupId,email?FROM?`t_email_data_temp`?WHERE?taskId=jobId; ?? ??/*根據群組id、email查詢是否存在相同記錄*/ ??DECLARE?cur2?CURSOR?FOR?SELECT?id?FROM?`t_email_info`?e?WHERE?e.`group_id`?=?groupIdval?AND?e.`email_address`?=?emailval;? ?? ??/*?出現錯誤，設置為1，只要發生異常就回滾*/ ??DECLARE?CONTINUE?HANDLER?FOR?SQLEXCEPTION?SET?err=1; ?? ??/*聲明當游標遍歷完全部記錄后將標志變量置成某個值*/ ??DECLARE?CONTINUE?HANDLER?FOR?NOT?FOUND ??SET?done=1; ?? ??/*開啟事務*/ ??START?TRANSACTION; ?? ??/*打開游標*/ ??OPEN?cur; ?? ??/*使用LOOP循環遍歷*/ ??out_loop:LOOP ?? ????/*將每一條結果對應的字段值賦值給變量*/ ????FETCH?cur?INTO?idval,taskIdval,groupIdval,emailval; ????IF?done?=?1?THEN ??????LEAVE?out_loop; ????END?IF; ???? ????/*打開第二個游標*/ ????OPEN?cur2; ??????SET?done?=?0; ??????FETCH?cur2?INTO?infoId; ?????? ??????/*如果正式表不存在相同groupId?and?email記錄，添加到正式表*/ ??????IF?done?=?1?THEN ?????? ????????/*插入正式表*/ ????????INSERT?INTO?`t_email_info`?VALUES(idval,emailval,groupIdval,0,'',NOW(),'admin',NOW(),'admin'); ???????? ????????/*刪除臨時數據*/ ????????DELETE?FROM?`t_email_data_temp`?WHERE?id?=?idval; ???????? ????????/*計數器，每1000條才提交*/ ????????SET?counts?=?counts?+?1; ???????? ????????/*發生異常，回滾*/ ????????IF?err=1?THEN ??????????SET?isrollback=1; ??????????ROLLBACK; ????????ELSE ??????????IF?counts?=?1000?THEN ????????????COMMIT; ????????????/*達到1000條提交后，重置計數器*/ ????????????SET?counts=0; ??????????END?IF; ????????END?IF; ??????ELSE ????????/*已經存在相同記錄，則刪除該記錄*/ ????????IF?done=0?THEN ??????????DELETE?FROM?`t_email_data_temp`?WHERE?id?=?idval; ????????END?IF; ??????END?IF; ??????FETCH?cur2?INTO?infoId; ????CLOSE?cur2; ???? ????/*控制外部的循環，該步驟不能缺少，否則只循環一次就結束了*/ ????SET?done=0; ???? ??END?LOOP?out_loop; ??CLOSE?cur; ?? ??/*如果沒有發生過回滾事件，則更新task狀態*/ ??/*如果回滾過，不更新task狀態，下次執行任務的時候，會再次將剩余沒有提交的數據進行添加到正式表*/ ??IF?isrollback=0?THEN ????UPDATE?`t_email_task`?t?SET?t.`if_finish`?=?1?WHERE?t.`id`=jobId; ??END?IF; ?? ??END$$  DELIMITER?;