SQL存储过程进行成绩绩点换算

来源：互联网发布：阿里云自定义监控实例编辑：程序博客网时间：2024/05/21 15:49

已知下面百分制成绩和GPA 绩点、等级成绩之间的关系，建立名为 PA** (即以 PA 开头，后面是建过程人的学号)的存储过程，用于实现对给定的百分制成绩，输出其GPA 绩点和等级成绩。输入参数为百分制成绩，返回参数为绩点、等级成绩。如输出成绩不在0~100 直接，存储过程返回-1，否则返回0。

成绩起始分值成绩截止分值绩点等级成绩
95 100 4.3 A+
90 94 4.0 A
85 89 3.7 A-
82 84 3.3 B+
78 81 3.0 B
75 77 2.7 B-
72 74 2.3 C+
68 71 2.0 C
65 67 1.7 C-
64 64 1.5 D+
61 63 1.3 D
60 60 1.0 D-
0 59 0 F

create procedure PAPB14210XXX (@grade int, @GPA float(2) output, @level char(5) output)asbeginif @grade between 0 and 59  beginselect @GPA=0select @level='F'endelse if @grade = 60 beginselect @GPA=1select @level='D-'endelse if(@grade between 61 and 63) beginselect @GPA=1.3select @level='D'; endelse if(@grade between 64 and 64) beginselect @GPA=1.5;select @level='D+'; endelse if(@grade between 65 and 67) beginselect @GPA=1.7;select @level='C-'; endelse if(@grade between 68 and 71)beginselect @GPA=2;select @level='C'; endelse if(@grade between 72 and 74)beginselect @GPA=2.3;select @level='C+'; endelse if(@grade between 75 and 77)beginselect @GPA=2.7;select @level='B-'; endelse if(@grade between 78 and 81)beginselect @GPA=3;select @level='B'; endelse if(@grade between 82 and 84)beginselect @GPA=3.3;select @level='B+';endelse if(@grade between 85 and 89)beginselect @GPA=3.7;select @level='A-';  endelse if(@grade between 90 and 94)beginselect @GPA=4;select @level='A'; endelse if(@grade between 95 and 100)beginselect @GPA=4.3select @level='A+'endif (@grade between 0 and 100)return 0else return -1end

调用：

declare @GPA float(2);declare @level char(5);execute PAPB14210XXX 95, @GPA output,@level outputselect @GPA,@level

0 0