CURSOR in Procedures
In this page:
What is a Cursor?
A cursor lets a stored procedure step through the rows of a query result one at a time, which is necessary when you need to run per-row logic that a single set-based SQL statement can't easily express.
Example: What is a Cursor?
DELIMITER //
CREATE PROCEDURE ListUserNames()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE uname TEXT;
DECLARE cur CURSOR FOR SELECT name FROM users;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO uname;
IF done THEN
LEAVE read_loop;
END IF;
SELECT uname;
END LOOP;
CLOSE cur;
END //
DELIMITER ;Opening and Fetching from a Cursor
Using a cursor starts with OPEN, and each subsequent FETCH copies the current row's column values into local variables so the procedure can inspect or act on that one row before moving to the next.
Example: Opening and Fetching from a Cursor
DELIMITER //
CREATE PROCEDURE FirstUserName()
BEGIN
DECLARE uname TEXT;
DECLARE cur CURSOR FOR SELECT name FROM users;
OPEN cur;
FETCH cur INTO uname;
CLOSE cur;
SELECT uname;
END //
DELIMITER ;Looping Through a Cursor
Wrapping a FETCH inside a loop lets a procedure walk through an entire result set row by row, typically paired with a handler that detects when there are no more rows left and exits the loop.
Example: Looping Through a Cursor
DELIMITER //
CREATE PROCEDURE SumAllPrices()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE p INT;
DECLARE total INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT price FROM products;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO p;
IF done THEN
LEAVE read_loop;
END IF;
SET total = total + p;
END LOOP;
CLOSE cur;
SELECT total;
END //
DELIMITER ;Closing the Cursor
Closing a cursor with CLOSE once you're finished releases the memory MySQL was holding for that result set — skipping this step on a long-running procedure can waste server resources.
Example: Closing the Cursor
DELIMITER //
CREATE PROCEDURE FirstUserName()
BEGIN
DECLARE uname TEXT;
DECLARE cur CURSOR FOR SELECT name FROM users;
OPEN cur;
FETCH cur INTO uname;
CLOSE cur; -- releases the result set MySQL was holding
SELECT uname;
END //
DELIMITER ;Multiple Cursors in a Procedure
A single procedure can declare and use several cursors at once, but they must be declared in the correct order — after any local variables, but before any handlers — or MySQL will raise a syntax error.
Example: Multiple Cursors in a Procedure
DELIMITER //
CREATE PROCEDURE TwoCursors()
BEGIN
DECLARE uname TEXT;
DECLARE pname TEXT;
DECLARE cur1 CURSOR FOR SELECT name FROM users;
DECLARE cur2 CURSOR FOR SELECT name FROM products;
OPEN cur1;
OPEN cur2;
FETCH cur1 INTO uname;
FETCH cur2 INTO pname;
CLOSE cur1;
CLOSE cur2;
SELECT uname, pname;
END //
DELIMITER ;Chapter Quiz — Complete all 5 topics to unlock
0/5 topics done
Complete these topics first: