mysql 游标使用 case when if elseif问题小结
背景：
函数，传入参数ID，知识点ID，初始程度，根据答题记录计算顺序计算改学生该知识点掌握程度，需要逐条计算，每条计算依赖上一条得出的值，
问题，游标定义问题，case when 内set问题
初始版本;
CREATE DEFINER=`cjtlis`@`%` FUNCTION `degree`(`masters` float,`start_time` datetime,`end_time` datetime,`subject_id` int,`student_id` BIGINT,`knowledge_id` int) RETURNS int(11)
BEGIN
declare ui_knowledge_id int DEFAULT 1;
declare ui_difficulty_level int DEFAULT 0;
declare ui_is_right int DEFAULT 0;
declare degree FLOAT;
declare done int DEFAULT 0;
declare question cursor for SELECT
ui_knowledge_id,
ui_difficulty_level,
ui_is_right
from
(SELECT
a.ui_question_id,
ui_knowledge_id,
v_knowledge_name,
ui_difficulty_level,
a.ui_student_id,
a.ui_is_right,
a.dt_syn_time
from db_cjhms_log.tb_student_question_log a
INNER JOIN db_cjhms_log.tb_homework_question_log b
on a.ui_homework_id=b.ui_homework_id
and a.ui_question_id=b.ui_question_id
and a.ui_student_id=student_id
and a.ui_subject_id=subject_id
and b.ui_knowledge_id=knowledge_id
and a.ui_is_master_over=0
and a.dt_syn_time>=start_time AND a.dt_syn_time <= end_time
UNION
SELECT
a.ui_question_id,
b.ui_knowledge_id,
b.v_knowledge_name,
b.ui_difficulty_level,
a.ui_student_id,
a.ui_is_right,
a.dt_syn_time
from db_exam_log.tb_student_exam_question_log a
INNER JOIN db_exam_log.tb_exam_question_log b
INNER JOIN db_exam_log.tb_exam_log c
on a.ui_exam_id=b.ui_exam_id
and a.ui_exam_id=c.ui_id
and a.ui_exam_id =b.ui_exam_id
and a.ui_student_id=student_id
and c.ui_subject_id=subject_id
and b.ui_knowledge_id=knowledge_id
and a.ui_is_master_over=0
and a.dt_syn_time>=start_time AND a.dt_syn_time <= end_time
)aa
ORDER BY
dt_syn_time DESC;
declare CONTINUE HANDLER FOR NOT FOUND SET done = 1;
set degree=if(masters is not null,masters,0);
open question;
-- DELETE from app_result_au_ana where name=longdate ;
read_loop:loop
fetch question into ui_knowledge_id,ui_difficulty_level,ui_is_right;
IF done = 1 THEN
LEAVE read_loop;
END IF;
CASE degree
WHEN degree<40 and ui_difficulty_level=1 and ui_is_right=1 THEN set degree=round(degree+(100-degree)*0.15,2);
WHEN degree<40 and ui_difficulty_level=1 and ui_is_right=0 THEN set degree=round(degree-degree*0.30,2);
--
WHEN degree<60 and ui_difficulty_level=2 and ui_is_right=1 THEN set degree=ROUND(degree+(100-degree)*0.25,2);
WHEN degree<60 and ui_difficulty_level=2 and ui_is_right=0 THEN set degree=ROUND(degree-degree*0.25,2);
WHEN degree<80 and ui_difficulty_level=3 and ui_is_right=1 THEN set degree=ROUND(degree+(100-degree)*0.35,2);
WHEN degree<80 and ui_difficulty_level=3 and ui_is_right=0 THEN set degree=ROUND(degree-degree*0.20,2);
WHEN degree<100 and ui_difficulty_level=4 and ui_is_right=1 THEN set degree=ROUND(degree+(100-degree)*0.45,2);
WHEN degree<100 and ui_difficulty_level=4 and ui_is_right=0 THEN set degree=ROUND(degree-degree*0.15,2);
WHEN degree<100 and ui_difficulty_level=5 and ui_is_right=1 THEN set degree=ROUND(degree+(100-degree)*0.55,2);
WHEN degree<100 and ui_difficulty_level=5 and ui_is_right=0 THEN set degree=ROUND(degree-degree*0.12,2);
ELSE
set degree=degree;
END CASE;
end loop read_loop;
CLOSE question;
RETURN degree;
END
这个版本会导致degree常为15，直走第一case，修改
Case如下：
去掉case后degree和每个case后“；”,但此时报出错误，找个各种解决方式无果，最后放弃case when，采用if elseif 方式，如下
但是此时发现结果还是不对，将函数内容复制出来创建过程，在过程中打印游标的值，发现值没有赋值进去，而是上面定义的默认值[函数前几行]，因为第一次使用游标，不会用，明天要用这个函数[之前使用初始版本，之前没有相关数据，此次测试包含数据，问题出现]，今天必须搞出来，加班到晚上8点时，灵光一闪，修改定义游标时的语句如下：
declare question cursor for SELECT
exam_id,
k_id,
d_l,
ri,
q_id
from
(SELECT
a.ui_homework_id as exam_id,
a.ui_question_id as q_id,
ui_knowledge_id as k_id,
v_knowledge_name as k_name,
ui_difficulty_level as d_l,
a.ui_student_id as s_id,
a.ui_is_right as ri,
a.dt_syn_time
from db_cjhms_log.tb_student_question_log a
INNER JOIN db_cjhms_log.tb_homework_question_log b
on a.ui_homework_id=b.ui_homework_id
and a.ui_question_id=b.ui_question_id
and a.ui_student_id=student_id
and a.ui_subject_id=subject_id
and b.ui_knowledge_id=knowledge_id
and a.ui_is_master_over=0
and a.ui_marked=1
and a.dt_syn_time>=start_time AND a.dt_syn_time <= end_time
UNION
SELECT
a.ui_exam_id as exam_id,
a.ui_question_id as q_id,
b.ui_knowledge_id as k_id,
b.v_knowledge_name as k_name ,
b.ui_difficulty_level as d_l,
a.ui_student_id s_id,
a.ui_is_right ri,
a.dt_syn_time
from db_exam_log.tb_student_exam_question_log a
INNER JOIN db_exam_log.tb_exam_question_log b
INNER JOIN db_exam_log.tb_exam_log c
on a.ui_exam_id=b.ui_exam_id
and a.ui_exam_id=c.ui_id
and a.ui_exam_id =b.ui_exam_id
and a.ui_student_id=student_id
and c.ui_subject_id=subject_id
and b.ui_knowledge_id=knowledge_id
and a.ui_is_master_over=0
and a.ui_marked=1
and a.dt_syn_time>=start_time AND a.dt_syn_time <= end_time
)aa
ORDER BY
dt_syn_time DESC;
运行测试，完全OK，原来定义游标时的select语句的字段不能跟游标字段一样，否则就不会有数据：
如果对你有用，请在右上点个赞哦
附上完整语句：
CREATE DEFINER=`cj_manager`@`%` FUNCTION `degree`(`masters` float,`start_time` datetime,`end_time` datetime,`subject_id` int,`student_id` BIGINT,`knowledge_id` int) RETURNS int(11)
BEGIN
declare ui_knowledge_id int DEFAULT 1;
declare difficulty_level int DEFAULT 0;
declare ui_is_right int DEFAULT 0;
declare question_id bigint DEFAULT 0;
declare ui_homework_exam_id bigint DEFAULT 0;
declare degree FLOAT ;
declare degree1 FLOAT;
declare done int DEFAULT 0;
declare question cursor for SELECT
all_id,
k_id,
d_L,
is_r,
q_id
from
(SELECT
a.ui_homework_id as all_id,
a.ui_question_id as q_id,
ui_knowledge_id as k_id,
v_knowledge_name as k_name,
ui_difficulty_level as d_L,
a.ui_student_id as s_id,
a.ui_is_right as is_r,
a.dt_syn_time
from db_cjhms_log.tb_student_question_log a
INNER JOIN db_cjhms_log.tb_homework_question_log b
on a.ui_homework_id=b.ui_homework_id
and a.ui_question_id=b.ui_question_id
and a.ui_student_id=student_id
and a.ui_subject_id=subject_id
and b.ui_knowledge_id=knowledge_id
and a.ui_is_master_over=0
and a.ui_marked=1
and a.dt_syn_time>=start_time AND a.dt_syn_time <= end_time
UNION
SELECT
a.ui_exam_id as ui_homework_exam_id,
a.ui_question_id as question_id,
b.ui_knowledge_id,
b.v_knowledge_name,
b.ui_difficulty_level,
a.ui_student_id,
a.ui_is_right,
a.dt_syn_time
from db_exam_log.tb_student_exam_question_log a
INNER JOIN db_exam_log.tb_exam_question_log b
INNER JOIN db_exam_log.tb_exam_log c
on a.ui_exam_id=b.ui_exam_id
and a.ui_exam_id=c.ui_id
and a.ui_exam_id =b.ui_exam_id
and a.ui_student_id=student_id
and c.ui_subject_id=subject_id
and b.ui_knowledge_id=knowledge_id
and a.ui_is_master_over=0
and a.ui_marked=1
and a.dt_syn_time>=start_time AND a.dt_syn_time <= end_time
)aa
ORDER BY
dt_syn_time DESC;
declare CONTINUE HANDLER FOR NOT FOUND SET done = 1;
set degree=if(masters is not null,masters,0);
open question;
-- DELETE from app_result_au_ana where name=longdate ;
read_loop:loop
fetch question into ui_homework_exam_id,ui_knowledge_id,difficulty_level,ui_is_right,question_id;
IF done = 1 THEN
LEAVE read_loop;
END IF;
--
-- CASE WHEN (degree<40 and ui_difficulty_level=1 and ui_is_right=1) THEN set degree=round(degree+(100-degree)*0.15,2)
-- WHEN (degree<40 and ui_difficulty_level=1 and ui_is_right=0) THEN set degree=round(degree-degree*0.30,2)
--
-- WHEN (degree<60 and ui_difficulty_level=2 and ui_is_right=1) THEN set degree=ROUND(degree+(100-degree)*0.25,2)
-- WHEN (degree<60 and ui_difficulty_level=2 and ui_is_right=0) THEN set degree=ROUND(degree-degree*0.25,2)
--
-- WHEN (degree<80 and ui_difficulty_level=3 and ui_is_right=1) THEN set degree=ROUND(degree+(100-degree)*0.35,2)
-- WHEN (degree<80 and ui_difficulty_level=3 and ui_is_right=0) THEN set degree=ROUND(degree-degree*0.20,2)
--
-- WHEN (degree<100 and ui_difficulty_level=4 and ui_is_right=1) THEN set degree=ROUND(degree+(100-degree)*0.45,2)
-- WHEN (degree<100 and ui_difficulty_level=4 and ui_is_right=0) THEN set degree=ROUND(degree-degree*0.15,2)
--
-- WHEN (degree<100 and ui_difficulty_level=5 and ui_is_right=1) THEN set degree=ROUND(degree+(100-degree)*0.55,2)
-- WHEN (degree<100 and ui_difficulty_level=5 and ui_is_right=0) THEN set degree=ROUND(degree-degree*0.12,2)
-- ELSE
-- set degree=degree
-- END CASE;
set degree =(SELECT
CASE WHEN (degree<40 and difficulty_level=1 and ui_is_right=1) THEN round(degree+(100-degree)*0.15,2)
WHEN (degree<40 and difficulty_level=1 and ui_is_right=0) THEN round(degree-degree*0.30,2)
WHEN (degree<60 and difficulty_level=2 and ui_is_right=1) THEN ROUND(degree+(100-degree)*0.25,2)
WHEN (degree<60 and difficulty_level=2 and ui_is_right=0) THEN ROUND(degree-degree*0.25,2)
WHEN (degree<80 and difficulty_level=3 and ui_is_right=1) THEN ROUND(degree+(100-degree)*0.35,2)
WHEN (degree<80 and difficulty_level=3 and ui_is_right=0) THEN ROUND(degree-degree*0.20,2)
WHEN (degree<100 and difficulty_level=4 and ui_is_right=1) THEN ROUND(degree+(100-degree)*0.45,2)
WHEN (degree<100 and difficulty_level=4 and ui_is_right=0) THEN ROUND(degree-degree*0.15,2)
WHEN (degree<100 and difficulty_level=5 and ui_is_right=1) THEN ROUND(degree+(100-degree)*0.55,2)
WHEN (degree<100 and difficulty_level=5 and ui_is_right=0) THEN ROUND(degree-degree*0.12,2)
ELSE
degree
end);
UPDATE db_cjhms_log.tb_student_question_log SET ui_is_master_over=1 WHERE ui_homework_id= ui_homework_exam_id and ui_question_id=question_id and ui_student_id=student_id;
UPDATE db_exam_log.tb_student_exam_question_log SET ui_is_master_over=1 WHERE ui_exam_id=ui_homework_exam_id and ui_question_id=question_id and ui_student_id=student_id;
end loop ;
CLOSE question;
RETURN degree;
END