What is a cursor in MySQL and when should you use it?

Question

Accepted Answer

72
   What is a cursor in MySQL and when should you use it?
  Level: Advanced — Cursors enable row-by-row processing, but must be used carefully for performance.

  A cursor is a database object that lets you iterate through a result set one row at a time, processing each row individually inside a stored procedure or function. It's the SQL equivalent of a loop over query results.

  
     Performance warning: SQL is designed for set-based operations (process all rows at once). Cursors are row-by-row (procedural) and are usually much slower. Prefer set-based queries whenever possible; use cursors only when row-by-row logic is truly unavoidable.
  

   The Four Steps to Use a Cursor
  
     DECLARE: Define the cursor with its SELECT query.
     OPEN: Execute the query and establish the result set.
     FETCH: Retrieve the next row into variables (usually in a loop).
     CLOSE: Release the cursor and free resources.
  

  
     Prerequisite: Cursors only work inside stored programs (procedures/functions/triggers). You also need a CONTINUE HANDLER for NOT FOUND to detect when there are no more rows and exit the loop.
  

   Cursor Properties in MySQL
  
    
      
        
          Property
          Behaviour
        
      
      
        
          Read-only
          Cannot update data through the cursor
        
        
          Non-scrollable
          Moves forward only (no going back)
        
        
          Asensitive
          May or may not reflect live changes
        
      
    
  

   Quick Example
DELIMITER $$
CREATE PROCEDURE ProcessSalaries()
BEGIN
    -- Variables to hold each row + a loop-done flag
    DECLARE v_done INT DEFAULT 0;
    DECLARE v_id INT;
    DECLARE v_salary DECIMAL(10,2);

    -- 1) DECLARE the cursor
    DECLARE cur CURSOR FOR
        SELECT emp_id, salary FROM employees;

    -- Handler: when no more rows, set v_done = 1
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

    -- 2) OPEN the cursor
    OPEN cur;

    read_loop: LOOP
        -- 3) FETCH the next row
        FETCH cur INTO v_id, v_salary;
        IF v_done = 1 THEN
            LEAVE read_loop;
        END IF;

        -- Row-by-row logic (example: give a 10% raise)
        UPDATE employees SET salary = v_salary * 1.10 WHERE emp_id = v_id;
    END LOOP;

    -- 4) CLOSE the cursor
    CLOSE cur;
END $$
DELIMITER ;

CALL ProcessSalaries();

  
     Better alternative: The example above could be done in a single set-based statement — UPDATE employees SET salary = salary * 1.10; — which is far faster. Always ask "can this be set-based?" before reaching for a cursor.
  

  
     Interviewer tip: The one-liner they want — "A cursor iterates through a result set row by row inside a stored program using DECLARE, OPEN, FETCH, and CLOSE. It's useful for procedural logic but slower than set-based operations, so use it sparingly."

Property	Behaviour
Read-only	Cannot update data through the cursor
Non-scrollable	Moves forward only (no going back)
Asensitive	May or may not reflect live changes

What is a cursor in MySQL and when should you use it?

Answer with Explanation

What is a cursor in MySQL and when should you use it?

The Four Steps to Use a Cursor

Cursor Properties in MySQL

Quick Example