Detailed explanation of MySQL's stored procedures, cursors, and transactions

This article mainly introduces relevant information about MySQL's stored procedures, cursors, and transaction examples. Here are examples to illustrate MySQL stored procedures, cursors, and transactions. Friends who need it can refer to it. I hope it can help. Everyone.

Recommended related mysql video tutorials: "mysql tutorial"

Detailed explanation of mysql's stored procedures, cursors, and transaction instances

The following is a mysql database stored procedure that I have written. I keep it as an archive for reference when using it in the future.

Among them, stored procedures, cursors (double-layer loops), and transactions are involved.

[Note]: The comments in the code are only for the business at that time and do not need to be 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 '';
  
  /*标识正式表是否存在一条相同数据，即：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 ;

Related recommendations:

A brief introduction to MySQL stored procedures and transactions

Stored procedures Definition, modification and deletion operation methods

MySQL stored procedure Cursor error handling sample code

The above is the detailed content of Detailed explanation of MySQL's stored procedures, cursors, and transactions. For more information, please follow other related articles on the PHP Chinese website!