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 存储过程 遍历的全部内容，希望文章能够帮你解决mysql 存储过程 遍历所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。