Mysql - Cursor
A cursor in MySQL is a database object used to retrieve, traverse, and manipulate data row-by-row. Cursors are useful when you need to perform operations on individual rows returned by a query, such as complex calculations, business logic, or updates based on row-by-row processing.
Types of Cursors in MySQL
MySQL supports two types of cursors:
- Read-only Cursors: These cursors allow you to read data from the result set but do not support updating the data.
- Non-scrollable Cursors: These cursors can only move forward in the result set, meaning you can only fetch rows sequentially from the first to the last.
Basic Cursor Operations
- DECLARE Cursor
The `DECLARE` statement is used to define a cursor, specifying the query whose result set the cursor will iterate over.
DECLARE cursor_name CURSOR FOR select_statement;
Where, `cursor_name` is the name you assign to the cursor. `select_statement` is the query whose result set the cursor will iterate over.
- OPEN Cursor
To open a cursor in MySQL, you use the `OPEN` statement after declaring the cursor. Opening the cursor initializes it and prepares it for fetching rows.
OPEN cursor_name;
Where, `cursor_name` is the name of the cursor you have declared.
- FETCH Cursor
To fetch data from a cursor in MySQL, you use the `FETCH` statement. Fetching retrieves the next row from the result set of the cursor into specified variables.
FETCH cursor_name INTO variable1, variable2, ...;
Where, `cursor_name` is the name of the cursor you have declared and opened. `variable1, variable2, ...` are the variables to store the fetched column values.
- CLOSE Cursor
To close a cursor in MySQL, you use the `CLOSE` statement. This statement releases the resources associated with an open cursor. It is important to close a cursor when it is no longer needed to free up resources and avoid potential memory leaks.
CLOSE cursor_name;
Where, `cursor_name` is the name of the cursor you want to close.
Basic Examples
Suppose, we have an `employee` table with `name` and `department_id`.
| id | name | department_id |
|---|---|---|
| 1 | Jane Smith | 1 |
| 2 | John Doe | 2 |
| 3 | Alice Johnson | 3 |
Ok, lets see how we manage `CURSOR` in Stored Procedure.
DELIMITER //
CREATE PROCEDURE process_employees()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE emp_id INT;
DECLARE emp_name VARCHAR(100);
DECLARE emp_dept INT;
DECLARE cur CURSOR FOR SELECT id, name, department_id FROM employees;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO emp_id, emp_name, emp_dept;
IF done THEN
LEAVE read_loop;
END IF;
-- Process each row: here we're just inserting a log
INSERT INTO employee_log (employee_id, log_message, log_time)
VALUES (emp_id, CONCAT('Processed employee: ', emp_name), NOW());
END LOOP;
CLOSE cur;
END //
DELIMITER ;
In this example
- DECLARE CURSOR: The cursor `cur` is declared to select `id`, `name`, and `department_id` from the `employees` table.
- OPEN CURSOR: The cursor is opened to begin fetching rows.
- FETCH CURSOR: Inside the loop, the `FETCH` statement retrieves each row and stores the values in the variables `emp_id`, `emp_name`, and `emp_dept`.
- LOOP and PROCESS: The loop processes each row until there are no more rows to fetch. In this example, each row's details are inserted into the `employee_log` table.
- CLOSE CURSOR: The cursor is closed after processing is complete.
Key Points to Remember
- Cursors should be closed after their use to release resources.
- Cursors are typically used when you need to process rows individually and cannot achieve the desired results with set-based operations.
- Overuse of cursors can lead to performance issues. They should be used judiciously, and set-based operations should be preferred when possible.
Terms of Use About Us Privacy Policy
All Rights Reserved.