Shulou(Shulou.com)06/01 Report--

This article is about the usefulness of mysql stored procedures, cursors, and transactions. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

Detailed explanation of stored procedures, cursors and transaction examples of mysql

The following is the mysql database stored procedure I have written, which is saved for archiving and used for reference in the future.

Among them, it involves stored procedures, cursors (double loops) and transactions.

[description]: the comments in the code are only for the business at that time and are ignored.

The code is as follows:

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''; / * identify whether the formal table has the same data, that is, groupId and email are the same * / DECLARE infoId VARCHAR (24) DEFAULT'; / * identify transaction error * / DECLARE err INT DEFAULT 0 / * submit when a certain number is reached. Counter * / DECLARE counts INT DEFAULT 0; / * identify whether rollback has been done * / DECLARE isrollback INT DEFAULT 0; / * cursor traversal time as a flag to determine whether all records have been traversed * / DECLARE done INTEGER DEFAULT 0; / * get temporary table data of this task * / DECLARE cur CURSOR FOR SELECT id,taskId,groupId,email FROM `tuploemailuploaddatatemp` WHERE taskId=jobId / * query whether the same record exists according to the group id and email * / DECLARE cur2 CURSOR FOR SELECT id FROM `taccounemailinfo`e WHERE e.`group _ id` = groupIdval AND e.`email _ address` = emailval; / * an error occurred, set to 1, and rollback whenever an exception occurs * / DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err=1; / * declare that the flag variable will be set to a value * / DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1 when the cursor has finished traversing all records / * Open transaction * / START TRANSACTION; / * Open cursor * / OPEN cur; / * Loop through using LOOP * / out_loop:LOOP / * assign the field value corresponding to each result to the variable * / FETCH cur INTO idval,taskIdval,groupIdval,emailval; IF done = 1 THEN LEAVE out_loop; END IF; / * Open the second cursor * / OPEN cur2; SET done = 0 FETCH cur2 INTO infoId; / * if the same groupId and email record does not exist in the official table, add it to the official table * / IF done = 1 THEN / * insert the official table * / INSERT INTO `taccounemailinfo` VALUES (idval,emailval,groupIdval,0,'',NOW (), 'admin',NOW (),' admin') / * Delete temporary data * / DELETE FROM `tSecretemailSecretdatatemp` WHERE id = idval; / * counter, only submit * / SET counts = counts + 1 for every 1000 messages; / * rollback * / IF err=1 THEN SET isrollback=1; ROLLBACK; ELSE IF counts = 1000 THEN COMMIT if an exception occurs / * when 1000 entries are submitted and the same record already exists in the reset counter * / SET counts=0; END IF; END IF; ELSE / *, delete the record * / IF done=0 THEN DELETE FROM `taccounemailuploaddatatemp` WHERE id = idval; END IF; END IF; FETCH cur2 INTO infoId; CLOSE cur2 / * Control the external loop, this step is indispensable, otherwise the loop will end only once * / SET done=0; END LOOP out_loop; CLOSE cur / * if no rollback event has occurred, update the task status * / / * if you rollback and do not update the task status, the remaining unsubmitted data will be added to the official table * / IF isrollback=0 THEN UPDATE `tuploemailtask`t SET t.`if _ finish` = 1 WHERE t.`id` = jobId; END IF; END$$DELIMITER the next time the task is executed. Thank you for reading! About mysql stored procedures, cursors, transactions have any use to share here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.