mysql 存储过程的 hello word

2019-04-02 09:24:25
  1. CREATE
  2. DEFINER=`root`@`%` #创建者是谁
  3. PROCEDURE `getRank`( IN `examId` INT, IN `course` INT, IN `classId` INT, IN `myScore` FLOAT)
  4. # PROCEDURE(存储过程关键字) getRank(存储过程名字)
  5. # 括号里为参数 in为输入参数 out为输出参数 inout为输入输出参数(需要定义为全局变量)
  6. BEGIN #开始直到end均为自己编写的代码
  7. DECLARE score FLOAT; #分数
  8. DECLARE stuSum INT; #同分数人数
  9. DECLARE s INT DEFAULT 0; #while循环控制变量
  10. DECLARE tempRank INT DEFAULT 1; #排名
  11. DECLARE
  12. scoreList CURSOR FOR SELECT
  13. a.score,
  14. COUNT( userId ) stuSum
  15. FROM
  16. (
  17. SELECT
  18. b.`userId`,
  19. SUM( a.`score` ) score
  20. FROM
  21. score a,
  22. student b
  23. WHERE
  24. a.`stuUserId` = b.`userId`
  25. AND a.`examId` = examId
  26. AND a.`course` = course
  27. AND b.`classId` = classId
  28. GROUP BY
  29. b.`userId`
  30. ) a
  31. GROUP BY a.score
  32. ORDER BY
  33. a.score DESC;
  34. ## DECLARE均为申明变量 scoreList为游标(用于储存结果集用于遍历)
  35. DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s= 1 ;
  36. #控制循环完结果集让s=1 结束循环
  37. OPEN scoreList;#打开游标
  38. WHILE s <> 1 DO #while不多做解释 都懂
  39. FETCH scoreList INTO score,stuSum; #把游标里的值赋予变量
  40. IF score = myScore THEN#我的成绩等于当前分数时停止循环
  41. SET s = 1;
  42. END IF;
  43. if s <> 1
  44. then
  45. set tempRank=tempRank+stuSum;#排名的累加
  46. end if;
  47. END WHILE;
  48. CLOSE scoreList;#关闭游标
  49. select tempRank;#输出排名
  50. END
  1. SELECT
  2. a.score,
  3. COUNT( userId ) stuSum
  4. FROM
  5. (
  6. SELECT
  7. b.`userId`,
  8. SUM( a.`score` ) score
  9. FROM
  10. score a,
  11. student b
  12. WHERE
  13. a.`stuUserId` = b.`userId`
  14. AND a.`examId` = examId
  15. AND a.`course` = course
  16. AND b.`classId` = classId
  17. GROUP BY
  18. b.`userId`
  19. ) a
  20. GROUP BY a.score
  21. ORDER BY
  22. a.score DESC;

附上这个sql语句结果图:

存储过程执行结果展示:
四个参数依次为考试id 科目id 班级id 我的成绩


0
2
0

添加评论

正在回复:
取消
0
0
2
0