SQL Cursor and it’s Uses with Examples

Structured Query Language (SQL) is a powerful tool for managing and manipulating relational databases. While most SQL operations are set-based, meaning they operate on entire sets of data at once, there are times when you need to process data row by row. This is where SQL cursors come into play. In this article, we will explore what SQL cursors are, their uses, and provide examples to illustrate their functionality.

What is an SQL Cursor?

An SQL cursor is a database object used to retrieve, manipulate, and navigate through a result set row by row. Unlike standard SQL queries that operate on entire data sets, cursors allow you to process individual rows within a result set. This can be particularly useful when you need to perform complex row-by-row operations that cannot be easily achieved with set-based operations.

Cursors are often used in stored procedures, triggers, and other database scripts where iterative processing is required. However, it’s important to note that cursors can be less efficient than set-based operations, so they should be used judiciously.

Types of SQL Cursors

There are several types of cursors in SQL, each with its own characteristics:

• Static Cursor: A static cursor creates a temporary copy of the data in the result set. This means that any changes made to the original data after the cursor is opened will not be reflected in the cursor. Static cursors are useful when you need a consistent view of the data.
• Dynamic Cursor: A dynamic cursor reflects all changes made to the data in the result set, even if those changes occur after the cursor is opened. This type of cursor is more flexible but can be slower due to the overhead of tracking changes.
• Forward-Only Cursor: A forward-only cursor allows you to move only forward through the result set. You cannot move backward or skip rows. This type of cursor is generally faster and uses less memory than other types.
• Keyset-Driven Cursor: A keyset-driven cursor is a compromise between static and dynamic cursors. It stores a set of keys that uniquely identify the rows in the result set. While the cursor is open, changes to non-key columns are reflected, but changes to key columns or new rows are not.

Uses of SQL Cursors

SQL cursors are used in various scenarios, including:

• Row-by-Row Processing: When you need to perform operations on each row individually, such as complex calculations or data transformations.
• Data Validation: When you need to validate data row by row before inserting or updating records in a database.
• Batch Processing: When you need to process data in batches, such as generating reports or sending notifications.
• Complex Business Logic: When you need to implement complex business logic that requires iterative processing.

Example of Using an SQL Cursor

Let’s consider a simple example to illustrate how an SQL cursor works. Suppose we have a table named Employees with the following structure:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Salary DECIMAL(18, 2)
);

We want to give a 10% salary raise to each employee whose salary is less than $50,000. We can achieve this using a cursor as follows:

DECLARE @EmployeeID INT;
DECLARE @Salary DECIMAL(18, 2);

-- Declare the cursor
DECLARE EmployeeCursor CURSOR FOR
SELECT EmployeeID, Salary
FROM Employees
WHERE Salary < 50000;

-- Open the cursor
OPEN EmployeeCursor;

-- Fetch the first row
FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @Salary;

-- Loop through the result set
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Calculate the new salary
    SET @Salary = @Salary * 1.10;

    -- Update the employee's salary
    UPDATE Employees
    SET Salary = @Salary
    WHERE EmployeeID = @EmployeeID;

    -- Fetch the next row
    FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @Salary;
END;

-- Close and deallocate the cursor
CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;

Explanation:

• Declare the Cursor: We declare a cursor named EmployeeCursor that selects the EmployeeID and Salary columns from the Employees table where the salary is less than $50,000.
• Open the Cursor: We open the cursor to start processing the result set.
• Fetch the First Row: We fetch the first row from the cursor into the variables @EmployeeID and @Salary.
• Loop Through the Result Set: We use a WHILE loop to iterate through the result set. For each row, we calculate the new salary by increasing it by 10% and then update the Employees table with the new salary.
• Fetch the Next Row: We fetch the next row from the cursor and repeat the process until there are no more rows to fetch.
• Close and Deallocate the Cursor: Finally, we close the cursor and deallocate it to free up resources.

Conclusion

SQL cursors provide a way to process data row by row, which can be essential for certain tasks that require iterative processing. However, due to their potential impact on performance, cursors should be used sparingly and only when set-based operations are not feasible. By understanding the different types of cursors and their appropriate use cases, you can effectively leverage them in your database applications.

In the example provided, we demonstrated how to use a cursor to give a salary raise to employees earning less than $50,000. This example highlights the basic steps involved in declaring, opening, fetching, and closing a cursor, as well as performing row-by-row operations within a loop.

Remember, while cursors can be powerful, they should be used with caution and only when necessary to ensure optimal database performance.