Cursor in SQL : Comprehensive Guide

In this article, we will explore the concept of “Cursor in SQL” in great details. Let’s get started.

Table of Contents

What is a Cursor in SQL ?

A Cursor in SQL is a database object that facilitates the traversal and manipulation of records in a result set. Unlike standard SQL queries that operate on entire result sets, cursors offer a row-by-row processing approach, enabling more granular control over data manipulation.

Cursors are particularly useful when dealing with complex logic or scenarios where data processing requires step-by-step navigation through a dataset.

How to create a Cursor in SQL ?

To Create a Cursor in SQL, we need to follow the below 5 steps

Declare Cursor: In this phase, declare variables and retrieve a set of values.
Open: Initiate the cursor.
Fetch: Retrieve data row by row from the cursor.
Close: Conclude the cursor; used to shut it.
Deallocate: Remove the cursor definition and release associated system resources.

Syntax of Cursor in SQL

-- Step 1: Declare Cursor
DECLARE cursor_name CURSOR FOR
    SELECT column1, column2, ...
    FROM your_table
    WHERE your_conditions;

-- Step 2: Open Cursor
OPEN cursor_name;

-- Step 3: Fetch Data
DECLARE variable1, variable2, ...;
FETCH cursor_name INTO variable1, variable2, ...;

-- Loop through fetched data
WHILE NOT done DO
    -- Process the data as needed
    -- Additional Fetch statements can be used for multiple rows

    -- Step 4: Close Cursor (optional)
    CLOSE cursor_name;

-- Step 5: Deallocate Cursor (optional)
DEALLOCATE PREPARE stmt_name;

Example of Cursor in SQL in MySQL

In this example, we will use Cursor within a Stored Procedure.

Let’s first create the table Employees and Insert some records using the below SQL Statements

-- Create Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    JobTitle VARCHAR(100),
    Department VARCHAR(100),
    Salary DECIMAL(10, 2)
);

-- Insert records into Employees table
INSERT INTO Employees (EmployeeID, FirstName, LastName, JobTitle, Department, Salary)
VALUES
    (1, 'John', 'Doe', 'Software Engineer', 'IT', 80000.00),
    (2, 'Jane', 'Smith', 'Data Analyst', 'Analytics', 65000.00),
    (3, 'Bob', 'Johnson', 'Project Manager', 'Project Management', 95000.00),
    (4, 'Alice', 'Williams', 'UX Designer', 'Design', 75000.00),
    (5, 'Charlie', 'Brown', 'System Administrator', 'IT', 85000.00);

Let’s now create the Stored Procedure

-- Create a stored procedure
DELIMITER //
CREATE PROCEDURE DisplayEmployeeData()
BEGIN
    -- Declare variables for cursor
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_ID INT;
    DECLARE first_Name VARCHAR(50);
    DECLARE last_Name VARCHAR(50);
    DECLARE job_Title VARCHAR(100);
    DECLARE dept VARCHAR(100);
    DECLARE sal DECIMAL(10, 2);

    -- Declare the cursor
    DECLARE EmployeeCursor CURSOR FOR
        SELECT EmployeeID, FirstName, LastName, JobTitle, Department, Salary
        FROM Employees;

    -- Declare continue handler
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Open the cursor
    OPEN EmployeeCursor;

    -- Debug statement
    SELECT 'Cursor opened.' AS DebugStatement;

    -- Loop through the cursor and display results
    read_loop: LOOP
        -- Fetch the current record into variables
        FETCH EmployeeCursor INTO emp_ID, first_Name, last_Name, job_Title, dept, sal;

        -- Exit the loop if no more records
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Display the current record
        SELECT CONCAT(
            'EmployeeID: ', emp_ID,
            ', Name: ', CONCAT(first_Name, ' ', last_Name),
            ', Job Title: ', job_Title,
            ', Department: ', dept,
            ', Salary: ', sal
        ) AS EmployeeDetails;
    END LOOP;

    -- Close the cursor
    CLOSE EmployeeCursor;

    -- Debug statement
    SELECT 'Cursor closed.' AS DebugStatement;

