存储过程

CREATE OR REPLACE
procedure test3 (sss in varchar) is
icount number;
begin
DBMS_OUTPUT.PUT_LINE('开始 去重 AID：'|| sss);
for wf in(select * from scorelog where (studentid,aid) in (select sr.studentid,sr.aid from scorelog sr group by sr.studentid,sr.aid having count(*) >1) and rowid not in (select max(rowid) from scorelog sr group by sr.studentid,sr.aid having count(*) >1))
loop
update score set SCORE = (SCORE - wf.score) where STUDENTID = wf.studentid;
select score into icount from score where studentid = wf.studentid;
DBMS_OUTPUT.PUT_LINE('更新score***STUDENTID:'|| wf.studentid || '减' || wf.score || '剩余'|| icount);
IF wf.type='1' THEN
BEGIN
delete x_certificate where keyid = wf.aid;
END;
END IF;

end loop;
delete from scorelog where (studentid,aid) in (select sr.studentid,sr.aid from scorelog sr group by sr.studentid,sr.aid having count(*) >1) and rowid not in (select max(rowid) from scorelog sr group by sr.studentid,sr.aid having count(*) >1);

for wf2 in(select * from literscorelog where (studentid,aid,LITERSCOREID) in (select sr.studentid,sr.aid,sr.LITERSCOREID from literscorelog sr group by sr.studentid,sr.aid,sr.LITERSCOREID having count(*) >1) and rowid not in (select max(rowid) from literscorelog sr group by sr.studentid,sr.aid,sr.LITERSCOREID having count(*) >1))
loop
update LITERSCORE set SCORE = (SCORE- wf2.score) where KEYID = wf2.literscoreid and STUDENTID = wf2.studentid;

--DBMS_OUTPUT.PUT_LINE('更新LITERSCORE***studentid:' || wf2.studentid || '减' || wf2.score);
select score into icount from LITERSCORE where KEYID = wf2.literscoreid and STUDENTID = wf2.studentid;
DBMS_OUTPUT.PUT_LINE('更新LITERSCORE***STUDENTID:'|| wf2.studentid || '减' || wf2.score || '剩余'|| icount);
end loop;
delete from literscorelog where (studentid,aid,LITERSCOREID) in (select sr.studentid,sr.aid,sr.LITERSCOREID from literscorelog sr group by sr.studentid,sr.aid,sr.LITERSCOREID having count(*) >1) and rowid not in (select max(rowid) from literscorelog sr group by sr.studentid,sr.aid,sr.LITERSCOREID having count(*) >1);
DBMS_OUTPUT.PUT_LINE('执行结束');
end;