Copyright

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

Question:

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; 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. If these statements execute successfully, commit the changes. Otherwise, roll back the changes.

Retrieving the Data From the Database:

To write the command that inserts the order to the database, you need to include a statement. If the statement is executed, use commit, and if the statement is not executed, then roll back the automatically generated changes to its original state.

Answer and Explanation:

This is the script that will create and call a stored procedure:

CREATE PROCEDURE test()

BEGIN

DECLARE sql_error TINYINT DEFAULT FALSE;

DECLARE...

See full answer below.

Become a Study.com member to unlock this answer! Create your account

View this answer

Learn more about this topic:

Loading...
Database Record: Definition & Explanation

from Relational Databases Tutorial

Chapter 2 / Lesson 3
16K

Recommended Lessons and Courses for You

Explore our homework questions and answers library