Write a script that creates and calls a stored procedure named test. This procedure should include these statements coded as a transaction:
INSERT INTO orders VALUES
(DEFAULT, 3, NOW(), '10.00', '0.00', NULL, 4,
'American Express', '378282246310005', '04/2016', 4);
SELECT LAST_INSERT_ID()
INTO order_id_var;
INSERT INTO order_items VALUES
(DEFAULT, order_id_var, 6, '415.00', '161.85', 1);
INSERT INTO order_items VALUES
(DEFAULT, order_id_var, 1, '699.00', '209.70', 1);
Here, the LAST_INSERT_ID function is used to get the order ID value that’s automatically generated when the first INSERT statement inserts an order.
If these statements execute successfully, commit the changes. Otherwise, roll back the changes.