Mysql fetch next iteration-CodePudding

 BEGIN
 DECLARE UserId INT DEFAULT 0;
 DECLARE t_cursor CURSOR FOR 
 SELECT DISTINCT Id FROM table;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET t_done = TRUE;
 OPEN t_cursor;
 r_loop: LOOP
  FETCH t_cursor INTO UserId;
  IF t_done THEN
    LEAVE r_loop;
  END IF;
  IF UserId < 5 THEN
     -- How to move to the next iteration, that is, to the next UserId in cursor ?
  END IF;

  UPDATE Table set val = 1 where Id = UserID;
 END LOOP;
 CLOSE t_cursor;
 END;

Is there a command to go to the next iteration? I understand that you can get by with ELSEIF, but rewriting a lot of code, it's easier to put a condition first and if the id does not match the condition, check the next id

CodePudding user response：

CREATE PROCEDURE test ()
 BEGIN
 DECLARE UserId INT DEFAULT 0;
 DECLARE t_done BOOLEAN DEFAULT FALSE;                     -- variable declared
 DECLARE t_cursor CURSOR FOR 
 SELECT DISTINCT Id FROM /* table */ test;                 -- tablename changed
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET t_done = TRUE;
 OPEN t_cursor;
 r_loop: LOOP
  FETCH t_cursor INTO UserId;
  IF t_done THEN
    LEAVE r_loop;
  END IF;
  IF UserId < 5 THEN
     -- How to move to the next iteration, that is, to the next UserId in cursor ?
     ITERATE r_loop;                                       -- ITERATE added
  END IF;

  UPDATE /* Table */ test set val = 1 where Id = UserID;   -- tablename changed
 END LOOP r_loop;                                          -- label added
 CLOSE t_cursor;
 END;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=22361369a6bd47d2ef514d3ca4010fb4