oracle查询男生的平均分,查询出所有课程的平均分,人数

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;

/