Stored Procedures — Concept Check
Questions on Stored Procedures. Stored procedures encapsulate multi-statement logic in the DB; use IN/OUT/INOUT parameters, cursors, loops, and conditional logic to build server-side reusable routines.
Questions in this quiz
8 questions on Stored Procedures — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.A stored procedure is used to calculate the total revenue for a customer. What happens if the customer has no orders?
- AThe procedure returns zero for both total revenue and order count.
- BThe procedure raises an error due to NULL values.
- CThe procedure returns NULL for total revenue and zero for order count.
- DThe procedure returns zero for total revenue and NULL for order count.
2.In a stored procedure, what is the purpose of using a DECLARE CONTINUE HANDLER FOR NOT FOUND?
- ATo terminate the procedure when a cursor is exhausted.
- BTo skip over rows that do not meet criteria.
- CTo handle errors during SQL execution.
- DTo continue execution after a cursor is exhausted.
3.A stored procedure uses a cursor to iterate over customer orders. What happens if the cursor is not closed?
- AThe cursor remains open, potentially causing resource leaks.
- BThe cursor will close after the first iteration.
- CThe procedure will automatically close the cursor.
- DThe database will throw an error when the procedure ends.
4.When transferring funds between accounts using a stored procedure, what ensures the transaction is rolled back on error?
- AThe use of a cursor to iterate over transactions.
- BThe use of a DECLARE EXIT HANDLER FOR SQLEXCEPTION.
- CThe use of SIGNAL for error handling.
- DThe use of COMMIT at the end of the procedure.
5.A developer claims that stored procedures always improve application performance. Which scenario might contradict this belief?
- AStored procedures centralize business logic in the database.
- BStored procedures make application logic harder to test and deploy.
- CStored procedures use IN/OUT parameters for efficient data handling.
- DStored procedures reduce network round-trips for complex operations.
6.What is a potential drawback of using stored procedures in modern applications?
- AThey encapsulate multi-statement logic.
- BThey centralize business logic in the database.
- CThey use IN/OUT parameters for data exchange.
- DThey make application logic harder to version and deploy.
7.In a stored procedure, what is the role of the SIGNAL statement?
- ATo raise a custom error within the procedure.
- BTo iterate over a result set using a cursor.
- CTo declare local variables before logic execution.
- DTo handle transactions and commit changes.
8.A stored procedure is called to update customer tiers. What happens if the procedure logic is incorrect and updates tiers wrongly?
- AThe procedure will automatically correct the tiers on the next call.
- BThe procedure will raise an error and stop execution.
- CThe procedure will update tiers incorrectly without any error.
- DThe procedure will roll back all changes automatically.