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