A cursor lets SQL process the results of a SELECT query one row at a time. Think of a query result as a spreadsheet: instead of working with the whole sheet at once, a cursor points to one row, lets you work with it, then moves to the next. This is useful when each row needs an individual action or decision. section 22.2 section 22.0
In MySQL, cursors are used inside prepared-SQL database objects such as stored procedures. They are more involved than a view or simple stored procedure because every cursor follows a required sequence: declare it, open it, fetch rows, and close it. section 22.0 section 22.2.1
A working cursor has four parts, in this order:
- DECLARE defines the cursor and its SELECT query; the query does not run yet. section 22.2.1
- OPEN runs the query and makes its result set available in server memory. section 22.2.1
- FETCH retrieves the next row into variables so a loop can evaluate or act on that row. section 22.2.1
- CLOSE releases the cursor contents from server memory after processing finishes. section 22.2.1
Counting titles sold at list price
A procedure can declare a cursor over OrderID values, fetch one order at a time, and add quantities for items whose item price matches the title’s listed price. A NOT FOUND handler sets a Boolean flag when there are no more rows, allowing the loop to end before the cursor closes. The final total is returned through an output parameter. section 22.2.2 section 22.2.1
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 MySQL procedure shows the cursor lifecycle alongside variables, a NOT FOUND handler, a loop, and an output total. section 22.2.2
Use cursors cautiously. SQL is designed to evaluate sets of rows, whereas cursors prescribe each row-by-row step. For many requests, a single set-based query using joins, aggregates, subqueries, or CASE is simpler and can use fewer server resources. First ask whether the result can be produced as a set; reserve a cursor for work that truly must send one value at a time to another procedure. section 22.4 section 22.4.1 section 22.6
When reading an existing stored procedure, treat a cursor as a candidate for replacement with a set-based solution. Replacing unnecessary row-by-row work can reduce complexity, improve performance, and reduce blocking and server resource use. section 22.4.2 section 22.4.1
