Write a named stored procedure that attempts to delete all the rows from order_details and orders...

 

Transcribed Image Text:

Write a named stored procedure that attempts to delete all the rows from order_details and orders tables when you pass it an order number. If you enter an invalid order_number and no rows were deleted, the system does not consider that to be an error. To check if a row is indeed deleted, use SQL%ROWCOUNT to return number of rows deleted. If the value of SQL%ROWCOUNT is zero, output a message stating that "No order rows were deleted. May not be a valid order_number". Otherwise, if the value is greater than zero, output a message that says "Order has been fully deleted.". Then, perform a commit. Once your procedure compiles correctly, test it with the following calls. --First Call-- CALL delete_detail_ID (10000); Sample Output: Order 10000 has been fully deleted. --Second and Third Calls-- CALL delete_detail_ID (10000); BEGIN END: / delete_detail_ID (11111); Sample Outputs: No order rows were deleted. May not be a valid order number Write a named stored procedure that attempts to delete all the rows from order_details and orders tables when you pass it an order number. If you enter an invalid order_number and no rows were deleted, the system does not consider that to be an error. To check if a row is indeed deleted, use SQL%ROWCOUNT to return number of rows deleted. If the value of SQL%ROWCOUNT is zero, output a message stating that "No order rows were deleted. May not be a valid order_number". Otherwise, if the value is greater than zero, output a message that says "Order has been fully deleted.". Then, perform a commit. Once your procedure compiles correctly, test it with the following calls. --First Call-- CALL delete_detail_ID (10000); Sample Output: Order 10000 has been fully deleted. --Second and Third Calls-- CALL delete_detail_ID (10000); BEGIN END: / delete_detail_ID (11111); Sample Outputs: No order rows were deleted. May not be a valid order number