MySql 游标 事务

注意：变量的声明、游标的声明和HANDLER声明的顺序不能搞错，必须是先声明变量，再申明游标，最后声明HANDLER。在遍历第二个游标前使用了set done = 0，因为当第一个游标遍历玩后其值被handler设置为1了，如果不用set把它设置为 0 ，那么第二个游标就不会遍历了。使用两个或者更多游标的简单粒子如下：

drop procedure IF EXISTS test_proc_1;
delimiter //
create procedure test_proc_1()
begin
	DECLARE done INT DEFAULT 0;  #自定义控制游标循环变量,默认false  
	DECLARE tid int(11) DEFAULT 0;
	DECLARE tname varchar(50) DEFAULT NULL;
	DECLARE tpass varchar(50) DEFAULT NULL;

    #定义游标并输入结果集  
	DECLARE cur_1 CURSOR FOR
		select name, password from netingcn_proc_test;

	DECLARE cur_2 CURSOR FOR
		select id, name from netingcn_proc_test;

    #绑定控制变量到游标,游标循环结束自动转true  
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

	open cur_1;
	REPEAT
		FETCH cur_1 INTO tname, tpass; #将游标当前读取行的数据顺序赋予自定义变量
		if not done then
			select tname, tpass;
		end if;
 	UNTIL done END REPEAT;
	CLOSE cur_1;

	-- 注意这里，一定要重置done的值为 0
	set done = 0;

	open cur_2;
	REPEAT
		FETCH cur_2 INTO tid, tname;
		if not done then
			select tid, tname;
		end if;
 	UNTIL done END REPEAT;
	CLOSE cur_2;
end
//
delimiter ;

call test_proc_1();

实际应用中的粒子：

DELIMITER $$

USE `productdb`$$

DROP PROCEDURE IF EXISTS initcommission$$

