🎯 MySQL Cursors – Row-by-Row Processing in Stored Procedures

---

🧲 Introduction – Why Use Cursors in MySQL?

A cursor in MySQL allows you to iterate through a result set row-by-row, enabling procedural operations that require loop-based processing inside stored procedures. While SQL is inherently set-based, cursors are useful when:

• 🔁 You need to process each row with custom logic
• 🛠️ Performing operations not expressible via simple joins or updates
• 🚀 Automating tasks like batch updates or audits in procedural workflows

🎯 In this guide, you’ll learn:

• What cursors are and when to use them
• How to declare, open, fetch, and close cursors
• Cursor usage inside stored procedures
• Best practices and performance notes

---

🧱 1. What Is a Cursor in MySQL?

A cursor is a database object used to loop through a query result set one row at a time, usually within a LOOP, WHILE, or REPEAT block in a stored procedure.

---

🔧 2. Cursor Lifecycle – Step-by-Step

Step	Command	Purpose
Declare	DECLARE cursor_name CURSOR FOR ...	Defines the query for the cursor
Open	OPEN cursor_name;	Executes the query and prepares result set
Fetch	FETCH cursor_name INTO ...;	Retrieves the next row from the result set
Close	CLOSE cursor_name;	Releases memory and cursor object

---

📐 3. Syntax – Declaring and Using Cursors

🔹 Full Example

DELIMITER //

CREATE PROCEDURE process_employees()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE emp_id INT;
  DECLARE emp_name VARCHAR(100);

  -- Declare cursor
  DECLARE emp_cursor CURSOR FOR
    SELECT id, name FROM employees;

  -- Declare handler to detect end of result set
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  -- Open cursor
  OPEN emp_cursor;

  -- Loop through rows
  read_loop: LOOP
    FETCH emp_cursor INTO emp_id, emp_name;
    IF done THEN
      LEAVE read_loop;
    END IF;

    -- Do something with each row
    INSERT INTO employee_logs(emp_id, action_time)
    VALUES (emp_id, NOW());
  END LOOP;

  -- Close cursor
  CLOSE emp_cursor;
END //

DELIMITER ;

---

🔁 4. Explanation

• DECLARE ... CURSOR FOR defines the query the cursor will use
• OPEN starts the cursor
• FETCH moves through the result set one row at a time
• CONTINUE HANDLER FOR NOT FOUND ensures the loop ends when all rows are fetched
• CLOSE frees memory and finalizes the cursor

---

🧠 Cursor Use Cases

Use Case	Why Cursors Help
Row-level auditing	Log changes for each row in a loop
Batch processing with conditions	Apply logic to each record individually
Complex per-row calculations	Run nested queries or procedures per row
Cross-table lookups	Process row-by-row updates with foreign logic

---

🚫 When NOT to Use Cursors

🚫 Don’t Use Cursors For	Use This Instead
Simple row updates	UPDATE ... WHERE ...
Aggregations	GROUP BY, SUM(), etc.
Multi-table joins	JOIN, MERGE

✅ Cursors are slower than set-based queries—use only when necessary.

---

📘 Best Practices

✅ Tip	💡 Why It Matters
Always close cursors	Prevent memory leaks or open sessions
Use cursors only inside stored procedures	MySQL does not support cursors outside procedures
Use handlers to manage end-of-cursor	Ensures graceful loop exits
Keep cursor logic minimal and performant	Avoid expensive logic inside loop
Test result set before using cursors	Prevents loops over large or unnecessary results

---

🚀 Real-World Use Cases

Scenario	Cursor Purpose
Archive inactive users	Loop through old users and insert into archive
Generate custom audit trail	Record changes row-by-row for reporting
Sync stock levels with vendor feed	Row-wise comparison and update logic
Send alerts for flagged transactions	Query alerts table and trigger messages per row

---

📌 Summary – Recap & Next Steps

Cursors in MySQL are a powerful tool when you need precise control over row-by-row operations within stored procedures. Use them wisely when set-based logic doesn’t fit.

🔍 Key Takeaways

• Cursors let you process results row-by-row in procedures
• Require declaring, opening, fetching, and closing
• Use CONTINUE HANDLER to detect end-of-data
• Keep logic simple and efficient
• Avoid cursors where SQL’s set-based logic can do the job

⚙️ Real-World Relevance

Used in data warehousing, financial systems, sync engines, and automated reporting where row-by-row analysis is necessary.

---

❓ FAQ – MySQL Cursors

---

❓ Can I use cursors outside of stored procedures?

❌ No. Cursors are only valid inside stored programs (procedures/functions).

---

❓ How do I exit a cursor loop?

Use a LEAVE statement with an IF done THEN ... check using a handler:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

---

❓ Can I fetch into multiple variables?

✅ Yes. Just match the number of columns:

FETCH cursor_name INTO var1, var2, var3;

---

❓ Can I nest cursors?

✅ Yes, but it’s complex. Use with extreme care to avoid resource issues.

---

❓ Do cursors improve performance?

❌ Usually not. They are slower than set-based SQL. Use only when needed.

---

Share Now :