Question 291934 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL/MariaDB Create multiple inserts from function

In an SQL function I want to create multiple records based on the parameters passed into the function, for example the function takes a member primary ID, a date/time stamp and a number specifying the number of records to create.

I could of course create a loop to perform the inserts, however I am curious to know if there is a way to get the server engine to create a specific number of records automatically ?

My function so far:

CREATE DEFINER=`root`@`localhost` FUNCTION `reserveTickets`(idMbr BIGINT(11),                                                          
                           dtWhen DATETIME, intTickets INT(11)) RETURNS varchar(24) CHARSET utf8mb4 COLLATE utf8mb4_general_ci
BEGIN
    DECLARE dtStartRef DATETIME;
    DECLARE idMbrVerified BIGINT(11);
    DECLARE intTicket INT(11);
/*Initialise reference time*/    
    SET dtStartRef = NOW(3);
/*Is member id valid?*/
    SELECT id INTO idMbrVerified FROM tblMembers WHERE id=idMbr;
    IF ISNULL(idMbrVerified) THEN 
        RETURN 0;
    END IF;    
/*Is date/time valid, can't be older that 60 seconds?*/
    IF SECOND(TIMEDIFF(NOW(), dtWhen)) > 60 THEN
        RETURN 0;
    END IF;
/*Is the number of tickets valid, must be at least 1?*/    
    IF intTickets <= 0 THEN
        RETURN 0;    
    END IF;
/*Create the records*/  
    SET intTicket = 0;
    insertTicket: LOOP
        IF intTicket < intTickets THEN
            INSERT INTO tblTickets (idMbr, dtWhen) VALUES (idMbr, dtWhen);
        ELSE
            LEAVE insertTicket;
        END IF;
        SET intTicket = intTicket + 1;
    END LOOP insertTicket;    
    RETURN TIMESTAMPDIFF(MICROSECOND, dtStartRef, NOW(3)) / 100000;
END
Tags:

2

Answers


  1. 0

    Can you try below adding instead of using RETURN 1;?

    WITH RECURSIVE numbers AS (
 SELECT 1 AS number
 UNION ALL
 SELECT number + 1 FROM numbers WHERE number < intTickets
 )
INSERT INTO tblTickets(member_id, timestamp)
SELECT idMbr, dtWhen
FROM numbers;
RETURN ROW_COUNT();
    Login or Signup to reply.
  2. 0

    Here’s another solution. I create a JSON array with a number of elements equal to the tickets requested. It doesn’t matter what values are in this array, it’s the length of the array that is important.

    I convert that array to a set of rows using JSON_TABLE(), and use that as a source for an INSERT...SELECT statement, inserting a set of rows in one statement, without using a loop.

    By joining to the tblMembers table, it will produce zero rows if there is no member matching the input.

    I also followed a habit of naming the function arguments differently than any columns referenced by the query.

    CREATE FUNCTION reserveTickets(p_idMbr BIGINT, p_dtWhen DATETIME, p_intTickets INT)
RETURNS VARCHAR(24)
MODIFIES SQL DATA
BEGIN
    DECLARE dtStartRef DATETIME;
    DECLARE ordinals JSON;

    SET dtStartRef = SYSDATE(3);
    SET ordinals = CAST(CONCAT('[', REPEAT('1,', p_intTickets-1), '1]') AS JSON);

    INSERT INTO tblTickets(idMbr, dtWhen)
    SELECT p_idMbr, p_dtWhen
    FROM tblMembers
    CROSS JOIN JSON_TABLE(ordinals, '$[*]' COLUMNS(ord FOR ORDINALITY)) AS j
    WHERE tblMembers.id = p_idMbr;

    RETURN TIMESTAMPDIFF(MICROSECOND, SYSDATE(3), dtStartRef);
END

    I tested this with MySQL 8.2. In my test, the timestampdiff returns wildly erratic values, sometimes even resulting a negative diff. I’m not going to attempt to explain that.

    But the time reported by the client is consistently about 0.24 or 0.25 seconds.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search