MySQL - Cursor

MySQL - Cursors

What is a Cursor?

A cursor is a database object that allows you to process the result set of a SELECT statement one row at a time. It acts like a pointer, moving through the records sequentially. Cursors are typically used inside stored procedures, functions, or triggers.

In MySQL, cursors have the following characteristics:

• Read-Only: You cannot update or delete records through a standard MySQL cursor.
• Non-Scrollable: You can only move forward through the result set, one row at a time. You cannot go backward or jump to a specific row.
• Asensitive: Cursors are ‘asensitive’, meaning they operate on a snapshot of the data from when the cursor was opened. Changes made to the base tables by other connections might not be visible to the cursor.

When to Use (and More Importantly, When to AVOID) Cursors

Golden Rule: SQL is designed for set-based operations. You should always try to solve problems with a single SQL statement (UPDATE, INSERT ... SELECT, etc.) before resorting to a cursor. Cursors are procedural (row-by-row) and generally much slower than set-based operations.

When to AVOID a cursor:

• Data Transformation/Copying: Never use a cursor to copy data from one table to another. A simple INSERT INTO ... SELECT ... is orders of magnitude faster.
• Simple Updates: If you can update multiple rows with a single UPDATE ... WHERE ... statement, do not use a cursor to loop through them.

Legitimate (but rare) use cases for a cursor:

• Complex Sequential Logic: When you need to perform a complex, multi-step calculation for each row that depends on the result of the previous row (e.g., complex running totals, dynamic simulations).
• Calling External Procedures: When you need to iterate through rows and call another stored procedure or external process for each one.

The Cursor Lifecycle: Declare, Open, Fetch, Close

Managing a cursor involves four main steps:

1. DECLARE: Define the cursor and associate it with a SELECT statement. This must be done after variable declarations.
2. OPEN: Execute the SELECT statement and populate the result set for the cursor to iterate over.
3. FETCH: Retrieve the next row from the result set and advance the cursor’s pointer. The values are typically stored in local variables.
4. CLOSE: Deactivate the cursor and release the memory it was using. It’s crucial to close cursors to avoid resource leaks.

A Corrected Cursor Example: Calculating Running Totals

Let’s look at a scenario where a cursor might be considered: calculating a running total of salaries and storing it. (Note: Modern MySQL versions can also do this with Window Functions like SUM() OVER (...), which are far more efficient. This example is for demonstrating cursor mechanics.)

-- Setup a sample table
CREATE TABLE Employees (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(100) NOT NULL,
    SALARY DECIMAL(10, 2) NOT NULL
);
INSERT INTO Employees(NAME, SALARY) VALUES
('Alice', 50000), ('Bob', 60000), ('Charlie', 55000);

-- Create a table to store results
CREATE TABLE SalaryRunningTotals (
    employee_name VARCHAR(100),
    salary DECIMAL(10, 2),
    running_total DECIMAL(12, 2)
);

-- The Stored Procedure with the cursor
DELIMITER //

CREATE PROCEDURE CalculateRunningTotal()
BEGIN
    -- 1. Variable Declarations
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_name VARCHAR(100);
    DECLARE emp_salary DECIMAL(10, 2);
    DECLARE current_total DECIMAL(12, 2) DEFAULT 0.00;

    -- 2. Cursor Declaration
    DECLARE cur CURSOR FOR
        SELECT NAME, SALARY FROM Employees ORDER BY ID;

    -- 3. Handler for when no more rows are found
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 4. Open the cursor
    OPEN cur;

    -- 5. Loop through the rows
    read_loop: LOOP
        -- Fetch the next row
        FETCH cur INTO emp_name, emp_salary;

        -- IMPORTANT: Check if the loop should exit *before* processing
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Process the row
        SET current_total = current_total + emp_salary;
        INSERT INTO SalaryRunningTotals(employee_name, salary, running_total)
        VALUES (emp_name, emp_salary, current_total);

    END LOOP read_loop;

    -- 6. Close the cursor
    CLOSE cur;
END //

DELIMITER ;

Execution and Output

Call the procedure and then check the results table.

CALL CalculateRunningTotal();
SELECT * FROM SalaryRunningTotals;

employee_name	salary	running_total
Alice	50000.00	50000.00
Bob	60000.00	110000.00
Charlie	55000.00	165000.00

Anti-Pattern: Why You Should Not Use a Cursor to Copy Data

A common mistake is using a cursor to copy data. The original tutorial showed this. Let’s see why it’s wrong and how to do it correctly.

-- THE WRONG WAY (with a cursor)
-- This involves opening a cursor, looping through every single row,
-- fetching it into variables, and then inserting it. It's slow and complex.

-- THE RIGHT WAY (set-based)
CREATE TABLE CUSTOMERS_BACKUP LIKE CUSTOMERS;
INSERT INTO CUSTOMERS_BACKUP SELECT * FROM CUSTOMERS;

The set-based INSERT ... SELECT statement is a single, highly optimized operation. It will outperform the cursor-based approach by a huge margin, especially on large tables.

Common Pitfalls and Debugging

• Infinite Loops: Forgetting to declare a NOT FOUND handler is a common cause of infinite loops.
• Off-by-One Errors: The LEAVE statement must be placed immediately after the FETCH. If you process the data before checking the done flag, you will process the last row twice.
• Forgetting to CLOSE: Always close your cursors to free up server resources. Open cursors can hold locks and consume memory.
• Performance Blindness: Assuming a cursor is necessary. Always ask: “Can I do this with a single SQL statement?” Use EXPLAIN to analyze query performance.