set serveroutput on
declare
count1 number;
count2 number;
count3 number;
avggrade number;
cdno number;
csno number;
--cdname  varchar2(20):='大学物理';
cdname  varchar2(20);
pdname varchar2(20);
cursor cdep is select dno,dname from dep;
cursor csc(pcname varchar2,csno number)
           is select grade from sc where cno=(select cno from course where cname =pcname) 
                                                 and sno in (select sno from student where dno =csno);
pgrade sc.grade%type;
cursor ccourse is select cname from course order by cno;
begin
open ccourse ;
loop
fetch ccourse into cdname;
exit when ccourse%notfound;
open cdep;
loop
fetch cdep into cdno,pdname;
exit when cdep%notfound;
count1 :=0;
count2 :=0;
count3 :=0;
select avg(grade) into avggrade from sc where cno=(select cno from course where cname =cdname) 
                                                 and sno in (select sno from student where dno =cdno);
open csc(cdname,cdno);
loop
fetch csc into pgrade;
exit when csc%notfound;
if pgrade < 60 then count1:=count1+1;
elsif pgrade >60 and pgrade <85 then count2:=count2+1;
else count3:=count3+1;
end if ;

end loop;

close csc;
insert into msg1 values(cdname,pdname,count1,count2,count3,avggrade);

end loop;
close cdep;
end loop ;

close ccourse;
commit;
end;
/