今天遇到一个系统的数据错误,需要进行修复,之前一直用Oracle,对MySQL不是特别熟悉
搞了好一会,记录下来写法,这里用到了三种循环的其中一种LOOP
大体上是将A表的数据更新到B表,B表和A表的关系属于被包含关系,遍历B表
详细如下:
BEGIN
DECLARE t_department_one VARCHAR (32);
-- 自定义变量
DECLARE t_department_second VARCHAR (50);
-- 自定义变量
DECLARE t_department_station VARCHAR (50);
-- 自定义变量
DECLARE t_job_number VARCHAR (50);
-- 自定义变量
-- 遍历数据结束标志
DECLARE done INT DEFAULT FALSE;
-- 游标
DECLARE cur CURSOR FOR SELECT
erw_employee_job_number
FROM
pw_employee_return_work;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done = TRUE;
-- 打开游标
OPEN cur;
-- 开始循环
myLoop : LOOP
-- 提取游标里的数据,这里只有一个,多个的话也一样;
FETCH cur INTO t_job_number;
-- 声明结束的时候
IF done THEN
LEAVE myLoop;
END
IF;
SELECT
pw_employee.e_department_one,
pw_employee.e_department_second,
pw_employee.e_department_station INTO t_department_one,
t_department_second,
t_department_station
FROM
pw_employee
WHERE
pw_employee.e_job_number = t_job_number;
UPDATE pw_employee_return_work
SET pw_employee_return_work.erw_department_name = t_department_one,
pw_employee_return_work.erw_department_one = t_department_one,
pw_employee_return_work.erw_department_second = t_department_second,
pw_employee_return_work.erw_department_station = t_department_station
WHERE pw_employee_return_work.erw_employee_job_number = t_job_number;
COMMIT;
-- 提交事务
END
LOOP;
-- 关闭游标
CLOSE cur;
END
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done = TRUE;
比较难理解的是这个地方,这个是个条件处理,针对NOT FOUND的条件,当没有记录时赋值为 TRUE