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);
