Session 12: Cursors

Duration: 2 hours (Lecture: 2 hours, Lab: 2 hours)

Syllabus Topics:

Lecture:
- Cursors (Asensitive, Insensitive, Read-only, Nonscrollable)
- Cursors Example and Real-Time Use
Lab:
- Writing Procedures with DECLARE, FETCH, and CLOSE Cursor
- Example of Each Type of Cursor

Lecture Notes (In-Depth)

1. Cursors (Asensitive, Insensitive, Read-only, Nonscrollable)

Definition: A cursor is a database object that allows row-by-row processing of a result set within a stored procedure or function. It acts as a pointer to rows returned by a SELECT query, enabling iterative processing when set-based operations are insufficient.
Types of Cursors:
- Asensitive Cursor:
  - Reflects changes to the underlying data during processing (e.g., if another transaction modifies the table).
  - Default cursor type in MySQL.
  - Sensitive to updates, deletes, or inserts in the table while the cursor is open.
  - Example: If a row’s data changes during cursor iteration, the cursor sees the updated values.
- Insensitive Cursor:
  - A snapshot of the data at the time the cursor is opened; does not reflect subsequent changes.
  - MySQL does not explicitly support insensitive cursors, but you can simulate them by copying data to a temporary table.
  - Example: Use a temporary table to store query results before processing to ensure data consistency.
- Read-only Cursor:
  - Allows reading data but not updating it through the cursor.
  - MySQL cursors are read-only by default; updates must be performed using separate DML statements (e.g., UPDATE).
  - Example: Fetch employee names and salaries for reporting without modifying them.
- Nonscrollable Cursor:
  - Allows sequential access to rows in one direction (forward only).
  - MySQL cursors are nonscrollable; you cannot move backward or jump to specific rows.
  - Example: Process rows sequentially from start to end of the result set.
Key Notes:
- Cursors are resource-intensive and should be used only when set-based SQL (e.g., joins, aggregates) cannot achieve the desired result.
- MySQL cursors are typically asensitive, read-only, and nonscrollable due to engine limitations.
- Cursors are most commonly used in stored procedures for tasks like row-by-row updates or logging.
Cursor Lifecycle:
1. DECLARE: Define the cursor with a SELECT statement.
```
DECLARE cursor_name CURSOR FOR SELECT_statement;
```
2. OPEN: Initialize the cursor to start processing the result set.
```
OPEN cursor_name;
```
3. FETCH: Retrieve the next row’s data into variables.
```
FETCH cursor_name INTO variable1, variable2, ...;
```
4. CLOSE: Release the cursor to free resources.
```
CLOSE cursor_name;
```
5. Handler: Use a CONTINUE HANDLER to detect when no more rows are available.
```
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
```

2. Cursors Example and Real-Time Use

Example:

A stored procedure that uses a cursor to process employees and increase salaries for a specific department.

DELIMITER //
CREATE PROCEDURE IncreaseDeptSalary(IN dept_id INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_id INT;
    DECLARE emp_salary DECIMAL(10,2);
    DECLARE emp_cursor CURSOR FOR
        SELECT emp_id, salary FROM employees WHERE dept_id = dept_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN emp_cursor;
    salary_loop: LOOP
        FETCH emp_cursor INTO emp_id, emp_salary;
        IF done THEN
            LEAVE salary_loop;
        END IF;
        UPDATE employees SET salary = emp_salary * 1.1 WHERE emp_id = emp_id;
    END LOOP;
    CLOSE emp_cursor;
END //
DELIMITER ;

Explanation:
- Declares a cursor to select emp_id and salary for a given dept_id.
- Fetches each row into variables and updates the salary.
- Exits the loop when no more rows are found (NOT FOUND handler).
- Closes the cursor to free resources.

Real-Time Use Cases:
- Data Migration: Process rows individually to transform or validate data during migration (e.g., converting data formats).
- Auditing: Log changes for each row processed (e.g., track salary updates in an audit table).
- Complex Calculations: Perform row-specific computations that cannot be handled with set-based SQL (e.g., iterative updates based on previous rows).
- Batch Processing: Process large datasets in smaller chunks to avoid locking or memory issues.
- Example: Update employee bonuses based on performance metrics stored in another table, requiring row-by-row logic.
Key Notes:
- Avoid cursors for simple operations that can be handled with set-based SQL (e.g., UPDATE employees SET salary = salary * 1.1 WHERE dept_id = 1;).
- Cursors are supported by InnoDB; MyISAM has limited support in older MySQL versions.

Lab Notes (In-Depth)

The lab focuses on creating stored procedures with cursors, implementing different cursor types (to the extent supported by MySQL), and testing their behavior.

1. Writing Procedures with DECLARE, FETCH, and CLOSE Cursor

Setup:

CREATE DATABASE company;
USE company;
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    salary DECIMAL(10,2),
    dept_id INT
);
INSERT INTO employees VALUES
    (1, 'Alice', 60000, 1),
    (2, 'Bob', 55000, 1),
    (3, 'Charlie', 40000, 2),
    (4, 'David', 45000, 2),
    (5, 'Eve', 70000, 3);
CREATE TABLE audit_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    action VARCHAR(50),
    emp_id INT,
    details VARCHAR(100),
    action_time TIMESTAMP
);

Basic Cursor Procedure:

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

    OPEN emp_cursor;
    emp_loop: LOOP
        FETCH emp_cursor INTO emp_id, emp_name, emp_salary;
        IF done THEN
            LEAVE emp_loop;
        END IF;
        INSERT INTO audit_log (action, emp_id, details, action_time)
        VALUES ('LOG', emp_id, CONCAT('Salary: ', emp_salary, ' for ', emp_name), NOW());
    END LOOP;
    CLOSE emp_cursor;
END //
DELIMITER ;
CALL LogEmployeeSalaries();
SELECT * FROM audit_log;

Explanation:
- Cursor selects emp_id, name, and salary.
- Each row is fetched and logged into audit_log.
- Loop exits when no more rows are found.