END //
DELIMITER ;

We can execute the Stored Procedure by CALL DisplayEmployeeData();

CALL DisplayEmployeeData();

Output

mysql> CALL DisplayEmployeeData();
+----------------+
| DebugStatement |
+----------------+
| Cursor opened. |
+----------------+
1 row in set (0.01 sec)

+-----------------------------------------------------------------------------------------------+
| EmployeeDetails                                                                               |
+-----------------------------------------------------------------------------------------------+
| EmployeeID: 1, Name: John Doe, Job Title: Software Engineer, Department: IT, Salary: 80000.00 |
+-----------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

+---------------------------------------------------------------------------------------------------+
| EmployeeDetails                                                                                   |
+---------------------------------------------------------------------------------------------------+
| EmployeeID: 2, Name: Jane Smith, Job Title: Data Analyst, Department: Analytics, Salary: 65000.00 |
+---------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

+----------------------------------------------------------------------------------------------------------------+
| EmployeeDetails                                                                                                |
+----------------------------------------------------------------------------------------------------------------+
| EmployeeID: 3, Name: Bob Johnson, Job Title: Project Manager, Department: Project Management, Salary: 95000.00 |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

+---------------------------------------------------------------------------------------------------+
| EmployeeDetails                                                                                   |
+---------------------------------------------------------------------------------------------------+
| EmployeeID: 4, Name: Alice Williams, Job Title: UX Designer, Department: Design, Salary: 75000.00 |
+---------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

+-------------------------------------------------------------------------------------------------------+
| EmployeeDetails                                                                                       |
+-------------------------------------------------------------------------------------------------------+
| EmployeeID: 5, Name: Charlie Brown, Job Title: System Administrator, Department: IT, Salary: 85000.00 |
+-------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

+----------------+
| DebugStatement |
+----------------+
| Cursor closed. |
+----------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Types of Cursors in SQL

In SQL, there are mainly two types of cursors: Implicit Cursors and Explicit Cursors.

Implicit Cursors:
- Description: Implicit cursors are automatically created by the database management system to process a SQL statement that returns only one row of data.
- Use Cases:
  - Typically used for queries that return a single row, like SELECT INTO or FETCH INTO.
  - Automatically used in SQL operations like UPDATE, DELETE, and INSERT, where the number of affected rows is one.
Explicit Cursors:
- Description: Explicit cursors are user-defined and provide more control over the processing of query result sets. They are explicitly created, opened, fetched, and closed by the user.
- Use Cases:
  - Suitable for queries that return multiple rows.
  - Allows users to control the fetch operations and navigate through the result set.
  - Commonly used in stored procedures and triggers where complex data processing is required.

SQL Cursor Exceptions

In SQL, cursor exceptions are conditions that can occur during the execution of cursor-related operations. Cursors are used to process query result sets, and various exceptions can occur during their lifecycle. Here are some common cursor exceptions:

CURSOR_ALREADY_OPEN Exception:
- Description: Raised when trying to open a cursor that is already open.
- Example Scenario: Attempting to open a cursor that is already in an open state.
NO_DATA_FOUND Exception:
- Description: Raised when a FETCH operation is attempted, but no rows are returned.
- Example Scenario: Fetching from a cursor, but there are no more rows to fetch.
TOO_MANY_ROWS Exception:
- Description: Raised when a SELECT INTO statement returns more than one row.
- Example Scenario: Using SELECT INTO expecting a single result but getting multiple rows.
CURSOR_NOT_FOUND Exception:
- Description: Raised when trying to close a cursor that is not open.
- Example Scenario: Attempting to close a cursor that was not opened.
INVALID_CURSOR Exception:
- Description: Raised when a cursor-related operation is attempted on an invalid cursor.
- Example Scenario: Performing operations on a cursor that has not been declared or is not open.
TOO_MANY_OPEN_CURSORS Exception:
- Description: Raised when the maximum number of open cursors is exceeded.
- Example Scenario: Opening more cursors than the database allows.

