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

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 there is an identical data in the formal table, i.e., groupId and email are the same*/
  DECLARE infoId VARCHAR(24) DEFAULT '';
  
  /*Transaction error*/
  DECLARE err INT DEFAULT 0;
  
  /*Submit when a certain number is reached, counter*/
  DECLARE counts INT DEFAULT 0;
  
  /*Identify whether it has been rolled back*/
  DECLARE isrollback INT DEFAULT 0;
  
  /*When the cursor is traversing, it is used as a mark to determine whether all records have been traversed*/
  DECLARE done INTEGER DEFAULT 0;
  
  /*Get the data of the task in the temporary table*/
  DECLARE cur CURSOR FOR SELECT id,taskId,groupId,email FROM `t_email_data_temp` WHERE taskId=jobId;
  
  /*Query whether there are identical records based on group id and email*/
  DECLARE cur2 CURSOR FOR SELECT id FROM `t_email_info` e WHERE e.`group_id` = groupIdval AND e.`email_address` = emailval; 
  
  /* Error occurs, set to 1, rollback as long as an exception occurs*/
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err=1;
  
  /*Declare that when the cursor traverses all records, the flag variable will be set to a certain value*/
  DECLARE CONTINUE HANDLER FOR NOT FOUND
  SET done=1;
  
  /*Start transaction*/
  START TRANSACTION;
  
  /*Open cursor*/
  OPEN cur;
  
  /*Use LOOP to loop through*/
  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 official table does not have the same groupId and email record, add it to the official table*/
      IF done = 1 THEN
      
        /*Insert into the formal table*/
        INSERT INTO `t_email_info` VALUES(idval,emailval,groupIdval,0,'',NOW(),'admin',NOW(),'admin');
        
        /*Delete temporary data*/
        DELETE FROM `t_email_data_temp` WHERE id = idval;
        
        /*Counter, submit every 1000 items*/
        SET counts = counts + 1;
        
        /*Exception occurred, rollback*/
        IF err=1 THEN
          SET isrollback=1;
          ROLLBACK;
        ELSE
          IF counts = 1000 THEN
            COMMIT;
            /*After reaching 1000 submissions, reset the counter*/
            SET counts=0;
          END IF;
        END IF;
      ELSE
        /*If the same record already exists, delete it*/
        IF done=0 THEN
          DELETE FROM `t_email_data_temp` WHERE id = idval;
        END IF;
      END IF;
      FETCH cur2 INTO infoId;
    CLOSE cur2;
    
    /*Control the external loop. This step cannot be missing, otherwise it will end after only one loop*/
    SET done=0;
    
  END LOOP out_loop;
  CLOSE cur;
  
  /*If no rollback event has occurred, update the task status*/
  /*If the task is rolled back and the task status is not updated, the remaining uncommitted data will be added to the formal table again when the task is executed next time*/
  IF isrollback=0 THEN
    UPDATE `t_email_task` t SET t.`if_finish` = 1 WHERE t.`id`=jobId;
  END IF;
  
  END$$

DELIMITER ;