Cursors in Stored Procedures

A cursor allows you to iterate over the rows of a result set one at a time inside a stored procedure. This is useful when you need to perform row-by-row processing that cannot be expressed as a set-based SQL operation.

Cursor Lifecycle

DECLARE the cursor with a SELECT statement
DECLARE CONTINUE HANDLER FOR NOT FOUND to detect end of rows
OPEN the cursor to execute the SELECT
FETCH rows one at a time into variables
CLOSE the cursor when done

Performance Note

Cursor-based row-by-row processing is significantly slower than set-based SQL. Use cursors only when set-based logic is genuinely insufficient. Often a JOIN or subquery can replace a cursor entirely.

Example

DELIMITER //

CREATE PROCEDURE ProcessPendingOrders()
BEGIN
    DECLARE v_done INT DEFAULT 0;
    DECLARE v_order_id INT;
    DECLARE cur CURSOR FOR
        SELECT id FROM orders WHERE status = 'pending';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_order_id;
        IF v_done THEN LEAVE read_loop; END IF;
        CALL ApplyDiscount(v_order_id);
    END LOOP;
    CLOSE cur;
END //

DELIMITER ;

SQL

Full Editor

Pro Tip

Always declare the NOT FOUND handler before OPEN to safely detect end-of-cursor.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned