FETCH retrieves the next row from an open cursor and assigns its column values to variables. It is the step that lets a stored procedure work with one result row at a time. section 22.2.1
Use FETCH as part of an ordered cursor process:
- DECLARE the cursor with the SELECT query that defines its result set. section 22.2.1
- OPEN the cursor so the SELECT query executes and its results become available. section 22.2.1
- FETCH one row into one or more variables, then evaluate or modify data using those values. section 22.2.1 section 23.2.2
- Repeat FETCH inside a loop until a NOT FOUND handler marks the end of the result set. section 22.2.2
- CLOSE the cursor after processing the rows to release its contents from server memory. section 22.2.1
Fetching order IDs
A MySQL procedure declares a cursor named AllOrders for OrderID values, opens it, and repeatedly executes FETCH AllOrders INTO _OrderID. Each fetched order ID is used in a query that adds qualifying title quantities to _TotalQuantitySold. A NOT FOUND handler sets _Done to TRUE, and the loop exits when no rows remain. section 22.2.2
Fetching two columns
A second procedure fetches TitleID and AuthorID together with FETCH AllTitles INTO _TitleID, _AuthorID. Later statements use those variables to calculate royalty, author counts, and sales before inserting an author payment. section 23.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 ;
Inspect the complete stored procedure to see FETCH positioned between the cursor loop and the calculations performed for each order. section 22.2.2
