MySQL Cursor

MySQL Cursor

A cursor in MySQL is a database object used to retrieve and process rows from a query result set one at a time. Cursors are typically used within stored procedures to handle operations where row-by-row processing is necessary, such as complex computations or conditional logic.

Types of Cursors in MySQL

MySQL only supports read-only, non-scrollable, and non-updatable cursors:

• Read-only: The cursor does not allow modifications to the rows it retrieves.
• Non-scrollable: The cursor can only move forward through the result set.
• Non-updatable: The cursor cannot be used to update or delete data in the table directly.

Steps to Use a Cursor

1. Declare the Cursor: Define the cursor with a SELECT statement.
2. Open the Cursor: Open the cursor to initialize and populate it with the result set.
3. Fetch Data: Retrieve rows one by one from the cursor.
4. Close the Cursor: Release the resources associated with the cursor.

Syntax

Declare a Cursor

DECLARE cursor_name CURSOR FOR select_statement;

• cursor_name: The name of the cursor.
• select_statement: The SQL query that defines the result set.

Open a Cursor

OPEN cursor_name;

Fetch Data from a Cursor

FETCH cursor_name INTO variable1, variable2, ...;

• The number of variables must match the number of columns in the SELECT statement.

Close a Cursor

CLOSE cursor_name;

Example: Using a Cursor

1. Cursor in a Stored Procedure

This example demonstrates using a cursor to iterate through rows of a table and process them:

DELIMITER $$

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

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

    -- Declare a handler for the end of the cursor
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- Open the cursor
    OPEN emp_cursor;

    -- Fetch rows from the cursor
    read_loop: LOOP
        FETCH emp_cursor INTO emp_id, emp_name;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Perform operations on the row
        INSERT INTO employee_log(employee_id, employee_name, action)
        VALUES (emp_id, emp_name, 'Processed');
    END LOOP;

    -- Close the cursor
    CLOSE emp_cursor;
END$$

DELIMITER ;

Explanation:

1. Cursor Declaration: DECLARE emp_cursor CURSOR FOR SELECT id, name FROM employees.
2. Handler Declaration: DECLARE CONTINUE HANDLER FOR NOT FOUND handles the situation when all rows are processed.
3. Row-by-Row Processing: Each row from the employees table is fetched and processed in the loop.
4. Cursor Closure: CLOSE emp_cursor releases resources.

Important Points

1. Cursor Scope:

   • Cursors are declared within stored procedures, stored functions, or triggers.
   • They cannot be used outside these constructs.

2. Cursor Limitations:

   • Cursors in MySQL are read-only, meaning you cannot modify data through a cursor.
   • They are non-scrollable, so you can only traverse rows in a forward direction.

3. Performance:

   • Using cursors can be slower compared to set-based operations (e.g., UPDATE, DELETE with WHERE conditions).
   • Avoid cursors when a single SQL statement can achieve the same result.

Advanced Example: Cursor with Conditional Logic

This example demonstrates using a cursor to calculate bonuses for employees based on their salary:

DELIMITER $$

CREATE PROCEDURE calculate_bonuses()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE emp_id INT;
    DECLARE emp_salary DECIMAL(10, 2);
    DECLARE bonus DECIMAL(10, 2);

    -- Declare the cursor
    DECLARE salary_cursor CURSOR FOR SELECT id, salary FROM employees;

    -- Declare a handler for the end of the cursor
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- Open the cursor
    OPEN salary_cursor;

    -- Fetch rows and calculate bonuses
    salary_loop: LOOP
        FETCH salary_cursor INTO emp_id, emp_salary;
        IF done THEN
            LEAVE salary_loop;
        END IF;

        -- Calculate bonus based on salary
        IF emp_salary > 100000 THEN
            SET bonus = emp_salary * 0.10;
        ELSE
            SET bonus = emp_salary * 0.05;
        END IF;

        -- Insert the bonus record
        INSERT INTO bonuses(employee_id, bonus_amount)
        VALUES (emp_id, bonus);
    END LOOP;

    -- Close the cursor
    CLOSE salary_cursor;
END$$

DELIMITER ;

When to Use Cursors

Cursors should be used when:

1. Row-by-Row Processing: You need to process data row by row with conditional logic or complex calculations.
2. Dependent Operations: Later steps depend on results from previous steps within a procedure.

Alternatives to Cursors

• Set-Based Operations:
  • Whenever possible, use SQL statements like INSERT INTO ... SELECT, UPDATE, or DELETE with conditions to perform operations on multiple rows.
• JOINs and Subqueries:
  • Replace cursors with JOIN or WHERE clauses for better performance.

Conclusion

Cursors are useful for specific tasks requiring row-by-row processing but should be used judiciously to avoid performance issues. Always evaluate if set-based operations can achieve the desired outcome more efficiently. Let me know if you need further clarification or examples