存储过程如下：
create procedure calcGPAbySno (
@vsno char(10),
@avg_gpa decimal(10,2) out
) as
declare @gpa decimal(10,1);
declare @vcredit int;
declare @vgrade int;
declare @sum_credit int;
declare @sum_gpa decimal(10,1);
declare cur_gpa cursor for select Ccredit,grade from course c,sc where c.cno=sc.cno and sno=@vsno and year<>'2014';
begin
set @sum_gpa=0.0;
set @sum_credit=0;
open cur_gpa;
fetch next from cur_gpa into @vcredit,@vgrade;
while @@FETCH_STATUS=0
begin
if @vgrade>=95 set @gpa=5.0;
else if @vgrade<60 set @gpa=0;
else set @gpa=5.0-(95-@vgrade)*0.1;
set @sum_credit=@sum_credit+@vcredit;
set @sum_gpa=@sum_gpa+@gpa*@vcredit;
fetch next from cur_gpa into @vcredit,@vgrade;
end;
close cur_gpa;
deallocate cur_gpa;
if @sum_credit=0 set @avg_gpa=0
else set @avg_gpa=@sum_gpa/@sum_credit;
update student set gpa=@avg_gpa where sno=@vsno;
end;
这是为了实现输入为学生学号，输出为该学生的GPA的功能，并且考虑大一刚入学、正在学课程、不及格课程等情况。
create function getCalcGPAbySnoById(@student_cno char(10))
returns decimal(10,2) as
begin
declare @avg_gpa decimal(10,2);
declare @gpa decimal(10,1);
declare @vcredit int;
declare @vgrade int;
declare @sum_credit int;
declare @sum_gpa decimal(10,1);
--根据传递的参数（学生的cno）用select 查询此学生的选课信息 并 创建游标
declare cur_gpa cursor for select Ccredit,grade from course c,sc where c.cno=sc.cno and sno=@vsno and year<>'2014' and sc.cno=@student_cno;
set @sum_gpa=0.0;--存放此学生所有选课 课程的gpa之和
set @sum_credit=0;--存放此学生所有选课 的总成绩
open cur_gpa;--打开游标
fetch next from cur_gpa into @vcredit,@vgrade;--将游标所指的值赋值给 变量 @vcredit,@vgrade
while @@FETCH_STATUS=0
begin
if @vgrade>=95 set @gpa=5.0;
else if @vgrade<60 set @gpa=0;
else set @gpa=5.0-(95-@vgrade)*0.1;
set @sum_credit=@sum_credit+@vcredit;
set @sum_gpa=@sum_gpa+@gpa*@vcredit;
fetch next from cur_gpa into @vcredit,@vgrade;
end
close cur_gpa;
deallocate cur_gpa;
if @sum_credit=0 set @avg_gpa=0;
else set @avg_gpa=@sum_gpa/@sum_credit;
update student set gpa=@avg_gpa where sno=@vsno;
return @avg_gpa;
end