mysql 存储过程遍历

最新推荐文章于 2023-08-23 15:01:44 发布

原创最新推荐文章于 2023-08-23 15:01:44 发布 · 1.5k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #存储过程 #遍历

mysql 专栏收录该内容

3 篇文章

订阅专栏

本文详细介绍了MySQL中如何创建及使用存储过程进行数据遍历操作，包括使用游标和循环结构等关键步骤，旨在帮助开发者更高效地处理批量数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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 存储过程遍历