Home > Mobile >  How to move back after FETCH NEXT in stored procedure while using CURSOR in MySQL
How to move back after FETCH NEXT in stored procedure while using CURSOR in MySQL

Time：11-02

Following is my table:

CREATE TABLE IF NOT EXISTS `clockintest` (
  `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `PartnerId` bigint(20) unsigned DEFAULT NULL,
  `TimeStamp` datetime DEFAULT NULL,
  `City` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Street` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Postalcode` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Country` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `CommissionId` bigint(20) unsigned DEFAULT NULL,
  `WorkDayId` bigint(20) unsigned DEFAULT NULL,
  `UserId` bigint(20) unsigned DEFAULT NULL,
  `WorkDescription` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `EmployeeId` bigint(20) unsigned DEFAULT NULL,
  `Type` enum('Start','End') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Kostenstelle` int(11) DEFAULT NULL,
  `Sachkonto` int(11) DEFAULT NULL,
  `Bearbeitungsschluessel` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `CreatedAt` datetime DEFAULT current_timestamp(),
  `UpdatedAt` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `DeletedAt` datetime DEFAULT NULL,
  `ExportedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Id` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=712 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table basedb.clockintest: ~6 rows (approximately)
DELETE FROM `clockintest`;
/*!40000 ALTER TABLE `clockintest` DISABLE KEYS */;
INSERT INTO `clockintest` (`Id`, `PartnerId`, `TimeStamp`, `City`, `Street`, `Postalcode`, `Country`, `CommissionId`, `WorkDayId`, `UserId`, `WorkDescription`, `EmployeeId`, `Type`, `Kostenstelle`, `Sachkonto`, `Bearbeitungsschluessel`, `CreatedAt`, `UpdatedAt`, `DeletedAt`, `ExportedAt`) VALUES
    (645, 1, '2021-10-26 08:00:00', NULL, NULL, NULL, NULL, NULL, 149, 1, NULL, 1, 'Start', NULL, NULL, NULL, '2021-10-27 11:41:29', '2021-10-27 11:43:20', NULL, NULL),
    (649, 1, '2021-10-26 08:00:00', NULL, NULL, NULL, NULL, NULL, 150, 1, NULL, 1, 'Start', NULL, NULL, NULL, '2021-10-27 11:41:29', '2021-10-29 13:02:50', NULL, NULL),
    (680, 1, '2021-10-26 10:00:00', NULL, NULL, NULL, NULL, NULL, 149, 1, NULL, 1, 'End', NULL, NULL, NULL, '2021-10-27 11:41:29', '2021-10-28 08:46:05', NULL, NULL),
    (684, 1, '2021-10-26 12:00:00', NULL, NULL, NULL, NULL, NULL, 149, 1, NULL, 1, 'Start', NULL, NULL, NULL, '2021-10-27 11:41:29', '2021-10-29 07:37:51', NULL, NULL),
    (709, 1, '2021-10-26 23:59:00', NULL, NULL, NULL, NULL, NULL, 149, 1, NULL, 1, 'End', NULL, NULL, NULL, '2021-10-29 09:52:07', '2021-10-29 09:52:07', NULL, NULL),
    (710, 1, '2021-10-26 23:59:00', NULL, NULL, NULL, NULL, NULL, 150, 1, NULL, 1, 'End', NULL, NULL, NULL, '2021-10-29 09:52:07', '2021-10-29 13:02:44', NULL, NULL);

Depending on this table I have wrote down a Stored Procedure in MySQL. I used cursor for retrieving the rows and for the calculation. But, I need to calculate between two rows and for that I need to move next and move backwards. I achieved the next purpose using FETCH NEXT.

But, the problem is that after moving FETCH NEXT, I am iterating two rows in a single loop execution. That is why when I have 4 rows (I should iterate 4 times the loop), but I am iterating only 2 times.

How can I solve this? Any help? Following in my Stored Procedure.

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 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;
        BEGIN
        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(cur_Type,';', c_Id );
            END;
    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 ;

CALL calculation (149);

CodePudding user response：

Ok that was tricky

i changed your procedure.

The cursor now uses the variable directly, your construch doesn't give any errors but also doesn't produce the correct result

The first fetch is now outside the loop so the whole thing gets intialised.

The add the end of the summarizing, i added the the pat where i set all c_... variables to the value of n_... so that the routine can go to the next loop

Also as you can see a good indentation helps to grasp the concept better as you can see where something starts and ends. This is also a good advise for the future

CREATE TABLE IF NOT EXISTS `clockintest` (
  `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `PartnerId` bigint(20) unsigned DEFAULT NULL,
  `TimeStamp` datetime DEFAULT NULL,
  `City` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Street` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Postalcode` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Country` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `CommissionId` bigint(20) unsigned DEFAULT NULL,
  `WorkDayId` bigint(20) unsigned DEFAULT NULL,
  `UserId` bigint(20) unsigned DEFAULT NULL,
  `WorkDescription` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `EmployeeId` bigint(20) unsigned DEFAULT NULL,
  `Type` enum('Start','End') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Kostenstelle` int(11) DEFAULT NULL,
  `Sachkonto` int(11) DEFAULT NULL,
  `Bearbeitungsschluessel` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `CreatedAt` datetime DEFAULT current_timestamp(),
  `UpdatedAt` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `DeletedAt` datetime DEFAULT NULL,
  `ExportedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Id` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=712 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table basedb.clockintest: ~6 rows (approximately)
DELETE FROM `clockintest`;
/*!40000 ALTER TABLE `clockintest` DISABLE KEYS */;
INSERT INTO `clockintest` (`Id`, `PartnerId`, `TimeStamp`, `City`, `Street`, `Postalcode`, `Country`, `CommissionId`, `WorkDayId`, `UserId`, `WorkDescription`, `EmployeeId`, `Type`, `Kostenstelle`, `Sachkonto`, `Bearbeitungsschluessel`, `CreatedAt`, `UpdatedAt`, `DeletedAt`, `ExportedAt`) VALUES
    (645, 1, '2021-10-26 08:00:00', NULL, NULL, NULL, NULL, NULL, 149, 1, NULL, 1, 'Start', NULL, NULL, NULL, '2021-10-27 11:41:29', '2021-10-27 11:43:20', NULL, NULL),
    (649, 1, '2021-10-26 08:00:00', NULL, NULL, NULL, NULL, NULL, 150, 1, NULL, 1, 'Start', NULL, NULL, NULL, '2021-10-27 11:41:29', '2021-10-29 13:02:50', NULL, NULL),
    (680, 1, '2021-10-26 10:00:00', NULL, NULL, NULL, NULL, NULL, 149, 1, NULL, 1, 'End', NULL, NULL, NULL, '2021-10-27 11:41:29', '2021-10-28 08:46:05', NULL, NULL),
    (684, 1, '2021-10-26 12:00:00', NULL, NULL, NULL, NULL, NULL, 149, 1, NULL, 1, 'Start', NULL, NULL, NULL, '2021-10-27 11:41:29', '2021-10-29 07:37:51', NULL, NULL),
    (709, 1, '2021-10-26 23:59:00', NULL, NULL, NULL, NULL, NULL, 149, 1, NULL, 1, 'End', NULL, NULL, NULL, '2021-10-29 09:52:07', '2021-10-29 09:52:07', NULL, NULL),
    (710, 1, '2021-10-26 23:59:00', NULL, NULL, NULL, NULL, NULL, 150, 1, NULL, 1, 'End', NULL, NULL, NULL, '2021-10-29 09:52:07', '2021-10-29 13:02:44', NULL, NULL);

CREATE TABLE workday (id int, TimeSpan bigint, BreakTime BIGINT )

INSERT INTO workday VALUES (149,0,0)

SELECT c.Id, c.TimeStamp, c.WorkDayId, c.EmployeeId, c.`Type` 
                       FROM clockintest c WHERE c.WorkDayId = 149 ORDER BY c.TimeStamp;

 Id | TimeStamp           | WorkDayId | EmployeeId | Type 
--: | :------------------ | --------: | ---------: | :----
645 | 2021-10-26 08:00:00 |       149 |          1 | Start
680 | 2021-10-26 10:00:00 |       149 |          1 | End  
684 | 2021-10-26 12:00:00 |       149 |          1 | Start
709 | 2021-10-26 23:59:00 |       149 |          1 | End  

DROP PROCEDURE IF EXISTS calculation ;

CREATE PROCEDURE calculation (IN workDayId INT)
BEGIN 
    DECLARE TimeSpan INT DEFAULT 0;
    DECLARE BreakTime 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 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 = workDayId ORDER BY c.TimeStamp;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cursor_clockIn;
    SET TimeSpan = 0;
    SET BreakTime = 0;
        FETCH cursor_clockIn INTO c_Id, c_TimeStamp, c_WorkDayId, c_EmployeeId, c_Type;
        SET @a = c_Id;  
    loop_through_rows: LOOP

        BEGIN
          
        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;
      SET @a = CONCAT(@a,';', n_id);      
        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(cur_Type,';', c_Id );
            SET c_Id = n_id;
            SET c_TimeStamp = n_TimeStamp;
            SET c_WorkDayId = n_WorkDayId;
            SET c_EmployeeId = n_EmployeeId;
            SET c_Type = n_Type;
            END;
    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 

CALL calculation (149);

TimeSpan | BreakTime | cur_Type    
-------: | --------: | :-----------
50340000 |   7200000 | ;645;680;684

✓

SELECT @a

| @a              |
| :-------------- |
| 645;680;684;709 |

SELECT * FROM workday

 id | TimeSpan | BreakTime
--: | -------: | --------:
149 | 50340000 |   7200000

db<>fiddle here

Page link：https//www.codepudding.com/Mobile/175946.html

Prev:Is there any way to get data type of XMLHttpRequest on base controller in .net core 3.1
Next:Recursive Quotation
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding