OPEN activates a declared cursor. It executes the cursor’s SELECT query, stores the resulting data set in server memory, and prepares it for row-by-row retrieval with FETCH. section 22.2.1
Use OPEN as the second step in the cursor sequence:
- DECLARE defines the cursor and its SELECT query. section 22.2.1
- OPEN executes that query and makes its result available. section 22.2.1
- FETCH retrieves rows one at a time for processing. section 22.2.1
- CLOSE releases the cursor’s contents from server memory. section 22.2.1
Opening AllOrders
After declaring AllOrders for SELECT OrderID FROM orderheader, OPEN AllOrders executes that query. A LOOP can then FETCH each OrderID, process it, and eventually CLOSE the cursor. 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 ;
This complete procedure places OPEN between cursor declaration and the FETCH loop, then closes the cursor after processing every order. section 22.2.2
