存储过程2

DELIMITER $$
CREATE PROCEDURE deal_diqu()
BEGIN
— 定义变量
DECLARE id_var INT(11);
DECLARE h_code_var INT(11);
DECLARE _region_name VARCHAR(50);
DECLARE _region_name_var VARCHAR(50);
DECLARE _father_id INT(11);
DECLARE s_count INT(11);
DECLARE s_count_var INT(11);

DECLARE stop_flag TINYINT;

— 遍历数据结束标志
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT `id`,`h_code` FROM `wo_resume`;

— 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET stop_flag = 1;

— 为变量赋值
— SET _nowTime = NOW();
SET stop_flag = 0;

OPEN cur; — 打开游标
FETCH cur INTO id_var,h_code_var;

SELECT count(*),`region_name`,`father_id` into s_count,_region_name,_father_id from `wo_region` where region_code=h_code_var;

IF s_count <> 0 THEN

SELECT count(*),`region_name` into s_count_var,_region_name_var from `wo_region` where region_code=_father_id;

END IF;

WHILE stop_flag<>1 DO — 若游标有下一条记录，循环
IF s_count <> 0 THEN
UPDATE wo_resume SET region_code_city = _father_id,region_name_city = _region_name_var,region_code_district = h_code_var,region_name_district = _region_name WHERE id = id_var;
END IF;

FETCH cur INTO id_var,h_code_var;

SELECT count(*),`region_name`,`father_id` into s_count,_region_name,_father_id from `wo_region` where region_code = h_code_var;
IF s_count <> 0 THEN
SELECT count(*),`region_name` into s_count_var,_region_name_var from `wo_region` where region_code = _father_id;
END IF;

END WHILE;
CLOSE cur; — 关闭游标

END $$
DELIMITER ;