-- 创建存储过程
DELIMITER // ---- 改变 MySQL delimiter 为:“//”
DROP PROCEDURE if EXISTS proc_dist_update//
CREATE PROCEDURE proc_dist_update()
BEGIN
DECLARE finished INT DEFAULT 0;
DECLARE distId,newId bigint(20);
DECLARE distName varchar(50) CHARACTER SET utf8;
DECLARE distY,distX varchar(50) CHARACTER SET utf8;
-- 定义游标
DECLARE dist_cursor CURSOR for select id,name,y,x from nets_test_district;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN dist_cursor;
dist_loop:LOOP
-- 使用游标检索数据
FETCH dist_cursor into distId,distName,distY,distX;
IF finished = 1 THEN
LEAVE dist_loop;
END IF;
-- 查询新表中ID
SELECT ta.id INTO newId FROM nets_test_area ta WHERE ta.name=distName;
-- 更新临时表数据
UPDATE nets_test_area ta set ta.y=distY,ta.x=distX WHERE ta.id=newId;
-- 更新测试结果数据
UPDATE nets_test_environment te set te.DIST_ID=newId WHERE te.DIST_ID=distId;
END LOOP dist_loop;
CLOSE dist_cursor;
END;
//
-- 还原分隔符
DELIMITER ;
-- 调用存储过程
call proc_dist_update();
-- 清空原表数据
truncate TABLE nets_test_district;
-- 新数据导入原表
INSERT INTO nets_test_district SELECT * FROM nets_test_area;
-- 删除临时表
DROP TABLE nets_test_area;
DELIMITER // ---- 改变 MySQL delimiter 为:“//”
DROP PROCEDURE if EXISTS proc_dist_update//
CREATE PROCEDURE proc_dist_update()
BEGIN
DECLARE finished INT DEFAULT 0;
DECLARE distId,newId bigint(20);
DECLARE distName varchar(50) CHARACTER SET utf8;
DECLARE distY,distX varchar(50) CHARACTER SET utf8;
-- 定义游标
DECLARE dist_cursor CURSOR for select id,name,y,x from nets_test_district;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN dist_cursor;
dist_loop:LOOP
-- 使用游标检索数据
FETCH dist_cursor into distId,distName,distY,distX;
IF finished = 1 THEN
LEAVE dist_loop;
END IF;
-- 查询新表中ID
SELECT ta.id INTO newId FROM nets_test_area ta WHERE ta.name=distName;
-- 更新临时表数据
UPDATE nets_test_area ta set ta.y=distY,ta.x=distX WHERE ta.id=newId;
-- 更新测试结果数据
UPDATE nets_test_environment te set te.DIST_ID=newId WHERE te.DIST_ID=distId;
END LOOP dist_loop;
CLOSE dist_cursor;
END;
//
-- 还原分隔符
DELIMITER ;
-- 调用存储过程
call proc_dist_update();
-- 清空原表数据
truncate TABLE nets_test_district;
-- 新数据导入原表
INSERT INTO nets_test_district SELECT * FROM nets_test_area;
-- 删除临时表
DROP TABLE nets_test_area;