SQL - Cursors

---

A database cursor solves the problem of impedance mismatch. Its acts as a filter between the result of a SQL query and the statements that process this result.

Cursors in SQL

A Cursor is a temporary memory that is allocated by the database server at the time of performing the Data Manipulation Language operations such as INSERT, UPDATE and DELETE etc., on a table. It holds the multiple rows returned by the SQL statement. You can use it to retrieve and manipulate data stored in the SQL tables.

For example, whenever we perform an insert operation on a table, the cursor holds data that is to be inserted. Using cursors one can perform multiple operations on each row of a result set, with or without returning the original data.

Types of Cursor in SQL

There are four types of cursor in SQL server as listed below −

Forward-only Cursor

The Forward-only(or non-scrollable) cursor is typically known as the default cursor in an SQL server. It can scroll through the result set only in forward direction. The forward-only cursor does not support the scrolling functionality, an ability to move forward and backward in the result set.

This type of cursor is also known as fire house cursors and support only fetching the rows from the beginning to the end of the result set.

Static Cursor

A Static cursor is one that shows the result set exactly as it was, when the cursor was opened. It will not display any updated, deleted, or inserted records after opening it.

The cursor does not reflect any changes made in the database that affect either the membership or the result set. A static cursor does not display new rows inserted into the database after the cursor was opened, even if they match the search conditions of the cursor SELECT statement.

Note − The SQL server operations like UPDATE, DELETE, and INSERT are not reflected in a static cursor (unless the cursor is closed and reopened), and not even the modification is made using the same connection that operands the cursor. In SQL-server the Static cursors are always read-only(they can't be updated).

Keyset Cursor

In an SQL server, the Keyset cursor provides a functionality between the static and a dynamic cursor that can detect changes. The keyset cursors have a scrolling ability that can only move from the first row to the last row and vice-versa.

When we open the cursor the order of the rows and the membership fixed, and the set of keys that identifies the rows uniquely will be stored in a table under the tempdb database. The tempdb database is one of the system database, that is used by many activities in SQL server to temporary store data.

Dynamic Cursor

A Dynamic cursor is an inverse (or opposite) of a static cursor. It reflects all the changes made to the rows in their result set when scrolling through the cursor. All updates, insert, and delete statements made by all the users are visible through the cursor. The updates (or changes) are visible immediately if they are made through the cursor using either an API function or the Transact-SQL statement.

Life cycle of the cursor

Following is the diagram of the SQL server cursor life cycle −

• The first stage in the life cycle of a cursor is to declare it, we can do so, using the Declare keyword, we need to specify the name and the data type of the Cursor after the Declare keyword along with the SELECT statement.

• Once we declare the cursor we need to open it to store and retrieve data from the result set, using the Open keyword.

• In the third stage we retrieve the rows using the Fetch keyword.

• Once we perform desired operations on the retrieved data we need to close the cursor.

• The lase stage of the lifecycle is Deallocate where we erase the definition of the cursor.

Example

Assume we have created a table with the name Customers using the CREATE TABLE statement as follows −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Now let’s insert some records into the Customers table using the INSERT statement as follows −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
 (3, 'kaushik', 23, 'Kota', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

The following SQL query creates the Cursor on the Customers table −

DECLARE MY_CUSR CURSOR FOR SELECT * FROM CUSTOMERS;

Output

Following is the output of the above SQL query −

Commands completed successfully.

Since the cursor has been created on the Customers table, let's fetch some records and deallocate the cursor.

OPEN MY_CUSR;
FETCH NEXT FROM MY_CUSR;
CLOSE MY_CUSR;
DEALLOCATE MY_CUSR;

Output

This will generate the following output −

+----+----------+-----------+-------------+
| ID | NAME     | ADDRESS   | SALARY      |
+----+----------+-----------+-------------+
|  1 | Ramesh   | Ahmedabad | 2000.00     |
+----+----------+-----------+-------------+

Example

Let us create another cursor on the CUSTOMERS table that fetches and prints data −

DECLARE @id INT, @name NVARCHAR(50), @sal INT 
DECLARE @Counter INT  
SET @Counter = 1  
   
DECLARE PrintData CURSOR  
FOR  
SELECT id, name, salary FROM customers
OPEN PrintData   
FETCH NEXT FROM PrintData INTO  @id, @name, @sal  
  
WHILE @@FETCH_STATUS = 0  
   BEGIN  
      IF @Counter = 1  
         BEGIN  
            PRINT 'id' + CHAR(9) + 'name' + CHAR(9) + CHAR(9) + 'sal'  
            PRINT '--------------------------'  
         END     
      PRINT CAST(@id AS NVARCHAR(10)) + CHAR(9) + @name + CHAR(9) + CAST(@sal AS NVARCHAR(10))        
         SET @Counter = @Counter + 1     
         FETCH NEXT FROM PrintData INTO @id, @name, @sal  
   END 
CLOSE PrintData    
DEALLOCATE PrintData

Output

This will generate the following output −

+----+----------+-----------+
| id | name     | sal       |
+----+----------+-----------+
|  1 | Ramesh   | 2000      |
|  2 | Khilan   | 1500      |
|  3 | kaushik  | 2000      |
|  4 | Chaitali | 6500      |
|  5 | Hardik   | 8500      |
|  6 | Komal    | 4500      |
|  7 | Muffy    | 10000     |
+----+----------+-----------+