mysql 游标 复杂查询_Mysql利用游标遍历查询结果集

drop procedure if exists looppc;

delimiter //

create procedure looppc(OUT param1 INT)

begin

declare Done int default 0;

declare address1 varchar(50);

declare cityCode1 varchar(50);

declare location1 varchar(50);

– 声明游标

declare rs cursor for select address,city_code,location from machine where id in ( select min(id) from machine group by address having address is not null);

– 异常处理

DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = 1;

– 打开游标

OPEN rs;

// 查询结果集的条数

set param1 = (select FOUND_ROWS());

/* 逐个取出当前记录LingQi字段的值，需要进行最大值的判断 */

FETCH rs INTO address1, cityCode1, location1;

/* 遍历数据表 */

REPEAT

insert into address(address,city_code,location) values (address1,cityCode1,location1);

update machine set address_id = id1 where address = address1;

FETCH rs INTO address1, cityCode1, location1;

UNTIL Done END REPEAT;

/* 关闭游标 */

CLOSE rs;

END //

DELIMITER ;

// 执行存储过程

call looppc(@a);

// 打印结果

select @a;