MySQL Cursors

A cursor in MySQL is used to process the rows returned by a query one row at a time. Cursors are mainly used inside stored procedures and stored functions when we need to perform an operation on each row individually.

• Processes query results row by row.
• Used inside stored programs such as procedures.
• Allows individual rows to be read and processed.
• Uses DECLARE, OPEN, FETCH and CLOSE statements.

Syntax

DECLARE cursor_name CURSOR FOR
SELECT column1, column2
FROM table_name;

Steps to Use a Cursor

A cursor generally follows these four steps:

• DECLARE : Define the cursor and the query.
• OPEN : Open the cursor.
• FETCH : Retrieve rows one at a time.
• CLOSE : Close the cursor.

Example

Consider the following employees table:

Suppose we want to process each employee's salary one by one.

DELIMITER //

CREATE PROCEDURE process_employees()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE emp_name VARCHAR(100);
    DECLARE emp_salary DECIMAL(10,2);

    DECLARE employee_cursor CURSOR FOR
        SELECT employee_name, salary
        FROM employees;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN employee_cursor;

    read_loop: LOOP
        FETCH employee_cursor INTO emp_name, emp_salary;

        IF done = 1 THEN
            LEAVE read_loop;
        END IF;

        SELECT emp_name, emp_salary;
    END LOOP;

    CLOSE employee_cursor;
END //

DELIMITER ;

Execute the Procedure

CALL process_employees();

Output:

The cursor reads each employee record one at a time and processes the employee name and salary.