Mysql的存储过程实例

要求：根据r_student_subject表中的score的值确定学生等级并在grade列中显示
1.创建student表并填充数据

2.创建课程表subject填写数据

3.将subject与student表创建关系生成r_student_subject

存储过程（setGrade）：

BEGIN
DECLARE tId int;
DECLARE tScore FLOAT;
DECLARE tGrade VARCHAR(50);
DECLARE isLoop int DEFAULT 1;
DECLARE cur CURSOR for  SELECT  id,score FROM r_student_subject;
DECLARE CONTINUE HANDLER for not found set isLoop=0;
  open cur;
fetch cur into tId,tScore;
while isLoop >0 do
if tScore>=90
then 
set tGrade='优秀';
elseif tScore>=80
then 
set  tGrade='良好';
elseif tScore>=70
then
set tGrade='一般';
elseif tScore>=60
then
set tGrade='及格';
elseif tScore<60
then
set tGrade='不及格';
end if;
update r_student_subject set grade=tGrade where id=tId;
fetch cur into tId,tScore;
  end while;
CLOSE cur;
END

在sql查询界面输入：Call setGrade

结果如图所示：