/*
综合案例4：
    用plsql语言编写一个程序，按系名分段统计（成绩小于60分，60~85，85分以上）"大学物理"
    课程各分数段的学生人数，及各系学生的平均成绩。
*/
set serveroutput on
declare
    --系的光标
    cursor cdept is select dno,dname from dep;
    pdno dep.dno%type;
    pdname dep.dname%type;
    
    --成绩光标
    cursor cgrade(coursename varchar2,deptno number) is select grade from sc 
    where  cno = (select cno from course where cname = coursename)
    and    sno in(select sno from student where dno = deptno);
    pgrade sc.grade%type;
    
    --每个分数段的人数
    count1 number;
    count2 number;
    count3 number;
    --每个系选修了"大学物理"学生的平均成绩
    avggrade number;
    --课程名称
    pcourseName varchar2(10) := '大学物理';
begin
  --打开系的光标 
  open cdept;
  
  loop
       --取一个系的信息
       fetch cdept into pdno,pdname;
       exit when cdept%notfound;
       
       --初始化工作
       count1 := 0;
       count2 := 0;
       count3 := 0;
       
       --系的平均成绩
       select avg(grade) into avggrade from sc
       where cno = (select cno from course where cname = pcourseName)
       and   sno in(select sno from student where dno = pdno);
       
       --取系中选修了大学物理的学生成绩
       open cgrade(pcourseName,pdno);
       
         loop
           --取一个学生的成绩
           fetch cgrade into pgrade;
           exit when cgrade%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 cgrade;
       
       --保存当前表
       insert into msg1 values(pcourseName,pdno,count1,count2,count3,avggrade);
  
  end loop;
  
  --关闭系的光标
  close cdept;
  commit;
  dbms_output.put_line('统计完成！');
end;
/