How to dynamically add value in a mySQL procedure?-CodePudding

I have the following procedure:

DROP PROCEDURE IF EXISTS insertCapacityIntoADay;
DELIMITER //
CREATE PROCEDURE insertCapacityIntoADay(startTime TIME, endTime TIME, capacity INT)
BEGIN
    INSERT INTO Capacities VALUES(UUID(), startTime, endTime, capacity;
END //
DELIMITER ;

So I can use it like this:

CALL insertCapacityIntoADay('00:00:00', '08:00:00', 120);

My question is, how can I define my procedure, so that when I call it with multiple startTime, endTime, capacity, it adds the value multiple times? i.e. if I do:

CALL insertCapacityIntoADay('00:00:00', '08:00:00', 120, '08:00:00', '20:00:00', 200, '20:00:00', '24:00:00', 150);

it should be equivalent to:

CALL insertCapacityIntoADay('00:00:00', '08:00:00', 120);
CALL insertCapacityIntoADay('08:00:00', '20:00:00', 200);
CALL insertCapacityIntoADay('20:00:00', '24:00:00', 150);

CodePudding user response：

You can emulate array parameters with a CSV string, kind of

CREATE PROCEDURE insertCapacityIntoADay(startTimes varchar(1000), endTimes varchar(1000), capacity varchar(1000))
...

CALL insertCapacityIntoADay('00:00:00, 08:00:00, 20:00:00', '08:00:00, 20:00:00, 24:00:00', '120, 200, 150')

In the proc create a loop which parses the parameters, and issues INSERT accordingly.

CodePudding user response：

I worked out a solution:

DROP PROCEDURE IF EXISTS insertCapacityIntoADay;
DELIMITER //
CREATE PROCEDURE insertCapacityIntoADay(capacities TEXT)
BEGIN
    DECLARE startTime VARCHAR(90);
    DECLARE endTime VARCHAR(90);
    DECLARE capacity VARCHAR(90);
    DECLARE maxIterations INT;
    DECLARE i INT;
    SET maxIterations = 3;
    SET i = 1;
    -- use a loop to insert values 3 times
    loop_label: LOOP
        IF i > maxIterations THEN
          LEAVE loop_label;
        END IF;
        -- separate values using comma
        SET startTime = (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(capacities, ",", i), ',', -1));
        SET endTime = (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(capacities, ",", i   1), ',', -1));
        SET capacity = (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(capacities, ",", i   2), ',', -1));
        INSERT INTO Capacities VALUES(UUID(), startTime, endTime, capacity);
        SET i = i   3;         
        ITERATE loop_label;
    END LOOP loop_label;
END//
DELIMITER ;

SET @capacity1= '00:00:00, 08:00:00, 80, 08:00:00, 15:00:00, 200, 15:00:00, 20:00:00, 150';
CALL insertCapacityIntoADay(@capacity1);