MYSQL 游标使用示例

最新推荐文章于 2024-06-21 11:56:49 发布

cxasen

最新推荐文章于 2024-06-21 11:56:49 发布

阅读量432

点赞数

本文链接：https://blog.csdn.net/lin1991921/article/details/41680517

版权

mysql使用游标示例备忘

BEGIN 

	DECLARE  receiverID INT;
	DECLARE existNum INT;
	DECLARE done INT DEFAULT 0;

	#mysql 不支持动态游标，so...
	declare UserID_cur CURSOR FOR select UserID FROM zhijia_gfamily_t_user;
	declare DeveloperID_cur CURSOR FOR select DeveloperID FROM zhijia_gfamily_t_developer;
	declare MerchantID_cur CURSOR FOR select MerchantID FROM zhijia_gfamily_t_equipmentmerchant;
	#定义结束循环标识，不这样可能会报错（1329 - No data - zero rows fetched, selected, or processed）
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

			CASE role
				WHEN 1 THEN 
					OPEN UserID_cur;#接收者为所有玩家
					fetchSeqLoop1:Loop 
						IF (done =1) THEN LEAVE fetchSeqLoop1; END IF;   
            FETCH UserID_cur INTO receiverID; 
						SELECT COUNT(*) INTO existNum from zhijia_gfamily_t_messagereceivemapping m where  m.ReceiverID = receiverID and m.MessageID = MessageID and m.ReceiveRole = role;
						#没有相同的信息才插入
						IF (existNum=0) THEN 
						INSERT INTO zhijia_gfamily_t_messagereceivemapping (ReceiveRole,ReceiverID,ReadFlag,MessageID) VALUES (role,receiverID,0,MessageID); 	
						END IF;
					end loop fetchSeqLoop1;				
				CLOSE UserID_cur;

				WHEN 2 THEN 
					OPEN DeveloperID_cur;#接收者为所有开发者
					fetchSeqLoop2:Loop   		
						IF (done =1) THEN LEAVE fetchSeqLoop2; END IF; 	 		
            FETCH DeveloperID_cur INTO receiverID; 
						SELECT COUNT(*) INTO existNum from zhijia_gfamily_t_messagereceivemapping m where  m.ReceiverID = receiverID and m.MessageID = MessageID and m.ReceiveRole = role;
						IF (existNum=0) THEN 
						INSERT INTO zhijia_gfamily_t_messagereceivemapping (ReceiveRole,ReceiverID,ReadFlag,MessageID) VALUES (role,receiverID,0,MessageID); 	
						END IF;
					end loop fetchSeqLoop2; 
					CLOSE DeveloperID_cur;


				WHEN 3 THEN 
					OPEN MerchantID_cur;#接收者为所有设备商
					fetchSeqLoop3:Loop  
						IF (done =1) THEN LEAVE fetchSeqLoop3; END IF; 	
            FETCH MerchantID_cur INTO receiverID; 
						SELECT COUNT(*) INTO existNum from zhijia_gfamily_t_messagereceivemapping m where  m.ReceiverID = receiverID and m.MessageID = MessageID and m.ReceiveRole = role;
						IF (existNum=0) THEN 
						INSERT INTO zhijia_gfamily_t_messagereceivemapping (ReceiveRole,ReceiverID,ReadFlag,MessageID) VALUES (role,receiverID,0,MessageID); 	
						END IF;
					end loop fetchSeqLoop3;  
					CLOSE MerchantID_cur;
			END CASE;		
END