Cursors and Row-by-Row Processing

Difficulty: Advanced
Generated on: 2025-07-10 02:32:19
Category: SQL Cheatsheet for Database Development

SQL Cursors & Row-by-Row Processing: Advanced Cheatsheet

1. Quick Overview

Cursors allow you to process the results of a SQL query one row at a time. They are essentially pointers that navigate through a result set. While powerful, row-by-row processing is generally less efficient than set-based operations. Use cursors judiciously, primarily when you must perform operations on individual rows that are impossible or extremely complex with standard SQL.

When to Use:

Complex Data Transformations: When you need to apply intricate logic to each row that cannot be easily expressed in a single SQL statement.
External System Integration: When you need to interact with external systems or APIs for each row in a result set.
Audit Logging: When detailed logging of each row’s processing is required.
Conditional Logic: When the processing logic depends on the values of previous rows.

When to Avoid:

Simple Data Updates/Deletes: Use UPDATE or DELETE statements with appropriate WHERE clauses for set-based operations.
Aggregations: Utilize aggregate functions like SUM, AVG, COUNT, MIN, MAX, and GROUP BY clauses.
Bulk Data Loading: Use bulk insert utilities or INSERT INTO ... SELECT statements.
Performance-Critical Operations: Cursors can be significantly slower than set-based operations.

2. Syntax

The general syntax involves declaring, opening, fetching from, and closing/deallocating the cursor. The specific syntax varies slightly depending on the database system.

General Cursor Structure:

-- Declare the cursor
DECLARE <cursor_name> CURSOR FOR <select_statement>;

-- Open the cursor
OPEN <cursor_name>;

-- Fetch data from the cursor
FETCH NEXT FROM <cursor_name> INTO <variable_list>;

-- Loop through the results
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Process the fetched data
    -- ...

    -- Fetch the next row
    FETCH NEXT FROM <cursor_name> INTO <variable_list>;
END;

-- Close the cursor
CLOSE <cursor_name>;

-- Deallocate the cursor
DEALLOCATE <cursor_name>;

SQL Server Example:

DECLARE @OrderID INT, @CustomerID VARCHAR(5);

-- Declare the cursor
DECLARE OrderCursor CURSOR FOR
SELECT OrderID, CustomerID
FROM Orders
WHERE OrderDate >= '2023-01-01';

-- Open the cursor
OPEN OrderCursor;

-- Fetch the first row
FETCH NEXT FROM OrderCursor INTO @OrderID, @CustomerID;

-- Loop through the results
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Process the data (e.g., print the values)
    PRINT 'Order ID: ' + CAST(@OrderID AS VARCHAR) + ', Customer ID: ' + @CustomerID;

    -- Fetch the next row
    FETCH NEXT FROM OrderCursor INTO @OrderID, @CustomerID;
END;

-- Close the cursor
CLOSE OrderCursor;

-- Deallocate the cursor
DEALLOCATE OrderCursor;

PostgreSQL Example (using PL/pgSQL):

CREATE OR REPLACE FUNCTION process_orders()
RETURNS VOID AS $$
DECLARE
    order_record RECORD;
    order_cursor CURSOR FOR SELECT OrderID, CustomerID FROM Orders WHERE OrderDate >= '2023-01-01';
BEGIN
    OPEN order_cursor;

    LOOP
        FETCH order_cursor INTO order_record;
        EXIT WHEN NOT FOUND;

        -- Process the data (e.g., raise a notice)
        RAISE NOTICE 'Order ID: %, Customer ID: %', order_record.OrderID, order_record.CustomerID;

    END LOOP;

    CLOSE order_cursor;
END;
$$ LANGUAGE plpgsql;

SELECT process_orders();

MySQL Example (using Stored Procedures):

DELIMITER //

