/*
綜合案例4：
????用plsql語言編寫一個程序，按系名分段統(tǒng)計（成績小于60分，60~85，85分以上）"大學物理"
????課程各分數(shù)段的學生人數(shù)，及各系學生的平均成績。
*/
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;
????
????--每個分數(shù)段的人數(shù)
????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;
??
??--關(guān)閉系的光標
??close?cdept;
??commit;
??dbms_output.put_line('統(tǒng)計完成！');
end;
/