oracle 统计成绩

set serveroutput on;

declare

cursor c1 is select dno,dname from dep;

pdno dep.dno%TYPE;

pdname dep.dname%type;

cursor c2(aa varchar2,bb number) is select grade from sc where cno=(select cno from course where cname=aa)

and sno in (select sno from student where dno=bb);

pgrade sc.grade%type;

count1 number;count2 number;count3 number;avgg number;

coursename varchar2(100):=‘大学物理‘;

begin

open c1;

loop

fetch c1 into pdno,pdname;

exit when c1%notfound;

count1 :=0;count2 :=0;count3 :=0;

select avg(grade) into avgg from sc where cno=(select cno from course where cname=coursename)

and sno in (select sno from student where dno=pdno);

open c2(coursename,pdno);

loop

fetch c2 into pgrade;

exit when c2%notfound;

if pgrade <60 then count1:=count1+1;

elsif pgrade >60 and pgrade <=80 then count2:=count2+1;

else count3:=count3+1;

end if;

end loop;

close c2;

insert into msg values (coursename,pdname,count1,count2,count3,avgg);

end loop;

close c1;

dbms_output.put_line(‘统计完成‘);

end;

/