mysql - 如何在 MySQL 中的 Cursor 中后退一步

Question

使用后有什么办法可以向后移动

FETCH NEXT FROM cursor_clockIn INTO n_Id, n_TimeStamp, n_WorkDayId, n_EmployeeId, n_Type;

当例程完成迭代时，我需要回到之前的位置。根据文档，光标是一个方向。那么，在这种情况下我能做些什么呢？

以下是我的程序：

DELIMITER //
DROP PROCEDURE IF EXISTS calculation //

CREATE PROCEDURE calculation (IN workDayId INT)
BEGIN 
    DECLARE TimeSpan INT DEFAULT 0;
    DECLARE BreakTime INT DEFAULT 0;
    DECLARE Id INT DEFAULT 0;
        DECLARE c_Id INT DEFAULT 0;
            DECLARE c_TimeStamp DateTime;
                DECLARE c_WorkDayId INT DEFAULT 0;
                    DECLARE c_EmployeeId INT DEFAULT 0;
                        DECLARE c_Type VARCHAR(10);
                            DECLARE n_TimeStamp DateTime;
                                DECLARE n_Id INT DEFAULT 0;
                                    DECLARE n_WorkDayId INT DEFAULT 0;
                                        DECLARE n_EmployeeId INT DEFAULT 0;
                                            DECLARE n_Type VARCHAR(10);
                                                    DECLARE cur_Type VARCHAR(100) DEFAULT "";
                                                    DECLARE dd DATETIME;
    DECLARE done INT DEFAULT FALSE;
    DECLARE cursor_clockIn CURSOR FOR SELECT c.Id, c.TimeStamp, c.WorkDayId, c.EmployeeId, c.`Type` FROM clockintest c WHERE c.WorkDayId = Id ORDER BY c.TimeStamp;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    SET Id = workDayId;
    OPEN cursor_clockIn;
    SET TimeSpan = 0;
    SET BreakTime = 0;
    loop_through_rows: LOOP
        FETCH cursor_clockIn INTO c_Id, c_TimeStamp, c_WorkDayId, c_EmployeeId, c_Type;
        FETCH NEXT FROM cursor_clockIn INTO n_Id, n_TimeStamp, n_WorkDayId, n_EmployeeId, n_Type;
    
        IF done THEN 
            LEAVE loop_through_rows;
        END IF;
        
        IF c_Type = 'Start' THEN
            SET TimeSpan = TimeSpan + TIMESTAMPDIFF(MICROSECOND, c_TimeStamp, n_TimeStamp) / 1000;
        END IF;
        
        IF c_Type = 'End' THEN
            SET BreakTime = BreakTime + TIMESTAMPDIFF(MICROSECOND, c_TimeStamp, n_TimeStamp) / 1000;
        END IF;

        SET cur_Type = CONCAT(c_Id, ',' );
       -- I need to back one step in cursor here
    END LOOP;
    SELECT TimeSpan, BreakTime, cur_Type;
        UPDATE workday w SET w.TimeSpan = TimeSpan, w.BreakTime = BreakTime WHERE w.Id = workDayId;

    CLOSE cursor_clockIn;

END //

DELIMITER ;

标签： mysqldatabasestored-proceduresdatabase-cursor