A SQL procedural loop repeats a block of statements while work remains. With a MySQL cursor, the loop fetches one row at a time, performs calculations or updates, and stops when a handler signals that no rows remain. section 22.2.2
Summing qualifying order quantities
A stored procedure opens an AllOrders cursor, enters the named GetOrders loop, and fetches each OrderID. For every order, a query adds quantities sold at the title's list price to _TotalQuantitySold. When the NOT FOUND handler sets _Done to TRUE, IF _Done = TRUE triggers LEAVE GetOrders; the procedure then closes the cursor.
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 procedure from delimiter setup through cursor declaration, fetch processing, termination, cleanup, and the procedure call. section 22.2.2
Loop requirements differ among relational database products. MySQL requires LOOP for these cursors and uses LEAVE to exit them, so check the target system before reusing the syntax. section 22.2.2
