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儲存過程中游標如何遍歷的詳細內容,更多請關注TW511.COM其它相關文章!