SQL - Cursors

SQL Cursors: Row-by-Row Processing

In SQL, which is fundamentally a set-based language, operations are designed to work on entire sets of rows at once. However, there are rare situations where you might need to process data in a more traditional, procedural, row-by-row fashion. A cursor is a database object that allows you to do just that: traverse a result set one row at a time.

The Modern Perspective: Set-Based vs. Procedural Logic

Warning: Cursors should be a tool of last resort. They are often significantly slower than set-based operations and can lead to complex, hard-to-maintain code. Before using a cursor, always try to solve the problem with standard SQL joins, window functions, or CTEs.

So, when are they appropriate? Cursors are typically confined to stored procedures and are reserved for complex tasks that are difficult or impossible to express in a single SQL statement, such as:

• Executing a series of complex stored procedures for each row in a result set.
• Performing intricate, stateful calculations that depend on the value of the previous row.
• Building and executing dynamic SQL statements for each row.

For tasks like data migration, conditional updates, or simple calculations, a set-based approach (e.g., INSERT ... SELECT or UPDATE ... JOIN) is almost always superior in both performance and simplicity.

The Cursor Lifecycle

Managing a cursor involves four distinct steps:

1. DECLARE: Define the cursor by giving it a name and associating it with a SELECT statement that defines the result set to be traversed.
2. OPEN: Execute the SELECT statement and populate the cursor with the resulting rows. The cursor now points to the position before the first row.
3. FETCH: Retrieve the next row from the cursor and advance the cursor’s position. The data from the row is typically stored in local variables for processing.
4. CLOSE: Deactivate the cursor and release the resources and memory it was using. This is a critical cleanup step.

Syntax Overview (MySQL/MariaDB Example)

-- 1. Declare the cursor and variables
DECLARE done INT DEFAULT FALSE;
DECLARE my_variable_type [DATATYPE];
DECLARE my_cursor CURSOR FOR SELECT my_column FROM my_table;

-- Declare a handler to exit the loop
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- 2. Open the cursor
OPEN my_cursor;

-- 3. Loop and fetch rows
read_loop: LOOP
  FETCH my_cursor INTO my_variable;
  IF done THEN
    LEAVE read_loop;
  END IF;
  -- ... process the variables ...
END LOOP;

-- 4. Close the cursor
CLOSE my_cursor;

Example: A Task Unsuited for Cursors

To illustrate both the syntax of a cursor and why it’s often overkill, let’s look at a common but misguided use case: copying data from one table to another.

First, our tables:

CREATE TABLE CUSTOMERS(
    ID INT PRIMARY KEY,
    NAME VARCHAR(100) NOT NULL,
    EMAIL VARCHAR(100)
);
INSERT INTO CUSTOMERS VALUES
    (1, 'Ramesh', 'ramesh@example.com'),
    (2, 'Khilan', 'khilan@example.com');

CREATE TABLE CUSTOMERS_BACKUP(
    ID INT PRIMARY KEY,
    NAME VARCHAR(100) NOT NULL
);

Here is a stored procedure using a cursor to copy the ID and NAME for each customer into the backup table:

DELIMITER //
CREATE PROCEDURE BackupCustomersWithCursor()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE cust_id INT;
    DECLARE cust_name VARCHAR(100);

    -- 1. Declare Cursor
    DECLARE cust_cursor CURSOR FOR
        SELECT ID, NAME FROM CUSTOMERS;

    -- Declare handler for when no more rows are found
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 2. Open Cursor
    OPEN cust_cursor;

    -- 3. Fetch and insert in a loop
    copy_loop: LOOP
        FETCH cust_cursor INTO cust_id, cust_name;
        IF done THEN
            LEAVE copy_loop;
        END IF;
        INSERT INTO CUSTOMERS_BACKUP(ID, NAME) VALUES (cust_id, cust_name);
    END LOOP;

    -- 4. Close Cursor
    CLOSE cust_cursor;
END //
DELIMITER ;

The Better Alternative: A Set-Based Approach

While the cursor procedure works, it is unnecessarily complex and slow. The same result can be achieved with a single, highly efficient, set-based statement:

INSERT INTO CUSTOMERS_BACKUP (ID, NAME)
SELECT ID, NAME FROM CUSTOMERS;

This single query is easier to write, easier to read, and will outperform the cursor-based approach by orders of magnitude, especially on large datasets. It demonstrates the power of thinking in terms of data sets rather than individual rows.

Key Takeaways

• Prioritize Set-Based Operations: Always seek a solution using standard SQL (JOIN, CASE, GROUP BY, etc.) before considering a cursor.
• Understand the Performance Cost: Cursors introduce overhead for each row fetched, making them inherently slower than set-based operations.
• Use for Procedural Necessity: Reserve cursors for complex, row-dependent logic that cannot be handled by set-based alternatives.
• Always Close Cursors: Failing to close a cursor can leave locks on tables and consume server resources until the session ends.