CREATE PROCEDURE `cp_score`()
BEGIN
DECLARE stuId VARCHAR (40) ;
DECLARE pcId VARCHAR (40) ;
DECLARE num INT ;
DECLARE sumS1 FLOAT ;
DECLARE sumS2 FLOAT ;
DECLARE sumS FLOAT ;
DECLARE stuScoreId VARCHAR (40) ;
DECLARE t_error INTEGER DEFAULT 0 ;
DECLARE done INT DEFAULT FALSE ;
DECLARE my_cursor CURSOR FOR
SELECT
studentid,
cpperiodcourseid,
COUNT(1) AS countNum
FROM
`cp_studentscore`
WHERE CPPeriodCourseId IS NOT NULL
GROUP BY studentid,
cpperiodcourseid
HAVING countNum > 1 ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE ;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1 ;
SELECT NOW() ;
OPEN my_cursor ;
myLoop :
LOOP
FETCH my_cursor INTO stuId,
pcId,
num ;
SET sumS = 0 ;
IF done
THEN LEAVE myLoop ;
END IF ;
SELECT
id INTO stuScoreId
FROM
cp_studentscore
WHERE studentId = stuId
AND cpperiodcourseId = pcId
ORDER BY lastupdatedTime DESC
LIMIT 0, 1 ;
START TRANSACTION ;
#更新相同课程下明细列表对应的studentScoreId
UPDATE
cp_studentscoredetail
SET
cpstudentscoreid = stuScoreId
WHERE cpstudentscoreid IN
(SELECT
id
FROM
cp_studentscore
WHERE studentId = stuId
AND cpperiodcourseId = pcId) ;
#计算有活动的课程总学分部分
SELECT
SUM(MaxScore) INTO sumS1
FROM
(SELECT
MAX(score) AS MaxScore
FROM
cp_studentscoredetail
WHERE cpcoursegroupId IS NOT NULL
AND cpstudentscoreid = stuScoreId
GROUP BY cpcoursegroupId) AS T ;
#计算没有活动名称课程分数部分
SELECT
SUM(score) INTO sumS2
FROM
cp_studentscoredetail
WHERE CPCourseGroupId IS NULL
AND CPStudentScoreId = stuScoreId ;
IF (sumS1 IS NOT NULL)
THEN SET sumS = sumS + sumS1 ;
END IF ;
IF (sumS2 IS NOT NULL)
THEN SET sumS = sumS + sumS2 ;
END IF ;
#更新课程成绩汇总表
UPDATE
cp_studentscore
SET
score = sumS
WHERE id = stuScoreId ;
#删除多余的课程成绩记录
DELETE
FROM
cp_studentScore
WHERE studentId = stuId
AND cpperiodcourseId = pcId
AND id <> stuScoreId ;
IF t_error = 1
THEN ROLLBACK ;
ELSE COMMIT ;
END IF ;
END LOOP myLoop ;
CLOSE my_cursor ;
SELECT NOW() ;
END