mysql 游标遍历_mysql存储过程中游标如何遍历

mysql存储过程中游标遍历的方法：首先取值，取多个字段；然后遍历数据结束标志，将结束标志绑定到游标，代码为【DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;】。

mysql存储过程中游标遍历的方法：CREATE DEFINER=`root`@`%` PROCEDURE `updStatus`()

BEGIN

DECLARE startTime DATETIME;

DECLARE endTime DATETIME;

DECLARE curTime DATETIME;

DECLARE id VARCHAR(36);

DECLARE estatus VARCHAR(4);

-- 遍历数据结束标志

DECLARE done INT DEFAULT FALSE;

-- 游标

DECLARE examIds CURSOR FOR SELECT EXAM_ID FROM t_exam WHERE EXAM_STATUS = 1 or EXAM_STATUS = 2;

-- 将结束标志绑定到游标

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN examIds;

-- 遍历

read_loop: LOOP

-- 取值 取多个字段

FETCH NEXT from examIds INTO id;

IF done THEN

LEAVE read_loop;

END IF;

SELECT EXAM_STATUS INTO estatus FROM t_exam WHERE EXAM_ID = id ;

IF estatus =1 THEN

SELECT NOW() INTO curTime;

SELECT EXAM_START_TIME INTO startTime from t_exam WHERE EXAM_ID = id ;

SELECT EXAM_END_TIME INTO endTime from t_exam WHERE EXAM_ID = id ;

IF curTime >= startTime AND endTime > curTime THEN

UPDATE t_exam SET EXAM_STATUS = 2 WHERE EXAM_ID = id;

ELSEIF curTime >= endTime THEN

UPDATE t_exam SET EXAM_STATUS = 3 WHERE EXAM_ID = id;

END IF;

ELSE

SELECT NOW() INTO curTime;

SELECT EXAM_END_TIME INTO endTime from t_exam WHERE EXAM_ID = id ;

IF curTime >= endTime THEN

UPDATE t_exam SET EXAM_STATUS = 3 WHERE EXAM_ID = id;

END IF;

END IF;

END LOOP;

CLOSE examIds;

END更多相关免费学习推荐：mysql教程(视频)