mysql 游标使用

//声明存储过程

DELIMITER $$
create procedure pro_initRecordCityID()
BEGIN
declare id int(10);
declare cityName varchar(20);/*城市名称名称*/
declare b int(10) default 0; /*是否达到记录的末尾控制变量*/
DECLARE cur_1 CURSOR FOR select CityID,Name from cities;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
OPEN cur_1;
FETCH cur_1 INTO id, cityName; /*获取第一条记录*/
while b<>1 do
SET SQL_SAFE_UPDATES=0;/*此句很重要*/
update node_bird set CityID=id where Location like CONCAT('%',cityName,'%');
FETCH cur_1 INTO id, cityName; /*取下一条记录*/
END while;
close cur_1;
END$$
DELIMITER ;

 

// 执行存储过程

call pro_initRecordCityID();