CLOSE is the final cursor step. After a cursor has been opened and its rows fetched, CLOSE ends cursor use and releases the cursor contents from server memory. section 22.2.1
Place CLOSE at the end of the cursor’s work:
- Declare the cursor and its query. section 22.2.1
- OPEN the cursor, FETCH rows, and process them one at a time. section 22.2.1
- CLOSE the cursor after the loop finishes. section 22.2.2
Closing a cursor in a stored procedure
After the GetOrders loop finishes, the procedure uses CLOSE AllOrders;. The loop has already fetched each OrderID and added qualifying title quantities; CLOSE now releases the cursor’s contents from memory. The procedure then ends, and its output can be inspected by calling it and selecting @TotalQuantitySold. section 22.2.2
DELIMITER //
CREATE PROCEDURE GetTitleTotalQuantitySoldListPrice(
OUT _TotalQuantitySold int
)
BEGIN
DECLARE _Done boolean DEFAULT FALSE;
DECLARE _OrderID int;
DECLARE AllOrders CURSOR FOR
SELECT OrderID
FROM orderheader;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _Done = TRUE;
SET _TotalQuantitySold = 0;
OPEN AllOrders;
GetOrders: LOOP
FETCH AllOrders INTO _OrderID;
SET _TotalQuantitySold = _TotalQuantitySold +
(SELECT COALESCE(SUM(Quantity),0)
FROM title t
INNER JOIN orderitem oi
ON t.TitleID = oi.TitleID
AND t.Price = oi.ItemPrice
WHERE oi.OrderID = _OrderID
);
IF _Done = TRUE THEN
LEAVE GetOrders;
END IF;
END LOOP GetOrders;
CLOSE AllOrders;
END //
DELIMITER ;
Browse the complete MySQL stored procedure to see CLOSE AllOrders placed after END LOOP and before END. section 22.2.2
