Detailed Explanation of Stored Procedures Cursors and Transaction Instances of mysql

Detailed Explanation of Stored Procedures, Cursors and Transaction Instances of mysql

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

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

[Note]: The comments in the code are only for the business at that time and need not be paid attention to.

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 a formal table exists 1 Items of the same data, namely: groupId , email Same */
  DECLARE infoId VARCHAR(24) DEFAULT '';
  
  /* Identify transaction errors */
  DECLARE err INT DEFAULT 0;
  
  /* Attain 1 Submit on a fixed quantity, counter */
  DECLARE counts INT DEFAULT 0;
  
  /* Identify whether it was rolled back */
  DECLARE isrollback INT DEFAULT 0;
  
  /* The cursor traversal time is used as a mark to judge whether to traverse the whole record */
  DECLARE done INTEGER DEFAULT 0;
  
  /* Get the data of this task in the temporary table */
  DECLARE cur CURSOR FOR SELECT id,taskId,groupId,email FROM `t_email_data_temp` WHERE taskId=jobId;
  
  /* According to the group id , email Query whether the same record exists */
  DECLARE cur2 CURSOR FOR SELECT id FROM `t_email_info` e WHERE e.`group_id` = groupIdval AND e.`email_address` = emailval; 
  
  /*  An error occurred, set to 1 To roll back whenever an exception occurs */
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err=1;
  
  /* Declares that the flag variable is set to a certain value after the cursor traverses the entire record */
  DECLARE CONTINUE HANDLER FOR NOT FOUND
  SET done=1;
  
  /* Open a transaction */
  START TRANSACTION;
  
  /* Open cursor */
  OPEN cur;
  
  /* Use LOOP Cyclic traversal */
  out_loop:LOOP
  
    /* Will each 1 The field value corresponding to the result of the bar is assigned to the variable */
    FETCH cur INTO idval,taskIdval,groupIdval,emailval;
    IF done = 1 THEN
      LEAVE out_loop;
    END IF;
    
    /* Open the 2 Cursor */
    OPEN cur2;
      SET done = 0;
      FETCH cur2 INTO infoId;
      
      /* If the formal table does not exist the same groupId and email Record, add to official table */
      IF done = 1 THEN
      
        /* Insert a 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, every 1000 Article is submitted */
        SET counts = counts + 1;
        
        /* Exception occurred, rollback */
        IF err=1 THEN
          SET isrollback=1;
          ROLLBACK;
        ELSE
          IF counts = 1000 THEN
            COMMIT;
            /* Attain 1000 Reset the counter after the bar is submitted */
            SET counts=0;
          END IF;
        END IF;
      ELSE
        /* If the same record already exists, delete the record */
        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 only loop 1 The second time is over */
    SET done=0;
    
  END LOOP out_loop;
  CLOSE cur;
  
  /* If no rollback event has occurred, update task Status */
  /* If rolled back, do not update task Status, the next time the task is executed, the remaining uncommitted data will be added to the official table again */
  IF isrollback=0 THEN
    UPDATE `t_email_task` t SET t.`if_finish` = 1 WHERE t.`id`=jobId;
  END IF;
  
  END$$

DELIMITER ;

The above is mysql stored procedures, cursors, transactions, if you have any questions, please leave a message or to the community exchange discussion, thank you for reading, hope to help everyone, thank you for your support!