存储过程例子（1）

/*
用PLSQL语言编写一个程序。按系(系名）分段统计（成绩小于60分，60-85分, 85分以上）
“大学物理”课程各分数段的学生人数，及各系学生的平均成绩。

可能的SQL：
系: select dno,dname from dep;
成绩：select grade from sc where cno = (select cno from course where cname=???)
and sno in (select sno from student where dno=???);
平均成绩：select avg(grade) from sc where cno = (select cno from course where cname=???)
and sno in (select sno from student where dno=???);
*/
set serveroutput on
declare
–系
cursor cdep is select dno,dname from dep;
pdno dep.dno%type;
pdname dep.dname%type;

–成绩
cursor cgrade(depNo number,courseName varchar2)
is select grade from sc where cno = (select cno from course where cname=courseName)
and sno in (select sno from student where dno=depNo);
pgrade sc.grade%type;

–计数器
count1 number; count2 number;count3 number;

–平均分
avgGrade number;

–课程名称
pcourseName varchar2(40) := ‘大学物理’;
begin
open cdep;
loop
–取一个系
fetch cdep into pdno,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=pcourseName)
                                and sno in (select sno from student where dno=pdno);  
--得到该系学生的成绩
open cgrade(pdno,pcourseName);
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 msg values(pcourseName,pdname,count1,count2,count3,avggrade);

end loop;
close cdep;

commit;
dbms_output.put_line(‘完成’);

end;
/