2. Write a script that creates and calls a stored procedure named test. This pro

Question

2. Write a script that creates and calls a stored procedure named test. This procedure should include these statements coded as a transaction: (Two Screenshots: Script and Executed Results) 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; INSERT INTO order_items VALUES (DEFAULT, order_id, 6, '415.00', '161.85', 1); INSERT INTO order_items VALUES (DEFAULT, order_id, 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.

Explanation / Answer

CREATE PROCEDURE test()
BEGIN
DECLARE sql_error TINYINT DEFAULT FALSE;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET sql_error = TRUE;

START 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;

INSERT INTO order_items VALUES
(DEFAULT, order_id, 6, '415.00', '161.85', 1);

INSERT INTO order_items VALUES
(DEFAULT, order_id, 1, '699.00', '209.70', 1);

IF sql_error = FALSE THEN
COMMIT;
SELECT ‘The transaction was committed.’;

END IF;
END//

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.