What is a cursor in MySQL?

In MySQL, a cursor is a database object that allows you to retrieve and manipulate rows from a result set one at a time. Unlike a normal SELECT query that returns all rows at once, a cursor lets you process each row individually, which is useful in procedural code like stored procedures, functions, or triggers.

Here’s a detailed breakdown:

Key Points About Cursors

1. Purpose:
   • Used to handle multiple rows returned by a query in a row-by-row manner.
   • Essential when you need to perform operations on each row individually, such as updating, logging, or conditional processing.
2. Types of Cursors in MySQL:
   MySQL supports only explicit cursors in stored programs. There are no implicit cursors exposed to the user like in some other DBMS.
3. Cursor Operations:
   A typical cursor workflow involves these steps:
   1. Declare a cursor for a SELECT statement.
   2. Open the cursor to establish the result set.
   3. Fetch rows one by one from the cursor into variables.
   4. Process the data as required.
   5. Close the cursor to free resources.

Syntax Example

DELIMITER //

CREATE PROCEDURE process_employees()
BEGIN
    -- Declare variables to hold fetched data
    DECLARE emp_name VARCHAR(50);
    DECLARE emp_salary DECIMAL(10,2);

    -- Declare a cursor for selecting employees
    DECLARE emp_cursor CURSOR FOR 
        SELECT name, salary FROM employees;

    -- Declare a handler for when no more rows are left
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Open the cursor
    OPEN emp_cursor;

    -- Loop through each row
    read_loop: LOOP
        FETCH emp_cursor INTO emp_name, emp_salary;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Example operation: print employee info
        SELECT CONCAT('Employee: ', emp_name, ', Salary: ', emp_salary);
    END LOOP;

    -- Close the cursor
    CLOSE emp_cursor;
END //

DELIMITER ;

Advantages

• Allows row-by-row processing in stored procedures.
• Can perform complex logic on each row that can’t be done easily with a single SQL query.
• Useful for conditional updates or aggregations that are procedural in nature.

Limitations

• Slower than set-based operations (UPDATE/INSERT with WHERE) because it processes one row at a time.
• Must be used inside stored procedures, functions, or triggers.

✅ Summary:
A cursor in MySQL is a tool for iterating over query results row by row, primarily in procedural SQL blocks. It’s powerful for detailed row-wise processing but should be used carefully due to performance considerations.