mysql 存储过程游标
来源:互联网 发布:python buffer类型使用 编辑:程序博客网 时间:2024/05/21 07:07
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
阅读全文
0 0
- mysql存储过程+游标
- mysql 游标+存储过程
- mysql 存储过程 游标
- mysql 存储过程游标
- MySQL 存储过程 游标
- Mysql 存储过程+游标应用
- MySQL存储过程和游标
- MySQL存储过程_游标
- MySql存储过程 游标(Cursor)
- mysql 存储过程游标学习
- Mysql存储过程与游标
- Mysql -- 存储过程/触发器/游标
- MySQL 存储过程/游标/事务
- MySQL 存储过程游标错误
- MySQL 存储过程之游标
- 【MySQL】存储过程 游标 触发器
- MySQL存储过程+游标+触发器
- MySQL存储过程及游标
- Python第三方组件
- Fixing Typos CodeForces
- Android零基础入门第23节:ImageButton和ZoomButton使用大全
- 移动端基础知识
- Python3输入输出与数据类型
- mysql 存储过程游标
- int 类的属性和方法
- mybatis访问数据库时报java.lang.NullPointerException
- mybatis的嵌套查询和延迟加载分析
- LARC DL笔记(二) 训练自己的img
- maven简介
- 实现Logistic回归
- Oracle查询语句
- 未使用宏的代码