MySQL Stored Procedure : Insert Data in Header and Detail within 1 Transaction

This is Generated by ChatGPT 2023-11-01

To insert data into a single-row header table and multiple-row detail table using a stored procedure in MySQL, you can follow these steps. In this example, we'll create two tables: header and detail, with a one-to-many relationship, and then create a stored procedure to insert data into both tables.

Assuming you have the following tables:

CREATE TABLE header (

    header_id INT AUTO_INCREMENT PRIMARY KEY,

    header_data VARCHAR(255)

);

CREATE TABLE detail (

    detail_id INT AUTO_INCREMENT PRIMARY KEY,

    header_id INT,

    detail_data VARCHAR(255),

    FOREIGN KEY (header_id) REFERENCES header(header_id)

);

Here's a stored procedure to insert data into both tables:

DELIMITER $$

CREATE PROCEDURE InsertHeaderAndDetails(

    IN headerData VARCHAR(255),

    IN detailDataArray JSON

)

BEGIN

    DECLARE done INT DEFAULT FALSE;

    DECLARE detailValue VARCHAR(255);

    DECLARE cur CURSOR FOR SELECT value FROM JSON_TABLE(detailDataArray, '$[*]' COLUMNS(value VARCHAR(255) PATH '$'));

    

    -- Insert data into the header table

    INSERT INTO header (header_data) VALUES (headerData);

    SET @lastHeaderID = LAST_INSERT_ID(); -- Get the last inserted header ID

    -- Insert data into the detail table

    OPEN cur;

    read_loop: LOOP

        FETCH cur INTO detailValue;

        IF done THEN

            LEAVE read_loop;

        END IF;

        INSERT INTO detail (header_id, detail_data) VALUES (@lastHeaderID, detailValue);

    END LOOP;

    CLOSE cur;

END $$

DELIMITER ;

 

This stored procedure takes two parameters:

headerData: The data to be inserted into the header table.

detailDataArray: A JSON array containing the data to be inserted into the detail table.

You can call the stored procedure like this:

CALL InsertHeaderAndDetails('Header Data', '["Detail 1", "Detail 2", "Detail 3"]');