Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to traverse cursors in mysql stored procedures, which can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

The method of cursor traversal in mysql stored procedure: first take the value and take multiple fields; then traverse the data end flag and bind the end flag to the cursor, the code is [DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;].

The method of cursor traversal in mysql stored procedures:

CREATE DEFINER= `root` @ `% `PROCEDURE `updStatus` () BEGINDECLARE startTime DATETIME;DECLARE endTime DATETIME;DECLARE curTime DATETIME;DECLARE id VARCHAR (36); DECLARE estatus VARCHAR (4);-- traversal data end flag DECLARE done INT DEFAULT FALSE;-- cursor DECLARE examIds CURSOR FOR SELECT EXAM_ID FROM t_exam WHERE EXAM_STATUS = 1 or EXAM_STATUS = 2;-- bind the end flag to the cursor DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;OPEN examIds -- traversing read_loop: LOOP-- takes multiple fields FETCH NEXT from examIds INTO id;IF done THENLEAVE read_loop;END IF;SELECT EXAM_STATUS INTO estatus FROM t_exam WHERE EXAM_ID = id;IF estatus = 1 THENSELECT 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 THENUPDATE t_exam SET EXAM_STATUS = 2 WHERE EXAM_ID = id;ELSEIF curTime > = endTime THENUPDATE t_exam SET EXAM_STATUS = 3 WHERE EXAM_ID = id;END IF;ELSESELECT NOW () INTO curTime;SELECT EXAM_END_TIME INTO endTime from t_exam WHERE EXAM_ID = id; IF curTime > = endTime THENUPDATE t_exam SET EXAM_STATUS = 3 WHERE EXAM_ID = id;END IF;END IF;END LOOP; CLOSE examIds END thank you for reading this article carefully. I hope it will be helpful for everyone to share how the cursor traverses the content in the mysql storage process. At the same time, I also hope that you will support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.