MySQL存储过程中的游标的使用以及双层游标嵌套循环实例

最新推荐文章于 2024-04-28 09:00:10 发布

Randy_0918

最新推荐文章于 2024-04-28 09:00:10 发布

阅读量1.4w

点赞数 3

分类专栏：数据库文章标签：游标双层游标嵌套循环 MySQL存储过程

本文链接：https://blog.csdn.net/m0_37897502/article/details/80911727

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

一、游标简介

1、游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

2、游标充当指针的作用

3、尽管游标能遍历结果中的所有行，但他一次只指向一行。

4、游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作

二、游标使用

1、声明游标

declare cur_name CURSOR for table; 
(这里的table可以是你查询出来的任意集合)

2、打开定义的游标

open  cur_name ;

3、获得下一行数据

FETCH NEXT FROM cur_name into  variable; (variable 为前面存储过程中定义的变量名)

4、需要执行的业务，也就是需要执行的语句(增删改查)

5、释放游标

close  cur_name;

三、游标实例

1、简单实例，要实现的功能：从表from_data 中，复制一份数据存入to_data 中

DELIMITER $$

DROP PROCEDURE IF EXISTS `transfer_data`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `transfer_data`()
BEGIN
	DECLARE a INT;
	DECLARE b VARCHAR(32);
	DECLARE c INT;
	
	-- 这个变量用于处理游标到最后一行的情况
	DECLARE s INT DEFAULT 0;
	
	-- 声明游标cursor_name (cursor_name是一个多行结果集)
	DECLARE cursor_name CURSOR FOR SELECT id,NAME FROM from_data;
	
	-- 设置一个终止标记，SQLSTATE '02000'是一个未找到条件
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;
	-- 打开游标
	OPEN cursor_name;
	-- 获取游标当前指针的记录，读取一行数据并传递给变量
	FETCH cursor_name INTO a,b;
	
	-- 开始循环，判断是否游标已经到了最后作为循环条件
	WHILE s <> 1 DO 
		INSERT INTO to_data(id,NAME) VALUES (a,b);
		-- 读取下一行的数据
		FETCH NEXT FROM cursor_name INTO a,b;
	END WHILE;
	
	-- 关闭游标
	CLOSE cursor_name;
END$$

DELIMITER ;

2、双层游标嵌套循环，实现功能：匹配两个表中的字段，当两个表中的字段的匹配度达到100 就向关联表中插入数据

-- 品牌与品牌自动匹配
DELIMITER $$
DROP PROCEDURE IF EXISTS `brand_brand_automatch`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `brand_brand_automatch`()
BEGIN
	-- 这里定义的变量不能和表字段相同（重点）
	DECLARE cus_brand_id VARCHAR(50);
	DECLARE winner_brand_id VARCHAR(50);
	
	DECLARE cus_brand_name VARCHAR(100);
	DECLARE winner_brand_name VARCHAR(100);
	DECLARE match_grade INT(4);
	-- 设置终止标记
	DECLARE stopFlag INT DEFAULT 0;

	DECLARE cus_brand CURSOR FOR SELECT bi.brand_guid,bi.brand_name FROM `brand_info` bi
		WHERE NOT EXISTS (
			SELECT bbr.* FROM `brand_brand_relation` bbr WHERE bi.brand_guid = bbr.cus_brand_id
		);
	DECLARE winner_brand CURSOR FOR SELECT brand_guid,brand_name FROM `brand_info_winnerinfo`;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag = 1;
	OPEN cus_brand;
	-- 第一个游标循环
	out_loop:LOOP
		FETCH NEXT FROM cus_brand INTO cus_brand_id,cus_brand_name;
		IF stopFlag = 1 THEN
		    LEAVE out_loop;
		END IF;
		
		OPEN winner_brand;
		        -- 第二个游标循环
			inner_loop:LOOP
				FETCH NEXT FROM winner_brand INTO winner_brand_id,winner_brand_name;
				IF stopFlag = 1 THEN
				    LEAVE inner_loop;
				END IF;
				-- levenshtein_ratio 为自己定义的匹配函数（计算匹配度的）
				SET match_grade = levenshtein_ratio(cus_brand_name,winner_brand_name);
				IF match_grade = 100 THEN
				    INSERT INTO `brand_brand_relation`(`id`,`cus_brand_id`,`winer_brand_id`,`match_grade`,`manually_matching`,`createTime`)  VALUES (REPLACE(UUID(), '-', ''),cus_brand_id,winner_brand_id,match_grade,0,NOW());
				    SET stopFlag=1;
				END IF;
			END LOOP inner_loop;
		CLOSE winner_brand;
		-- 注意这里，这里是重点
		SET stopFlag=0;
		
	END LOOP out_loop;
	CLOSE cus_brand;
END $$
DELIMITER ;	-- 这里定义的变量不能和表字段相同（重点）
	DECLARE cus_brand_id VARCHAR(50);
	DECLARE winner_brand_id VARCHAR(50);
	
	DECLARE cus_brand_name VARCHAR(100);
	DECLARE winner_brand_name VARCHAR(100);
	DECLARE match_grade INT(4);
	-- 设置终止标记
	DECLARE stopFlag INT DEFAULT 0;

	DECLARE cus_brand CURSOR FOR SELECT bi.brand_guid,bi.brand_name FROM `brand_info` bi
		WHERE NOT EXISTS (
			SELECT bbr.* FROM `brand_brand_relation` bbr WHERE bi.brand_guid = bbr.cus_brand_id
		);
	DECLARE winner_brand CURSOR FOR SELECT brand_guid,brand_name FROM `brand_info_winnerinfo`;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag = 1;
	OPEN cus_brand;
	-- 第一个游标循环
	out_loop:LOOP
		FETCH NEXT FROM cus_brand INTO cus_brand_id,cus_brand_name;
		IF stopFlag = 1 THEN
		    LEAVE out_loop;
		END IF;
		
		OPEN winner_brand;
		        -- 第二个游标循环
			inner_loop:LOOP
				FETCH NEXT FROM winner_brand INTO winner_brand_id,winner_brand_name;
				IF stopFlag = 1 THEN
				    LEAVE inner_loop;
				END IF;
				-- levenshtein_ratio 为自己定义的匹配函数（计算匹配度的）
				SET match_grade = levenshtein_ratio(cus_brand_name,winner_brand_name);
				IF match_grade = 100 THEN
				    INSERT INTO `brand_brand_relation`(`id`,`cus_brand_id`,`winer_brand_id`,`match_grade`,`manually_matching`,`createTime`)  VALUES (REPLACE(UUID(), '-', ''),cus_brand_id,winner_brand_id,match_grade,0,NOW());
				    SET stopFlag=1;
				END IF;
			END LOOP inner_loop;
		CLOSE winner_brand;
		-- 注意这里，这里是重点
		SET stopFlag=0;
		
	END LOOP out_loop;
	CLOSE cus_brand;
END $$
DELIMITER ;