What Is a Cursor in SQL?

A cursor is a control structure in SQL that enables traversal over the rows in a result set. It is a way to retrieve data from a database and process it one row at a time, rather than retrieving all the data at once and then processing it.

When a SELECT statement is executed, the result set is stored in a cursor in SQL, which can then be used to fetch and process the rows one at a time. The cursor acts as a pointer to the current row in the result set, allowing the developer to move forward and backwards through the result set.

You can also explore: SQL Tutorial : Basic to Advance

In SQL, there are two types of cursors:

• Implicit cursors: These are automatically created by the database management system (DBMS) when a SELECT statement is executed without the use of an explicit cursor.
• Explicit cursors: These are created by the developer using the DECLARE CURSOR statement. They are used to retrieve and process data in a more fine-grained way and also can be parameterized.

Cursors in SQL are commonly used in PL/SQL and T-SQL to fetch and process data from the database, but they can also be used in other languages that have an SQL interface.

A cursor in SQL, is a mechanism that allows for the traversal and manipulation of the rows in a result set returned by a SELECT statement. A cursor can be used to retrieve data row-by-row and perform various operations on each row, such as updating or deleting the data. Cursors can also be used to process large result sets in smaller chunks, rather than loading the entire result set into memory at once. Cursors are typically used in stored procedures and functions in SQL.

You can also explore: What are TCL Commands in SQL?

TYPES OF CURSORS IN SQL

IMPLICIT CURSORS

An implicit cursor is a cursor that is automatically created and managed by the database management system (DBMS) when certain SQL statements are executed. These statements include SELECT INTO, INSERT, UPDATE, and DELETE. The DBMS uses an implicit cursor to handle the rows affected by these statements, and it automatically opens, fetches, and closes the cursor as needed.

Following is an example where an SQL statement creates an implicit cursor to handle the rows affected by the update statement:

UPDATE Employees SET Salary = Salary * 1.1 WHERE Department = 'IT';

The implicit cursor is used to locate the rows in the Employees table that have a Department value of ‘IT’ and update their Salary values by increasing them by 10%. The DBMS automatically opens the cursor, fetches the rows that match the WHERE clause condition, performs the update operation, and then closes the cursor.

You can also explore: Difference Between WHERE and HAVING Clause

It’s important to note that implicit cursors are created and managed by the DBMS and not directly by the user. Therefore, the user does not have direct control over the cursor and cannot perform operations like FETCH and CLOSE. Also, It’s more efficient than an explicit cursor since you don’t need to declare and manage it.

More examples of implicit cursors in SQL are stated below:

SELECT INTO Statement:

SELECT COUNT(*) INTO @total_employees FROM Employees;

In the above example, the SELECT INTO statement creates an implicit cursor to handle the result set returned by the SELECT COUNT(*) statement. The cursor is used to retrieve the count of rows in the Employees table and store it in the variable @total_employees.

INSERT Statement:

INSERT INTO Orders(OrderID, CustomerID, OrderDate) VALUES(123, 'C001', GETDATE());

The statement above creates an implicit cursor to handle the row that will be inserted into the Orders table. The cursor is used to insert the new row with the specified values for OrderID, CustomerID, and OrderDate.

UPDATE Statement:

UPDATE Employees SET Salary = Salary * 1.1 WHERE Department = 'IT';

This statement creates an implicit cursor to handle the rows that will be updated in the Employees table. The cursor is used to locate the rows that have a Department value of ‘IT’, and update their Salary values by increasing them by 10%.

DELETE Statement:

DELETE FROM Customers WHERE Country = 'USA'

This statement creates an implicit cursor to handle the rows that will be deleted from the Customers table. The cursor is used to locate the rows that have a Country value of ‘USA’ and delete them.

In the examples above, the DBMS automatically opens the cursor, performs the operation on the rows, and closes the cursor. The user does not have to declare or manage the cursor explicitly.

EXPLICIT CURSORS

An explicit cursor in SQL is a programming construct that allows for the retrieval and manipulation of rows from a result set one at a time. It is defined by the programmer and can be used to perform operations such as fetching, updating, or deleting rows.

Explicit cursors are created using the DECLARE statement and are typically used in situations where the result set is too large to be handled efficiently with a SELECT statement, or when the programmer needs to perform operations on each row in the result set in a specific order.

An explicit cursor has four basic operations:

• Open: The cursor is opened and the result set is populated.
• Fetch: The cursor retrieves a single row from the result set and makes it available for processing.
• Close: The cursor is closed and the result set is no longer available for processing.
• Deallocate: The cursor is deallocated and the memory it was using is freed up.

Explicit cursors can be useful for performing complex data processing tasks, but they can also be a performance bottleneck. In most cases, it’s recommended to use a set-based operation like a join or a subquery instead of a cursor, if it’s possible.

You must explore: How to use DISTINCT in SQL

Examples of explicit cursors in SQL are stated below:

Declare a cursor and retrieve all rows from a table:

DECLARE cur CURSOR FOR SELECT * FROM Customers;

Open a cursor and fetch the first row:

OPEN cur;
FETCH NEXT FROM cur;

Loop through all rows in the cursor:

WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT FROM cur;
END

Close the cursor and deallocate memory:

CLOSE cur;
DEALLOCATE cur;

The cursor can be resource-intensive and slow down the performance of the database, so it should be used only when it’s really necessary.

IMPLEMENTATION OF CURSORS IN PL/SQL-

Implementations of cursors in PL/SQL, both of them are the same only a few additional features are being added which is equal to equivalent. Below are the implementation steps:

• DECLARE THE CURSOR: The cursor is declared using the DECLARE statement, specifying the SELECT statement which will be used to populate the result set.

DECLARE
cursor cursor_name IS SELECT column_name FROM table_name;

• OPEN THE CURSOR: The cursor is opened using the OPEN statement which populates the result set and hence makes it available for processing.

OPEN cursor_name;

• FETCH ROWS FROM THE CURSOR: Rows are retrieved from the cursor using the FETCH statement, which moves the cursor to the next row in the result set and makes the row’s data available for processing.

FETCH cursor_name INTO variable_name;

• PROCESS THE FETCHED ROW: The data of the fetched row can be processed using the variable_name.
• CLOSE THE CURSOR: When all rows in the result set have been processed, the cursor is closed using the CLOSE statement. This releases the resources associated with the cursor.

CLOSE cursor_name;

• DEALLOCATE THE CURSOR: The cursor is deallocated using the DEALLOCATE statement, which releases the memory used by the cursor.

DEALLOCATE cursor;

It’s also possible to use a loop like “WHILE” or “FOR” to fetch all rows from the cursor, and it’s important to close and deallocate the cursor after the process is finished to avoid resource leaks.

PL/SQL allows you to also use Implicit Cursor, which is automatically created and managed by Oracle Database to process the SQL statement. It’s worth noting that explicit cursors are resource-intensive and can slow down the performance of the database, so it’s good practice to use them only when it’s truly necessary and in an optimized way.

A detailed example of implementing cursors in PL/SQL is illustrated below-

In this example, we first declare a cursor named “emp_cursor” that retrieves all rows from the “employees” table. We also declare a variable “emp_record” of the “employees” table’s row type, which will store the fetched row.

You can also explore: How to use WHERE clause in SQL

Then, we open the cursor using the OPEN statement. We use a LOOP statement to fetch each row from the cursor using the FETCH statement and store it in the “emp_record” variable. The loop exits when the cursor reaches the end of the result set, indicated by the NOTFOUND attribute. Inside the loop, we process the fetched row by displaying the first name and last name of the employee.

Finally, we close the cursor using the CLOSE statement. It’s important to close the cursor after it’s finished processing to release the resources associated with it.

In this example, the cursor is not deallocated after closing, but it’s also possible to deallocate a cursor using DEALLOCATE statement and it’s good practice to do so.

Must explore: How to use LIMIT clause in SQL

This is an example of how cursors can be used in PL/SQL, and you can use cursors to perform various other operations such as updating or deleting rows or even using the cursor as a parameter in a stored procedure.

Author: Nimisha