DROP procedure IF EXISTS t_from1_to_from2_1;
CREATE procedure t_from1_to_from2_1()
BEGIN
-- 定义变量
DECLARE i INT DEFAULT 0;
DECLARE fid varchar(255);
DECLARE sum INT DEFAULT 0;
-- 结果集
DECLARE report CURSOR FOR select t.fid ,sum(t.sum) sum from (
select f1.fid , f1.count * f2.price as sum
from from1 f1
LEFT JOIN from2 f2 on f1.cid = f2.cid
) t GROUP BY t.fid ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET i = 1;
-- 打开游标
open report;
out_loop:
LOOP
FETCH report INTO fid,sum;
IF (i =1) THEN
LEAVE out_loop;
END IF;
if (sum is null or sum = 0) then
set @flag = (select sum(t.sum) sum from (
select f1.fid , f1.count * f2.price as sum
from from1 f1
LEFT JOIN from2 f2 on f1.cid = f2.cid
) t where t.fid = fid GROUP BY t.fid )
update from2 set price = @flag where cid = fid;
SET i = 0;
ELSE
update from2 set price = sum where cid = fid;
SET i = 0;
end if;
END LOOP out_loop;
-- 关闭游标
close report;
END
call t_from1_to_from2_1();