set serveroutput on;declarecursor 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):='大学物理';beginopen c1;loopfetch 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);loopfetch 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;/