Assignment No 6
Cursors in MySQL
Introduction
In MySQL, cursors are used to process individual rows returned by a query.
Cursors are especially useful when performing row-by-row operations that can't be handled
with standard SQL statements.
While SQL works best with set-based operations, sometimes procedural logic is required to
process data one row at a time — this is where cursors come in.
1. Implicit Cursors
Implicit cursors are automatically created by MySQL when executing SQL statements like
SELECT INTO, INSERT, UPDATE, or DELETE.
The server takes care of opening, fetching, and closing the cursor behind the scenes.
They are mostly used in stored procedures and functions for single-row operations.
Example:
DECLARE emp_name VARCHAR(50);
DECLARE emp_id INT;
SELECT name, id INTO emp_name, emp_id FROM employees WHERE id = 101;
2. Explicit Cursors
Explicit cursors are manually declared and controlled by the developer to handle queries
that return multiple rows.
Steps to Use:
1. DECLARE the cursor.
2. OPEN the cursor.
3. FETCH rows from the cursor into variables.
4. CLOSE the cursor.
Example:
DELIMITER $$
CREATE PROCEDURE list_employees()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE emp_name VARCHAR(50);
DECLARE emp_sal DECIMAL(10,2);
DECLARE cur CURSOR FOR SELECT name, salary FROM employees;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO emp_name, emp_sal;
IF done THEN
LEAVE read_loop;
END IF;
SELECT emp_name, emp_sal;
END LOOP;
CLOSE cur;
END$$
DELIMITER ;
3. Cursor FOR Loop (Simulated)
MySQL does not natively support a FOR loop cursor like PL/SQL.
But you can simulate it using LOOP or REPEAT along with a cursor.
Example:
DELIMITER $$
CREATE PROCEDURE loop_example()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE emp_name VARCHAR(50);
DECLARE cur CURSOR FOR SELECT name FROM employees;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
emp_loop: LOOP
FETCH cur INTO emp_name;
IF done THEN
LEAVE emp_loop;
END IF;
SELECT emp_name;
END LOOP;
CLOSE cur;
END$$
DELIMITER ;
4. Parameterized Cursor (Simulated)
MySQL does not directly support parameterized cursors (unlike Oracle PL/SQL).
However, you can simulate similar functionality by using prepared statements and dynamic
SQL, or by using stored procedures with parameters to dynamically declare cursors.
Example:
DELIMITER $$
CREATE PROCEDURE get_employees_by_dept(IN dept_id INT)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE emp_name VARCHAR(50);
DECLARE cur CURSOR FOR
SELECT name FROM employees WHERE department_id = dept_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
dept_loop: LOOP
FETCH cur INTO emp_name;
IF done THEN
LEAVE dept_loop;
END IF;
SELECT emp_name;
END LOOP;
CLOSE cur;
END$$
DELIMITER ;
Best Practices with Cursors in MySQL
- Avoid cursors when possible. Use set-based operations (JOIN, UPDATE, INSERT ... SELECT)
for better performance.
- Use cursors for row-by-row processing only when necessary.
- Always ensure to close the cursor to free memory.
- Use handlers (NOT FOUND) to avoid runtime errors during fetch operations.
Summary Table
Cursor Type Supported in Use Case Syntax/Notes
MySQL?
Implicit Cursor Yes Single-row queries No declaration
(SELECT INTO) needed
Explicit Cursor Yes Multi-row fetch, Declare, open, fetch,
row-by-row logic close
Cursor FOR Loop No (Simulated) Simulated via LOOP No native support
Parameterized No (Simulated) Use procedure Simulate using IN
Cursor parameters params in SELECT
Conclusion
Cursors in MySQL are a powerful feature for situations requiring row-by-row processing.
While explicit cursors and simulated cursor loops provide necessary control, developers
must be cautious of performance impacts.
Set-based logic is always preferred in MySQL when possible.
