create procedure ayjsyj1(IN rkbh varchar(255))
begin
DECLARE not_found INT DEFAULT 0;
DECLARE cpbh varchar(255);
DECLARE rksl decimal(12,2) ;
DECLARE cgdj decimal(12,2) ;
DECLARE cgzj decimal(12,2) ;
DECLARE sl decimal(12,2) ;
UPDATE SKT74 SET SKF1598=0 WHERE SKF1598 IS NULL;
UPDATE SKT74 SET SKF1599=0 WHERE SKF1599 IS NULL;
UPDATE SKT74 SET SKF1600=0 WHERE SKF1600 IS NULL;
UPDATE SKT74 SET SKF1601=0 WHERE SKF1601 IS NULL;
UPDATE SKT74 SET SKF1623=0 WHERE SKF1623 IS NULL;
begin
DECLARE cur_1 CURSOR FOR SELECT SKF1575,SKF1576,SKF1577,SKF1578 FROM SKT110 where SKF1573=rkbh;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found=1;
OPEN cur_1;
WHILE not_found = 0 DO
FETCH cur_1 INTO cpbh,rksl,cgdj ,cgzj ;
select count(*) into sl from SKT74 where SKF1072=cpbh;
if not_found=0 and sl=0 then
INSERT INTO SKT74(SKF1072,SKF1600,SKF1598,SKF1601,SKF1623) VALUES (cpbh,cgdj ,cgzj ,rksl,cgzj);
end if;
if not_found=0 and sl>0 then
update SKT74 set SKF1601=SKF1601+rksl,SKF1598=SKF1598+cgzj ,SKF1623=SKF1623+cgzj where SKF1072=cpbh;
update SKT74 set SKF1600=SKF1623/SKF1601 where SKF1072=cpbh;
end if;
END WHILE;
CLOSE cur_1;
end;
end;
从SKT110表选出数据,声明一个游标cur_1,然后遍历这个得到的结果,将根据该游标信息得到的其他信息insert到另一张表