MYSQL 存储过程多游标嵌套遍历

原创
2020/11/21 14:32
阅读数 387

mysql 存储过程临时表、多游标嵌套使用样例:

 



CREATE PROCEDURE method(IN start_Code varchar(10),IN end_Code varchar(10),in start_Airport varchar(255),in end_Airport varchar(255))
BEGIN
DECLARE out_done INT DEFAULT FALSE ; -- 外层游标控制变量
DECLARE e_done INT DEFAULT 0;
DECLARE s_province varchar(50);
DECLARE s_city varchar(100);
DECLARE s_county varchar(100);
DECLARE e_province varchar(50);
DECLARE e_city varchar(100);
DECLARE e_county varchar(100);
DECLARE delivery_type INT;
DECLARE delivery_type_cn varchar(10);
DECLARE delivery_type_num INT;
DECLARE delivery_num_one INT;
DECLARE result_tmp varchar(10);
DECLARE startlist CURSOR FOR 
		SELECT p,c,cou FROM conver WHERE code = start_Code;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET out_done = TRUE ; -- 外层游标执行结束,置为TRUE
-- 创建临时表
create temporary table if not exists tmpTable(
	id int primary key,
	startport varchar(100),
	startProvince varchar(100),
	endport varchar(100),
	endProvince varchar(100),
	deliveryType varchar(10),
	result varchar(10)
);
-- 清除临时表数据
truncate TABLE tmpTable;
OPEN startlist;
WHILE NOT out_done DO -- out WHILE
    FETCH startlist INTO s_province,s_city,s_county;
 	IF NOT out_done THEN
		BEGIN
			DECLARE inner_done int DEFAULT FALSE ;
			DECLARE endlist CURSOR FOR 
				select p,c,cou FROM conver where code = end_Code;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET inner_done = TRUE ;
			OPEN endlist;
				WHILE NOT inner_done DO -- inner WHILE
					FETCH endlist INTO e_province,e_city,e_county; -- 从【内层游标】中获取数据,赋值到定义变量中
					IF NOT inner_done THEN
					set delivery_type=1;  --  方式 1:自  2:送
					WHILE delivery_type <=2 DO  
						IF delivery_type = 1 THEN 
							set delivery_type_cn = '自';
							select COUNT(*) INTO delivery_type_num from user au where code =  end_Code;
							IF delivery_type_num > 0  THEN
								set result_tmp = 'OK';
							ELSE 
								set result_tmp = 'NO';
							END IF;
						ELSE 
							set delivery_type_cn = '送';
							select count(*) into delivery_num_one from delivery where county = e_county;
							IF delivery_num_one > 0 THEN
								set result_tmp = 'OK';
							ELSE 	
								set result_tmp = 'NO';
							END IF;	
						END IF;
							set e_done =e_done+1;
							insert into tmpTable values(e_done,start_Airport,s_province,end_Airport,e_province,delivery_type_cn,result_tmp);
							set delivery_type=delivery_type+1;  
					END WHILE;  
					END IF; -- END inner IF
				END WHILE ; -- END inner WHILE
			CLOSE endlist;-- 这个语句关闭先前打开的光标。
		END; -- END inner BEGIN
 	END IF; -- END out IF
END WHILE; -- END out WHILE
CLOSE startlist;-- 这个语句关闭先前打开的光标。
select * from tmpTable; 
END;

调用过程:

set @start_Code='O';
set @end_Code='C';
set @start_Airport='机场';
set @end_Airport='国际机场';
call Method(@start_Code,@end_Code,@start_Airport,@end_Airport);

 

 

 

 

 

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
0 评论
0 收藏
0
分享
返回顶部
顶部