CREATE PROCEDURE process_orders()
BEGIN
    DECLARE order_id INT;
    DECLARE customer_id VARCHAR(5);
    DECLARE done INT DEFAULT FALSE;

    -- Declare the cursor
    DECLARE order_cursor CURSOR FOR
    SELECT OrderID, CustomerID
    FROM Orders
    WHERE OrderDate >= '2023-01-01';

    -- Declare a handler for when the cursor reaches the end
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Open the cursor
    OPEN order_cursor;

    read_loop: LOOP
        FETCH order_cursor INTO order_id, customer_id;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Process the data (e.g., display the values)
        SELECT CONCAT('Order ID: ', order_id, ', Customer ID: ', customer_id);

    END LOOP;

    -- Close the cursor
    CLOSE order_cursor;
END //

DELIMITER ;

CALL process_orders();

Oracle Example (using PL/SQL):

DECLARE
  order_id NUMBER;
  customer_id VARCHAR2(5);
  CURSOR order_cursor IS
    SELECT OrderID, CustomerID
    FROM Orders
    WHERE OrderDate >= DATE '2023-01-01';
BEGIN
  OPEN order_cursor;
  LOOP
    FETCH order_cursor INTO order_id, customer_id;
    EXIT WHEN order_cursor%NOTFOUND;

    -- Process the data (e.g., print the values)
    DBMS_OUTPUT.PUT_LINE('Order ID: ' || order_id || ', Customer ID: ' || customer_id);
  END LOOP;
  CLOSE order_cursor;
END;
/

3. Common Use Cases

Conditional Updates Based on Previous Row: Imagine updating a “RunningTotal” column in a table. Each row’s “RunningTotal” depends on the previous row’s “RunningTotal” and the current row’s value. This is difficult (though sometimes possible with window functions) without a cursor.
Interacting with External Systems: For each customer in a database, you might need to call an external address validation service. A cursor allows you to iterate through the customer records and make the API calls for each one.
Complex Data Validation and Transformation: Suppose you need to validate complex data relationships across multiple tables for each row in a primary table. A cursor can help you iterate, perform the validations, and apply transformations based on the validation results.
Generating Reports with Custom Formatting: When generating reports, especially those requiring custom formatting or complex calculations for each row, a cursor can be used to process the data and format it according to the report’s requirements.

4. Best Practices

Minimize Cursor Scope: Keep the cursor’s scope as narrow as possible. Only declare and use it within the specific procedure or function where it’s needed. Avoid global cursors.
Optimize the SELECT Statement: The SELECT statement used to populate the cursor is crucial. Make sure it’s as efficient as possible. Use indexes, avoid unnecessary columns, and filter data early.
Use READ ONLY Cursors When Possible: If you don’t need to update the data through the cursor, declare it as READ ONLY. This can improve performance. (Syntax varies by database.)
Use LOCAL Cursors: LOCAL cursors are deallocated automatically when the procedure or function finishes, reducing the risk of resource leaks. (Relevant for SQL Server).
Consider FAST_FORWARD Cursors: FAST_FORWARD cursors are optimized for read-only, forward-only access, which is the most common use case. (Relevant for SQL Server).
Batch Processing When Feasible: Instead of processing one row at a time, try to batch the data into smaller sets and process them in bulk. This can significantly improve performance. For example, collect a batch of IDs, perform a bulk update, then move to the next batch.
Use Transaction Management: Wrap cursor operations within a transaction to ensure data consistency and atomicity. If any error occurs during processing, the entire transaction can be rolled back.
Avoid Cursors Whenever Possible: Reiterate: Is there a set-based solution? Often, a clever combination of joins, subqueries, window functions, or stored procedures can achieve the same result much more efficiently.

5. Examples

Example 1: Updating a Running Total (SQL Server)

-- Create a sample table
CREATE TABLE Sales (
    SaleID INT IDENTITY(1,1) PRIMARY KEY,
    SaleDate DATE,
    Amount DECIMAL(10, 2)
);

INSERT INTO Sales (SaleDate, Amount) VALUES
('2023-01-01', 100.00),
('2023-01-02', 150.00),
('2023-01-03', 75.00),
('2023-01-04', 200.00);

ALTER TABLE Sales ADD RunningTotal DECIMAL(10, 2);

-- Update the RunningTotal using a cursor
DECLARE @SaleID INT, @Amount DECIMAL(10, 2), @RunningTotal DECIMAL(10, 2);

-- Initialize RunningTotal
SET @RunningTotal = 0;

