mysql中使用for循环

DELIMITER //

CREATE PROCEDURE IncrementAge()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE userId INT;
    DECLARE userAge INT;
    
    -- 假设我们有一个游标来遍历users表中的每一行
    DECLARE cur CURSOR FOR SELECT id, age FROM users;
    
    -- 声明一个继续处理的标志
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO userId, userAge;
        
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 更新用户的年龄
        UPDATE users SET age = userAge + 1 WHERE id = userId;
    END LOOP;
    
    CLOSE cur;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE IncrementAgeBatch(IN batchSize INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE userId INT;
    DECLARE userAge INT;
    DECLARE batchCount INT DEFAULT 0;
    
    DECLARE cur CURSOR FOR SELECT id, age FROM users LIMIT batchSize;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO userId, userAge;
        
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        UPDATE users SET age = userAge + 1 WHERE id = userId;
        SET batchCount = batchCount + 1;
        
        IF batchCount >= batchSize THEN
            SET batchCount = 0;
            -- 可以在这里添加一些日志记录或其他处理
        END IF;
    END LOOP;
    
    CLOSE cur;
END //

DELIMITER ;