DELIMITER $$

DROP PROCEDURE IF EXISTS update_district_level $$

CREATE PROCEDURE update_district_level()

BEGIN

DECLARE row_id INT;#定义变量ID

DECLARE row_district_id INT;#定义变量地区ID

DECLARE row_level INT;#定义变量地区等级

DECLARE done INT;

-- 定义游标

DECLARE rs_cursor CURSOR FOR

SELECT main.id,main.district_id,d.`level` FROM jsjh_goods_district main LEFT JOIN jsjh_district d ON d.id=main.district_id;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

OPEN rs_cursor;

cursor_loop:LOOP

FETCH rs_cursor INTO row_id,row_district_id,row_level; -- 取数据

IF done=1 THEN

leave cursor_loop;

END IF;

-- 更新表

UPDATE jsjh_goods_district SET district_level=row_level WHERE id=row_id;

END LOOP cursor_loop;

CLOSE rs_cursor;

END$$

DELIMITER ;

CALL update_district_level();