DECLARE SaleCursor CURSOR FOR
SELECT SaleID, Amount
FROM Sales
ORDER BY SaleDate;

OPEN SaleCursor;

FETCH NEXT FROM SaleCursor INTO @SaleID, @Amount;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @RunningTotal = @RunningTotal + @Amount;

    UPDATE Sales
    SET RunningTotal = @RunningTotal
    WHERE SaleID = @SaleID;

    FETCH NEXT FROM SaleCursor INTO @SaleID, @Amount;
END;

CLOSE SaleCursor;
DEALLOCATE SaleCursor;

SELECT * FROM Sales;

Example 2: Calling an External API (Conceptual - Pseudocode)

This example is conceptual because calling an external API directly from SQL is generally not recommended for security and architectural reasons. However, the cursor part demonstrates the logic.

-- Pseudocode - Requires integration with a CLR procedure, external script, or message queue.

-- Assuming a table of Customers with Address information

DECLARE @CustomerID INT, @Address VARCHAR(255);

DECLARE CustomerCursor CURSOR FOR
SELECT CustomerID, Address
FROM Customers
WHERE NeedsAddressValidation = 1;  -- Only process customers needing validation

OPEN CustomerCursor;

FETCH NEXT FROM CustomerCursor INTO @CustomerID, @Address;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- **THIS IS WHERE YOU WOULD CALL THE EXTERNAL API**
    -- Example (conceptual):
    -- SET @ValidatedAddress = ExecuteExternalAPI('AddressValidationService', @Address);

    -- IF @ValidatedAddress IS NOT NULL
    -- BEGIN
    --    UPDATE Customers SET Address = @ValidatedAddress, NeedsAddressValidation = 0
    --    WHERE CustomerID = @CustomerID;
    -- END
    -- ELSE
    -- BEGIN
    --    -- Log the error or handle the failure.
    --    PRINT 'Address validation failed for CustomerID: ' + CAST(@CustomerID AS VARCHAR);
    -- END

    FETCH NEXT FROM CustomerCursor INTO @CustomerID, @Address;
END;

CLOSE CustomerCursor;
DEALLOCATE CustomerCursor;

Example 3: Audit Logging (SQL Server)

-- Create a sample table
CREATE TABLE Products (
    ProductID INT IDENTITY(1,1) PRIMARY KEY,
    ProductName VARCHAR(255),
    Price DECIMAL(10, 2)
);

CREATE TABLE ProductAudit (
    AuditID INT IDENTITY(1,1) PRIMARY KEY,
    ProductID INT,
    OldPrice DECIMAL(10, 2),
    NewPrice DECIMAL(10, 2),
    AuditDate DATETIME DEFAULT GETDATE()
);

-- Sample data
INSERT INTO Products (ProductName, Price) VALUES ('Widget', 10.00);
INSERT INTO Products (ProductName, Price) VALUES ('Gadget', 20.00);

-- Procedure to update prices and audit changes
CREATE PROCEDURE UpdateProductPrices (@PercentageIncrease DECIMAL(5,2))
AS
BEGIN
    DECLARE @ProductID INT, @OldPrice DECIMAL(10, 2), @NewPrice DECIMAL(10, 2);

    DECLARE ProductCursor CURSOR FOR
    SELECT ProductID, Price
    FROM Products;

    OPEN ProductCursor;

    FETCH NEXT FROM ProductCursor INTO @ProductID, @OldPrice;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Calculate the new price
        SET @NewPrice = @OldPrice * (1 + (@PercentageIncrease / 100));

        -- Update the product price
        UPDATE Products
        SET Price = @NewPrice
        WHERE ProductID = @ProductID;

        -- Log the audit record
        INSERT INTO ProductAudit (ProductID, OldPrice, NewPrice)
        VALUES (@ProductID, @OldPrice, @NewPrice);

        FETCH NEXT FROM ProductCursor INTO @ProductID, @OldPrice;
    END;

    CLOSE ProductCursor;
    DEALLOCATE ProductCursor;
END;

-- Execute the procedure
EXEC UpdateProductPrices 10;  -- Increase prices by 10%

