CURSOR in Transact SQL
A cursor is a data structure that allows you to go through the set of rows returned by a
SELECT and process each record one by one.
A cursor consists of different steps that are collected in the following syntax:
-- Declaring variables DECLARE variables -- Declaring Cursor Name DECLARE cursorName CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] -- Select to itirate FOR SELECT [ FOR UPDATE [ OF <Column_Name>[ ,…n ] ] ] [;] -- Cursor Opening OPEN cursorName -- Record iteration and insertion into variables FETCH NEXT FROM cursorName -- Cursor Closing CLOSE cursorName -- Cursor dellocating DEALLOCATE cursorName
Taking into account the different arguments that a cursor can receive, we can categorize them according to:
- Cursor scope.
- Data recovery options.
- Types of cursors.
- Types of constraints.
LOCAL: The scope is limited only to the stored procedure, trigger or batch that defined it.
GLOBAL: The scope is at the connection level, and can be used in any stored procedure, trigger or batch.
Data recovery options
FORWARD_ONLY: specifies that the cursor can only move forward from the first to the last row. It only allows as a data retrieval option:
FETCH NEXTto read a record and go to the next.
SCROLL provides the following data recovery options:
If the SCROLL argument is omitted, the cursor would be
FORWARD_ONLY, allowing only the option
STATIC: they allow you to move forwards and backwards. These cursors never reflect the changes to the rows, always displaying the result set equal to the time the cursor was opened.
DYNAMIC: Unlike the static ones, the dynamic cursors will reflect all the modifications made in the rows of the result set.
READ_ONLYwith performance optimizations.
KEYSET: cursor halfway between a static and a dynamic cursor. This type of cursors will not always detect changes in the membership and order of the result set, but they will detect changes in the values of the rows of the result set.
READ ONLY: Read only, preventing updates through the cursor. This option removes the default ability to update the cursor.
SCROLL_LOCKS: locks rows as they are read, to ensure data integrity and that UPDATES AND DELETES are successful.
OPTIMISTIC: does not lock rows as they are read, preventing UPDATES AND DELETES from succeeding if any updates are made outside the cursor.
TYPE_WARNING sends a message if the cursor is converted from one type to another.
The following example of cursor, has the objective of reading the records corresponding to the relationship between the table
Orders. We will use its creation to briefly explain the essential steps required.
--Declaring Variables DECLARE @name VARCHAR (20), @lastName VARCHAR (50), @product VARCHAR (50), @price FLOAT -- Declaring the cursor name and Select to Iterate DECLARE cCustomersOrders CURSOR FOR SELECT c.name, c.lastName, o.product, o.price FROM Customers c INNER JOIN Orders o ON c.customerId = o.customerId -- Cursor opening OPEN cCustomersOrders -- Record iteration and insertion into variables FETCH NEXT FROM cCustomersOrders INTO @name, @lastName, @product, @price WHILE @@FETCH_STATUS = 0 BEGIN SELECT @name, @lastName, @product, @price FETCH NEXT FROM cCustomersOrders INTO @name, @lastName, @product, @price END -- Cursor closing CLOSE cCustomersOrders; -- Cursor deallocating DEALLOCATE cCustomersOrders;
The returned result, selecting and executing all its instructions is the following:
|Peter||Davis||Xiaomi Mi 11||286,95|
|Mavin||Pettitt||Play Station 5||549,95|
|Mavin||Pettitt||Xbox series X||499,99|
|Janice||Haynes||MacBook Pro M1||2249,5|
|Helen||Ward||Echo DOT 3||39,99|
|Helen||Ward||Echo DOT 4||59,99|
Let’s move on to a brief explanation of each step of our cursor:
STEP 1: Declaring variables
--Declaring Variables DECLARE @name VARCHAR (20), @lastName VARCHAR (50), @product VARCHAR (50), @price FLOAT
The first step is to declare the necessary variables to fill in with the data returned by the cursor. Note that the data type of the variable must be compatible with the data type of the field whose value you want to store.
STEP 2: Declaring the cursor name and SELECT
-- Declaring the cursor name and Select to Iterate DECLARE cCustomersOrders CURSOR FOR SELECT c.name, c.lastName, o.product, o.price FROM Customers c INNER JOIN Orders o ON c.customerId = o.customerId
The next step is to declare the name of the cursor and the
SELECT that it will execute and then iterate over to loop through each record in the result set. Optionally, this step is subject to the use of various options and arguments that will define the cursor as seen at the beginning of this section.
STEP 3: Cursor opening
-- Cursor opening OPEN cCustomersOrders
STEP 4: Iterating records and inserting into variables
-- Record iteration and insertion into variables FETCH NEXT FROM cCustomersOrders INTO @name, @lastName, @product, @price WHILE @@FETCH_STATUS = 0 BEGIN SELECT @name, @lastName, @product, @price FETCH NEXT FROM cCustomersOrders INTO @name, @lastName, @product, @price END
The next step consists of iterating through the records obtained in the query and inserting the returned data into the declared variables. The loop works as follows:
FETCHis a command that places the cursor at a position. Once the first
NEXTrecord has been read and processed, it places the cursor on the next row for processing.
Once the first record is processed, if everything goes well, the variables will be filled with the values returned by the query while the
FETCH_STATUSfunction will return a 0, indicating that the row has been processed correctly.
As long as
FETCH_STATUSreturns 0, each record will be read and processed in a loop until the last row of the result set is iterated over.
STEP 5: Cursor closing
-- Cursor closing CLOSE cCustomersOrders;
Once the last row has been processed, it is necessary to exit the cursor by closing it. This way, the result set cannot be traversed again until it is reopened.
STEP 6: Cursor Deallocating
--Cursor Deallocating DEALLOCATE cCustomersOrders;
The last step will be to remove the definition of the cursor and release all system resources associated with it, removing it from the memory.