CREATE DEFINER=root@% PROCEDURE pro_upGrade()
BEGIN
declare sort_a int;
DECLARE classId int;
DECLARE compInfoId int;
DECLARE graduationYear int;
declare flag int default true;
declare curl cursor for SELECT tc.class_id ,tc.comp_info_id ,tg.sort,CAST(tc.graduation_year as SIGNED) graduation_year from t_class tc LEFT JOIN t_grade tg on tg.grade_id=tc.grade;
declare continue handler for not found set flag = false;
OPEN curl;
repeat
FETCH curl INTO classId,compInfoId,sort_a,graduationYear;
IF sort_a <> null and graduationYear
UPDATE t_class set grade=(SELECT grade_id FROM t_grade WHERE sort>sort_a AND comp_info_id=compInfoId AND status=1 ORDER BY sort LIMIT 1)
,grade_name=(SELECT name FROM t_grade WHERE sort>sort_a AND comp_info_id=compInfoId AND status=1 ORDER BY sort LIMIT 1) where class_id=classId;
ELSEif graduationYear=DATE_FORMAT(NOW(), '%Y') THEN
UPDATE t_class SET status=2 where class_id=classId;
end IF;
until flag = false
end repeat;
close curl;
END
这个是使用游标的方式， 就是如果数据量大的时候循环操作的时候会锁表，如何优化进行直接将结果按条件批量更新