-- Verify the results
SELECT * FROM Products;
SELECT * FROM ProductAudit;

6. Common Pitfalls

Performance Issues: This is the biggest pitfall. Row-by-row processing is generally slow. Always explore set-based alternatives first. Profile your code to identify performance bottlenecks.
Deadlocks: If the cursor performs updates or deletes on tables that are also accessed by other processes, you can encounter deadlocks. Use appropriate transaction isolation levels and locking hints to mitigate this.
Resource Leaks: Failing to close and deallocate cursors can lead to resource leaks, especially in long-running procedures. Always ensure proper cleanup.
Incorrect FETCH_STATUS Handling: If you don’t properly check @@FETCH_STATUS (SQL Server), %NOTFOUND (Oracle), or similar mechanisms, your loop might not terminate correctly, leading to infinite loops or unexpected behavior.
Using Cursors for Simple Tasks: Avoid using cursors for tasks that can be easily accomplished with standard SQL queries. For instance, updating a column based on a simple calculation can be done without a cursor.
Security Vulnerabilities: If the SELECT statement used to populate the cursor is vulnerable to SQL injection, the entire procedure becomes vulnerable. Use parameterized queries or input validation to prevent SQL injection attacks.

Troubleshooting Tips:

Use Profiling Tools: Use database profiling tools (e.g., SQL Server Profiler, pgAdmin’s query analyzer, MySQL Workbench’s performance schema) to identify performance bottlenecks in your cursor-based code.
Check Error Logs: Examine the database error logs for any errors or warnings related to cursor operations.
Simplify the Cursor Logic: If you’re experiencing performance problems, try simplifying the cursor logic to isolate the issue.
Test with Small Datasets: Test your cursor-based code with small datasets to ensure it’s working correctly before deploying it to production.

7. Database Variations

Feature	SQL Server	PostgreSQL	MySQL	Oracle
Cursor Syntax	`DECLARE ... CURSOR FOR ...`	`DECLARE ... CURSOR FOR ...` (PL/pgSQL)	`DECLARE ... CURSOR FOR ...` (Stored Procedure)	`CURSOR ... IS ...` (PL/SQL)
Fetch Status	`@@FETCH_STATUS`	`NOT FOUND` (using `EXIT WHEN NOT FOUND;`)	`CONTINUE HANDLER FOR NOT FOUND`	`%NOTFOUND`
Cursor Types	`LOCAL`, `GLOBAL`, `STATIC`, `KEYSET`, `DYNAMIC`, `FAST_FORWARD`	(Limited options within PL/pgSQL)	(Limited options within Stored Procedures)	(Limited options within PL/SQL)
Error Handling	`TRY...CATCH`	`EXCEPTION` (PL/pgSQL)	`DECLARE CONTINUE HANDLER`	`EXCEPTION` (PL/SQL)
Transactions	`BEGIN TRANSACTION`, `COMMIT TRANSACTION`, `ROLLBACK TRANSACTION`	`BEGIN`, `COMMIT`, `ROLLBACK`	`START TRANSACTION`, `COMMIT`, `ROLLBACK`	`COMMIT`, `ROLLBACK`
Printing Output	`PRINT`	`RAISE NOTICE`	`SELECT CONCAT(...)`	`DBMS_OUTPUT.PUT_LINE`

Key Differences:

Procedural Language: PostgreSQL (PL/pgSQL), MySQL (Stored Procedures), and Oracle (PL/SQL) require cursors to be used within procedural code blocks. SQL Server allows cursors to be used in ad-hoc scripts as well.
Error Handling: The error handling mechanisms differ significantly across databases. Understanding the specific error handling syntax for each database is essential.
Cursor Types: SQL Server offers a wider range of cursor types (e.g., STATIC, KEYSET, DYNAMIC, FAST_FORWARD) that can be used to optimize performance. Other databases have more limited cursor type options.
Output: The methods for printing or displaying output from within a cursor loop vary depending on the database.

This cheatsheet provides a comprehensive guide to using cursors in SQL. Remember to prioritize set-based operations whenever possible and use cursors judiciously to avoid performance bottlenecks. Always test your code thoroughly and monitor performance in production environments.