Firdaus Archive

Blog pribadi untuk curhat, share tentang script, software, kuliah, project IT dan seputar IT.

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

Tidak ada komentar

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"]');

This will insert a new row into the header table and multiple rows into the detail table with the corresponding header_id.

Please adjust the data types, table structures, and stored procedure parameters as needed to match your specific requirements. Additionally, ensure that you have proper error handling and validation in your production code for real-world scenarios.