How to Use Cursor in Stored Procedure MySQL: Syntax and Example

In MySQL, use DECLARE cursor_name CURSOR FOR select_statement inside a stored procedure to define a cursor. Open it with OPEN cursor_name, fetch rows with FETCH cursor_name INTO variables, and close it with CLOSE cursor_name to process query results row by row.

Syntax

A cursor in MySQL stored procedure lets you handle query results row by row. The main parts are:

• DECLARE cursor_name CURSOR FOR select_statement: Defines the cursor with a query.
• OPEN cursor_name: Opens the cursor to start fetching rows.
• FETCH cursor_name INTO variable_list: Retrieves the next row into variables.
• CLOSE cursor_name: Closes the cursor when done.

You also need to declare handlers for when no more rows are found.

sql

DECLARE cursor_name CURSOR FOR select_statement;
OPEN cursor_name;
FETCH cursor_name INTO variable1, variable2, ...;
-- process data
CLOSE cursor_name;

Example

This example shows a stored procedure that uses a cursor to list all employee names and their salaries from a table called employees. It prints each row using SELECT inside the procedure.

sql

DELIMITER $$
CREATE PROCEDURE ListEmployees()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE emp_name VARCHAR(100);
  DECLARE emp_salary DECIMAL(10,2);
  DECLARE emp_cursor CURSOR FOR SELECT name, salary FROM employees;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN emp_cursor;

  read_loop: LOOP
    FETCH emp_cursor INTO emp_name, emp_salary;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SELECT CONCAT('Employee: ', emp_name, ', Salary: ', emp_salary) AS info;
  END LOOP;

  CLOSE emp_cursor;
END$$
DELIMITER ;

Output

Employee: Alice, Salary: 50000.00
Employee: Bob, Salary: 60000.00
Employee: Carol, Salary: 55000.00

Common Pitfalls

Common mistakes when using cursors in MySQL stored procedures include:

• Not declaring a CONTINUE HANDLER for NOT FOUND, which causes infinite loops.
• Forgetting to CLOSE the cursor, leading to resource leaks.
• Trying to use cursors outside stored procedures (not allowed).
• Not opening the cursor before fetching.

Always handle the end of data properly and close cursors.

sql

/* Wrong: Missing handler causes infinite loop */
DECLARE emp_cursor CURSOR FOR SELECT name FROM employees;
OPEN emp_cursor;
FETCH emp_cursor INTO emp_name;
-- No handler for NOT FOUND, loop may never end

/* Right: Declare handler and check done flag */
DECLARE done INT DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

Quick Reference

Cursor Operation	Syntax	Purpose
Declare Cursor	DECLARE cursor_name CURSOR FOR SELECT ...	Define the query for the cursor
Open Cursor	OPEN cursor_name;	Prepare cursor to fetch rows
Fetch Row	FETCH cursor_name INTO var1, var2;	Retrieve next row into variables
Close Cursor	CLOSE cursor_name;	Release cursor resources
Handler for End	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;	Detect no more rows

Key Takeaways

Declare, open, fetch from, and close cursors inside stored procedures to process rows one by one.

Always declare a CONTINUE HANDLER for NOT FOUND to avoid infinite loops.

Close cursors after use to free resources.

Cursors cannot be used outside stored procedures in MySQL.

Use loops with a done flag to fetch all rows safely.