CREATE PROCEDURE initcommission()                
BEGIN
		
	DECLARE done INT DEFAULT 0;  /*用于判断是否结束循环*/	
	DECLARE has_err INT DEFAULT 0;
	DECLARE p_FK_Product  INT; 
	DECLARE p_StartDate DATE ; 
	DECLARE p_EndDate   DATE ; 
	DECLARE p_projectcommissionId  INT; #新生成的项目ID
	DECLARE p_OldprojectcommissionId  INT; #历史项目ID
	DECLARE p_date   DATE ;  #循环添加项目的时间
	
	
	#第一个游标
	DECLARE idCur CURSOR FOR 
		SELECT `FK_Product`,`StartDate`,`EndDate` #,`CreateTime`,`CreateUser`
		FROM productdb.`projectcommission_bak` c WHERE c.isvalid='T' AND c.`StartDate` IS NOT NULL AND c.`EndDate` IS NOT NULL AND c.`StartDate`!= c.`EndDate`
		AND c.id  IN (22); #FISH条件去掉

	#第二个游表
	DECLARE rebateCur CURSOR FOR 
		SELECT rd.FK_ProjectCommission, rd.IntoTimeGuide
		FROM `financedb`.`shoppingrebatedetail_bak`  rd 
		LEFT JOIN productdb.`projectcommission_bak` c ON rd.`FK_ProjectCommission` = c.`ID`
		WHERE rd.`FK_ProjectCommission` IS NOT NULL AND rd.`IntoTimeGuide` IS NOT NULL
		AND (rd.`IntoTimeGuide`<c.`StartDate`  OR rd.`IntoTimeGuide`>c.`EndDate`)
		AND rd.`FK_ProjectCommission` IN (22);
	
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; /*done = true;亦可*/
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_err = 1;
	
    #事务
	START TRANSACTION;
	
	#将项目上的币种初始化到商店上
		
		UPDATE productdb.`productshop` s
		RIGHT JOIN productdb.projectcommission_bak c ON s.`FK_Product` = c.`FK_Product`
		SET s.`FK_Currency`=c.`FK_Currency` 
		WHERE c.`IsValid`='T' AND c.`FK_Currency` IS NOT NULL AND s.`FK_Currency` IS NULL
		AND c.id  IN (22); #FISH条件去掉
			
	
	OPEN idCur;  /*打开游标*/
	
	REPEAT
	FETCH idCur INTO p_FK_Product,p_StartDate,p_EndDate ; 
	
	IF done<>1 AND has_err=0 THEN  /*数值为非0，MySQL认为是true*/
	
		SET @p_createtime='2019-05-26 00:00:00';
		
		#初始化项目、项目明细信息
		SET p_date = p_StartDate;
	
		WHILE p_date<=p_EndDate DO
		
			#SELECT  p_date;
			INSERT INTO productdb.`projectcommissionNew`(`FK_Product`,`NAME`,`StartDate`,`EndDate`,`IsValid`,`CreateTime`,`CreateUser`,IsSummation,`MODE`,`TYPE`)			
			SELECT p_FK_Product,`NAME`,p_date,p_date,`IsValid`,@p_createtime,-1,'F',0,0
			FROM productdb.`projectcommission_bak`
			WHERE IsValid='T' AND FK_Product=p_FK_Product AND StartDate=p_StartDate AND EndDate=p_EndDate;
			SELECT LAST_INSERT_ID() INTO p_projectcommissionId;
			
			#项目明细表
			INSERT INTO productdb.`projectcommissiondetail` (`FK_Projectcommission`,`Start`,`End`,`ReturnPoint`,`IsValid`,`CreateTime`,`CreateUser`,`Sort`)
			SELECT p_projectcommissionId,0,NULL,`Percent`,'T',@p_createtime,-1,1
			FROM productdb.`projectcommission_bak`	
			WHERE IsValid='T' AND FK_Product=p_FK_Product AND StartDate=p_StartDate AND EndDate=p_EndDate;

			#项目历史数据映射表
			INSERT INTO productdb.`rebatemapping` (`OldProjectcommission`,`NewProjectcommission`,`UseDate`,`CreateTime`)
			SELECT `ID`,p_projectcommissionId,p_date,NOW()
			FROM productdb.`projectcommission_bak`
			WHERE IsValid='T' AND FK_Product=p_FK_Product AND StartDate=p_StartDate AND EndDate=p_EndDate;
			
			SET p_date=DATE_ADD(p_date,INTERVAL 1 DAY);
			
		END WHILE;
		
        END IF;
        UNTIL done=1 END REPEAT;
        
        CLOSE idCur;  /*关闭游标*/
        
        SET done = 0; #重置done的值为 0
        
	OPEN rebateCur;  
	
	REPEAT
	FETCH rebateCur INTO p_OldprojectcommissionId,p_date; 
	
	IF done<>1 AND has_err=0 THEN  /*数值为非0，MySQL认为是true*/
	
		SET @p_createtime='2019-05-26 00:00:00';
			
			INSERT INTO productdb.`projectcommissionNew`(`FK_Product`,`NAME`,`StartDate`,`EndDate`,`IsValid`,`CreateTime`,`CreateUser`,IsSummation,`MODE`,`TYPE`)			
			SELECT p_FK_Product,`NAME`,p_date,p_date,'T',@p_createtime,-1,'F',0,0
			FROM productdb.`projectcommission_bak`
			WHERE ID = p_OldprojectcommissionId;
			SELECT LAST_INSERT_ID() INTO p_projectcommissionId;
			
			#项目明细表
			INSERT INTO productdb.`projectcommissiondetail` (`FK_Projectcommission`,`Start`,`End`,`ReturnPoint`,`IsValid`,`CreateTime`,`CreateUser`,`Sort`)
			SELECT p_projectcommissionId,0,NULL,`Percent`,'T',@p_createtime,-1,0
			FROM productdb.`projectcommission_bak`	
			WHERE ID = p_OldprojectcommissionId;
			
			#项目历史数据映射表
			INSERT INTO productdb.`rebatemapping` (`OldProjectcommission`,`NewProjectcommission`,`UseDate`,`CreateTime`)
			VALUES (p_OldprojectcommissionId,p_projectcommissionId,p_date,NOW());
			
			#更新历史报账项目ID
			UPDATE `financedb`.`shoppingrebatedetail` rd
			SET rd.`FK_ProjectCommission` = p_projectcommissionId, rd.`ModifyUser`=-1 
			WHERE rd.`FK_ProjectCommission` =p_OldprojectcommissionId AND rd.IntoTimeGuide = p_date ;
			
        END IF;
        UNTIL done=1 END REPEAT;
        
        CLOSE rebateCur; 
        
        
	#已报过帐，且时间在项目范围之内 更新历史报账数据中的项目ID
	
	UPDATE `financedb`.`shoppingrebatedetail` rd
	LEFT JOIN productdb.`projectcommission_bak` c ON rd.`FK_ProjectCommission` = c.`ID` 
	LEFT JOIN productdb.`rebatemapping` m ON m.`OldProjectcommission` = rd.`FK_ProjectCommission` AND m.`UseDate` = rd.`IntoTimeGuide`
	SET rd.`FK_ProjectCommission` = m.`NewProjectcommission`,rd.`ModifyUser`=-1 
	WHERE rd.`FK_ProjectCommission` IS NOT NULL AND rd.`IntoTimeGuide` IS NOT NULL AND c.`StartDate` IS NOT NULL AND c.`EndDate` IS NOT NULL 
	AND rd.`IntoTimeGuide` BETWEEN c.`StartDate`AND c.`EndDate`; 
		
		        
        IF has_err<>1 THEN		
		COMMIT;
	ELSE
		ROLLBACK;
	END IF;	      
        
END$$

DELIMITER ;
	
	
	





#调用
#call initcommission();