Types of SQL Cursor Locks

In the context of SQL cursors, locks are mechanisms that control access to the data while it’s being processed. Different types of locks provide varying levels of control over concurrent access to the data. Here are two types of locks commonly associated with SQL cursors:

READ-ONLY Locks:

Description: READ-ONLY locks prevent any modifications to the data being processed by the cursor. This is suitable when you don’t intend to update or modify the data, and you want to ensure it remains unchanged during the cursor operation.

Example:

DECLARE MyCursor CURSOR READ_ONLY FOR
    SELECT EmployeeID, FirstName, LastName
    FROM Employees;
OPEN MyCursor;
FETCH NEXT FROM MyCursor;
-- This cursor won't allow updates on the fetched rows
CLOSE MyCursor;

SCROLL_LOCKS:

Description: SCROLL_LOCKS provide more control by locking rows as they are read into the cursor. This ensures that updates or deletes made using the cursor will succeed, as the rows are locked during the entire cursor operation.

Example:

DECLARE MyCursor CURSOR SCROLL_LOCKS FOR
    SELECT EmployeeID, FirstName, LastName
    FROM Employees;
OPEN MyCursor;
FETCH NEXT FROM MyCursor;
-- This cursor allows updates on the fetched rows
CLOSE MyCursor;

It’s essential to choose the appropriate lock type based on your specific requirements. If you only need to read data and don’t plan to modify it, a READ-ONLY lock is sufficient. If you intend to update or delete rows based on cursor operations, using SCROLL_LOCKS can provide better control over data integrity. Always consider the implications of the chosen lock type on data concurrency and consistency.

Best Practices for Using Cursor in SQL

Using cursors in SQL should be approached with caution, as they can have performance implications and may not be the most efficient way to process data in a set-based language like SQL. Here are some best practices when working with cursors:

Avoid Cursors if Possible:
- Before using a cursor, explore alternative approaches like temp tables, table variables, or set-based operations. Sometimes, there’s a more efficient way to achieve the same result.
Use Cursors for Row-by-Row Processing Only:
- Cursors are designed for row-by-row processing. If you need to perform operations on each row individually, cursors might be appropriate.
Keep Transactions Short:
- If you must use a cursor, try to keep the transactions short. Holding locks for an extended period can lead to blocking issues and impact concurrency.
Avoid Nesting Cursors:
- Nesting cursors (using a cursor inside another cursor) can lead to complex and hard-to-maintain code. Consider using joins or subqueries instead.
Declare Cursors with the RIGHT Options:
- Declare cursors with the appropriate options, such as READ_ONLY or SCROLL_LOCKS, based on your specific requirements. This helps control the behavior of the cursor.
Fetch in Batches:
- If you need to fetch a large result set, consider fetching rows in smaller batches to reduce the impact on memory and improve performance.
Avoid Dynamic SQL Inside Cursors:
- Dynamic SQL inside a cursor can be harder to read, debug, and maintain. Whenever possible, avoid using dynamic SQL within a cursor.
Close Cursors Explicitly:
- Close cursors explicitly when you are done using them. This releases resources and can prevent unexpected behavior.

Conclusion: Cursor in SQL

This exploration of Cursors in SQL provided insights into their fundamental concepts, syntax, and practical applications. Cursors, crucial for row-by-row processing in a result set, offer granular control over data manipulation.

The article outlined the five steps in creating a cursor, accompanied by a MySQL example demonstrating their usage in a stored procedure with the “Employees” table.

Implicit and explicit cursor types were discussed, along with cursor exceptions and SQL cursor locks. Best practices were highlighted, encouraging thoughtful cursor usage for optimized data processing.

Related Articles: