MYSQL 游标使用.避免插入duplicate 重复数据

云墨cloudMel

已于 2022-10-18 07:53:16 修改

阅读量271

点赞数

文章标签： mysql sql 数据库

于 2022-10-18 07:30:09 首次发布

本文链接：https://blog.csdn.net/weixin_42481234/article/details/127380511

版权

CREATE DEFINER=`root`@`%` PROCEDURE `Process_Over_Days_Orders`()
BEGIN
	#Routine body goes here...
	BEGIN
		DROP TABLE IF EXISTS test_table;
		CREATE  TEMPORARY TABLE IF NOT EXISTS test_table( `order_no` varchar(30));

		Insert into test_table
		Select order_no from order_status
		where 
			TIMESTAMPDIFF(DAY, order_time, modified ) >= 90
			and order_status = '10'
-- 			and order_no Not in (
-- 				 Select order_no from TMP_Over_90_days_order
-- 			)
		limit 1;
	END;
	
	BEGIN
	
		DECLARE cid BIGINT;
		DECLARE vDone INT;
		DECLARE cur CURSOR for 	
			  Select order_no as cid from  test_table;
		DECLARE CONTINUE HANDLER for not FOUND set vDone=1;
		
-- 		open cur;		
-- 				posLoop:LOOP
-- 					IF done=1 THEN
-- 							LEAVE posLoop; 						
-- 					END IF;	
 ## 错误写法, 或造成最后一条数据, 重复插入2次	
-- 					
-- 					FETCH cur INTO cid;				
-- 					Insert into TMP_Over_90_days_order (order_no) Values (cid);	
-- 				
-- 				END LOOP posLoop;
-- 				
-- 		CLOSE cur;
-- 	
	
-- ok		
		open cur;		
				posLoop:LOOP
					FETCH cur INTO cid;		
							
					IF vDone=1 THEN
							LEAVE posLoop;
					ELSE 					
						Insert into TMP_Over_90_days_order (order_no) Values (cid);		
					END IF;		
				END LOOP posLoop;
				
		CLOSE cur;
-- ok
		open cur;		
				posLoop:LOOP
				
        FETCH cur INTO cid;				
					IF vDone=1 THEN
							LEAVE posLoop;				
					END IF;					
					Insert into TMP_Over_90_days_order (order_no) Values (cid);	
				
				END LOOP posLoop;
				
		CLOSE cur;
		
   END;
	 
	 

	
	


END