MySQL存储过程双重循环

最新推荐文章于 2024-07-28 21:36:06 发布

Crystalqy

最新推荐文章于 2024-07-28 21:36:06 发布

阅读量9k

点赞数 1

分类专栏：数据库文章标签： MySQL 存储过程

本文链接：https://blog.csdn.net/Crystalqy/article/details/89927393

版权

数据库专栏收录该内容

38 篇文章 1 订阅

订阅专栏

在工作中遇到了数据迁移，需要些存储过程对数据进行迁移，用到了双重循环

BEGIN

-- 定义变量
DECLARE orderNo VARCHAR(500);
DECLARE total INT;
DECLARE payAmount decimal(11,2);

DECLARE itemId INT;
DECLARE itemNo VARCHAR(500);
DECLARE x  INT;

-- 设置终止标记
DECLARE stopFlag INT DEFAULT 0;

-- 定义游标，查询满足需要修复数据的结果集
DECLARE order_item_list CURSOR FOR 
SELECT tmp.* from (SELECT count(*) as total , order_no orderNo,payment_amount payAmount from table_a where payment_amount > 0  and extend_5 = 1 GROUP BY order_no) tmp where tmp.total > 1 ;

DECLARE item_list CURSOR FOR 
SELECT oit.id as itemId ,oit.order_no as itemNo from table_a oit JOIN 
	(SELECT * from 
		(SELECT count(*) as total , order_no orderNo from table_a where payment_amount > 0  and extend_5 = 1 GROUP BY order_no) tp where tp.total > 1 ) as temp
	on temp.orderNo = oit.order_no;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag = 1;


	-- 打开游标
	OPEN order_item_list;

		-- 第一个游标循环
		out_loop:LOOP
			-- 将游标中的值赋值给变量，要注意sql结果列的顺序
			FETCH NEXT FROM order_item_list INTO total, orderNo, payAmount;
				IF stopFlag = 1 THEN 
					LEAVE  out_loop;
				END IF;
				set x = 1;
				-- SELECT orderNo;

			-- 打开第二个游标
			OPEN item_list;
				-- 第二个游标循环
				inner_loop:LOOP
						FETCH NEXT FROM item_list INTO itemId,itemNo;
						IF stopFlag = 1 THEN 
							LEAVE  inner_loop;
						END IF;
					
						IF orderNo = itemNo THEN 

									IF  x > total THEN 	
										LEAVE  inner_loop;
									END  IF;
								
									IF  x <= total -1 THEN 
												-- SELECT itemId;
										-- SELECT (payAmount div total);
											update table_a set payment_amount = (payAmount div total) ,extend_5 = 0  where id = itemId and extend_5 = 1;

									ELSE
										 -- SELECT itemId;
									-- SELECT  (payAmount div total)+(payAmount MOD total);
										 update table_a set payment_amount = (payAmount div total)+(payAmount MOD total),extend_5 = 0  where id = itemId and extend_5 = 1; 

									END  IF;
									SET  x = x + 1;

						END IF;
						
				END LOOP inner_loop;

			CLOSE item_list;
		-- 注意这里，停止循环标志
			SET stopFlag=0;

		END LOOP out_loop;

	-- 关闭游标
	CLOSE order_item_list;
END