mysql的存儲(chǔ)過(guò)程、游標(biāo) 、事務(wù)實(shí)例詳解
下面是自己曾經(jīng)編寫(xiě)過(guò)的mysql數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程，留作存檔，以后用到的時(shí)候拿來(lái)參考。
其中，涉及到了存儲(chǔ)過(guò)程、游標(biāo)（雙層循環(huán)）、事務(wù)。
【說(shuō)明】：代碼中的注釋只針對(duì)當(dāng)時(shí)業(yè)務(wù)而言，無(wú)須理會(huì)。
代碼如下：
| 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 ''; /*標(biāo)識(shí)正式表是否存在一條相同數(shù)據(jù)，即：groupId、email相同*/ DECLARE infoId VARCHAR(24) DEFAULT ''; /*標(biāo)識(shí)事務(wù)錯(cuò)誤*/ DECLARE err INT DEFAULT 0; /*達(dá)到一定數(shù)量就進(jìn)行提交，計(jì)數(shù)器*/ DECLARE counts INT DEFAULT 0; /*標(biāo)識(shí)是否回滾過(guò)*/ DECLARE isrollback INT DEFAULT 0; /*游標(biāo)遍歷時(shí)，作為判斷是否遍歷完全部記錄的標(biāo)記*/ DECLARE done INTEGER DEFAULT 0; /*獲取臨時(shí)表該任務(wù)的數(shù)據(jù)*/ DECLARE cur CURSOR FOR SELECT id,taskId,groupId,email FROM `t_email_data_temp` WHERE taskId=jobId; /*根據(jù)群組id、email查詢是否存在相同記錄*/ DECLARE cur2 CURSOR FOR SELECT id FROM `t_email_info` e WHERE e.`group_id` = groupIdval AND e.`email_address` = emailval; /* 出現(xiàn)錯(cuò)誤，設(shè)置為1，只要發(fā)生異常就回滾*/ DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err=1; /*聲明當(dāng)游標(biāo)遍歷完全部記錄后將標(biāo)志變量置成某個(gè)值*/ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; /*開(kāi)啟事務(wù)*/ START TRANSACTION; /*打開(kāi)游標(biāo)*/ OPEN cur; /*使用LOOP循環(huán)遍歷*/ out_loop:LOOP /*將每一條結(jié)果對(duì)應(yīng)的字段值賦值給變量*/ FETCH cur INTO idval,taskIdval,groupIdval,emailval; IF done = 1 THEN LEAVE out_loop; END IF; /*打開(kāi)第二個(gè)游標(biāo)*/ 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'); /*刪除臨時(shí)數(shù)據(jù)*/ DELETE FROM `t_email_data_temp` WHERE id = idval; /*計(jì)數(shù)器，每1000條才提交*/ SET counts = counts + 1; /*發(fā)生異常，回滾*/ IF err=1 THEN SET isrollback=1; ROLLBACK; ELSE IF counts = 1000 THEN COMMIT; /*達(dá)到1000條提交后，重置計(jì)數(shù)器*/ SET counts=0; END IF; END IF; ELSE /*已經(jīng)存在相同記錄，則刪除該記錄*/ IF done=0 THEN DELETE FROM `t_email_data_temp` WHERE id = idval; END IF; END IF; FETCH cur2 INTO infoId; CLOSE cur2; /*控制外部的循環(huán)，該步驟不能缺少，否則只循環(huán)一次就結(jié)束了*/ SET done=0; END LOOP out_loop; CLOSE cur; /*如果沒(méi)有發(fā)生過(guò)回滾事件，則更新task狀態(tài)*/ /*如果回滾過(guò)，不更新task狀態(tài)，下次執(zhí)行任務(wù)的時(shí)候，會(huì)再次將剩余沒(méi)有提交的數(shù)據(jù)進(jìn)行添加到正式表*/ IF isrollback=0 THEN UPDATE `t_email_task` t SET t.`if_finish` = 1 WHERE t.`id`=jobId; END IF; END$$DELIMITER ; |
