SQL Language

Cursors in SQL

Cursors

A cursor is a temporary work area created in system memory when an SQL statement is executed. A cursor is a set of rows together with a pointer that identifies a current row. It is a database object to retrieve data from a result set one row at a time. It is useful when we want to manipulate the record of a table in a singleton method, in other words one row at a time. In other words, a cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.

Types of Cursors

There are the following two types of Cursors:

  1. Implicit Cursor
  2. Explicit Cursor

Implicit Cursor

These types of cursors are generated and used by the system during the manipulation of a DML query (INSERT, UPDATE and DELETE). An implicit cursor is also generated by the system when a single row is selected by a SELECT command.

Explicit Cursor

This type of cursor is generated by the user using a SELECT command. An explicit cursor contains more than one row, but only one row can be processed at a time. An explicit cursor moves one by one over the records. An explicit cursor uses a pointer that holds the record of a row. After fetching a row, the cursor pointer moves to the next row.

Main components of Cursors

Each cursor contains the followings 5 parts:

  1. Declare Cursor: In this part we declare variables and return a set of values.
  2. Open: This is the entering part of the cursor.
  3. Fetch: Used to retrieve the data row by row from a cursor.
  4. Close: This is an exit part of the cursor and used to close a cursor.
  5. Deallocate: In this part we delete the cursor definition and release all the system resources associated with the cursor.

Syntax of a Cursor

  1. DECLARE @Variable  nvarchar(50)  /* Declare All Required Variables */  
  2. DECLARE Cursor_Name CURSOR       /* Declare Cursor Name*/  
  3.  [LOCAL | GLOBAL]               /* Define  Cursor Scope  */  
  4.  [FORWARD_ONLY | SCROLL]                /* Define  Movement Direction  of Cursor  */  
  5.  [ KEYSET | DYNAMIC |STATIC | FAST_FORWARD] /* Define basic type of cursor   */  
  6.  [  SCROLL_LOCKS | OPTIMISTIC |READ_ONLY ]    /*   Define Locks  */  
  7.   
  8.  OPEN Cursor_Name               /* Open Cursor  */  
  9.  FETCH NEXT FROM Cursor_Name    /*  Fetch data From Cursor  */  
  10. Implement SQL QUery                          
  11.  CLOSE Cursor_Name              /*  Clsoe The Cursor  */              
  12. DEALLOCATE Cursor_Name          /* Deallocate all resources and Memory */  
Now we will explain 4 important terminologies of cursors.

Cursor Scope

Microsoft SQL Server supports the GLOBAL and LOCAL keywords on the DECLARE CURSOR statement to define the scope of the cursor name.
  1. GLOBAL: specifies that the cursor name is global to the connection.
  2. LOCAL: specifies that the cursor name is local to the Stored Procedure, trigger or query that holds the cursor.

Data Fetch Option in Cursors

Microsoft SQL Server supports the following two fetch options for data:

  1. FORWARD_ONLY: Specifies that the cursor can only be scrolled from the first to the last row.
  2. SCROLL: It provides 6 options to fetch the data (FIRST, LAST, PRIOR, NEXT, RELATIVE and ABSOLUTE).

Types of cursors

Microsoft SQL Server supports the following 4 types of cursors.

  1. STATIC CURSOR: A static cursor populates the result set during cursor creation and the query result is cached for the lifetime of the cursor. A static cursor can move forward and backward.

  2. FAST_FORWARD: This is the default type of cursor. It is identical to the static except that you can only scroll forward.

  3. DYNAMIC: In a dynamic cursor, additions and deletions are visible for others in the data source while the cursor is open.

  4. KEYSET: This is similar to a dynamic cursor except we can't see records others add. If another user deletes a record, it is inaccessible from our record set.

Types of Locks

Locking is the process by which a DBMS restricts access to a row in a multi-user environment. When a row or column is exclusively locked, other users are not permitted to access the locked data until the lock is released. It is used for data integrity. This ensures that two users cannot simultaneously update the same column in a row.

Microsoft SQL Server supports the following three types of Locks.

  1. READ ONLY: Specifies that the cursor cannot be updated.

  2. SCROLL_LOCKS: Provides data integrity into the cursor. It specifies that the cursor will lock the rows as they are read into the cursor to ensure that updates or deletes made using the cursor will succeed.

  3. OPTIMISTIC: Specifies that the cursor does not lock rows as they are read into the cursor. So, the updates or deletes made using the cursor will not succeed if the row has been updated outside the cursor.

First we create a table as in the following:

  1. GO  
  2.   
  3. CREATE TABLE [dbo].[Employee](  
  4.     [Emp_ID] [int] NOT NULL,  
  5.     [Emp_Name] [nvarchar](50) NOT NULL,  
  6.     [Emp_Salary] [int] NOT NULL,  
  7.     [Emp_City] [nvarchar](50) NOT NULL,  
  8.  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED   
  9. (  
  10.     [Emp_ID] ASC  
  11. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  12. ) ON [PRIMARY]  
  13.   
  14. GO  
Now insert some values into the table as in the following:
  1. Insert into Employee  
  2. Select 1,'Pankaj',25000,'Alwar' Union All  
  3. Select 2,'Rahul',26000,'Alwar' Union All  
  4. Select 3,'Sandeep',25000,'Alwar' Union All  
  5. Select 4,'Sanjeev',24000,'Alwar' Union All  
  6. Select 5,'Neeraj',28000,'Alwar' Union All  
  7. Select 6,'Naru',20000,'Alwar' Union All  
  8. Select 7,'Omi',23000,'Alwar'   
Select all values from the table as in the following:

Select all value from table

Example 1
  1. SET NOCOUNT ON  
  2. DECLARE @EMP_ID INT  
  3. DECLARE @EMP_NAME NVARCHAR(MAX)  
  4. DECLARE @EMP_SALARY INT  
  5. DECLARE @EMP_CITY NVARCHAR(MAX)  
  6.   
  7. DECLARE EMP_CURSOR CURSOR  
  8. LOCAL  FORWARD_ONLY  FOR  
  9. SELECT * FROM Employee  
  10. OPEN EMP_CURSOR  
  11. FETCH NEXT FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY  
  12. WHILE @@FETCH_STATUS = 0  
  13. BEGIN  
  14. PRINT  'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+  '  EMP_NAME '+@EMP_NAME +'  EMP_SALARY '  +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  +  '  EMP_CITY ' +@EMP_CITY  
  15. FETCH NEXT FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY  
  16. END  
  17. CLOSE EMP_CURSOR  
  18. DEALLOCATE EMP_CURSOR  
Output

    EMP_ID: 1 EMP_NAME Pankaj EMP_SALARY 25000 EMP_CITY Alwar
    EMP_ID: 2 EMP_NAME Rahul EMP_SALARY 26000 EMP_CITY Alwar
    EMP_ID: 3 EMP_NAME Sandeep EMP_SALARY 25000 EMP_CITY Alwar
    EMP_ID: 4 EMP_NAME Sanjeev EMP_SALARY 24000 EMP_CITY Alwar
    EMP_ID: 5 EMP_NAME Neeraj EMP_SALARY 28000 EMP_CITY Alwar
    EMP_ID: 6 EMP_NAME Naru EMP_SALARY 20000 EMP_CITY Alwar
    EMP_ID: 7 EMP_NAME Omi EMP_SALARY 23000 EMP_CITY Alwar

This is a simple example of a cursor that prints the value of a table.

Example 2 (SCROLL)

  1. SET NOCOUNT ON  
  2. DECLARE @EMP_ID INT  
  3. DECLARE @EMP_NAME NVARCHAR(MAX)  
  4. DECLARE @EMP_SALARY INT  
  5. DECLARE @EMP_CITY NVARCHAR(MAX)  
  6.   
  7. DECLARE EMP_CURSOR CURSOR  
  8. LOCAL  SCROLL  FOR  
  9. SELECT * FROM Employee  
  10. OPEN EMP_CURSOR  
  11. FETCH NEXT FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY  
  12.   
  13. FETCH RELATIVE 3 FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY  
  14. PRINT  'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+  '  EMP_NAME '+@EMP_NAME +'  EMP_SALARY '  +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  +  '  EMP_CITY ' +@EMP_CITY  
  15. FETCH ABSOLUTE  3 FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY  
  16.   
  17. PRINT  'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+  '  EMP_NAME '+@EMP_NAME +'  EMP_SALARY '  +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  +  '  EMP_CITY ' +@EMP_CITY  
  18. FETCH FIRST FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY  
  19. PRINT  'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+  '  EMP_NAME '+@EMP_NAME +'  EMP_SALARY '  +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  +  '  EMP_CITY ' +@EMP_CITY  
  20. FETCH LAST FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY  
  21. PRINT  'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+  '  EMP_NAME '+@EMP_NAME +'  EMP_SALARY '  +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  +  '  EMP_CITY ' +@EMP_CITY  
  22. FETCH PRIOR FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY  
  23. PRINT  'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+  '  EMP_NAME '+@EMP_NAME +'  EMP_SALARY '  +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  +  '  EMP_CITY ' +@EMP_CITY  
  24. FETCH NEXT FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY  
  25. PRINT  'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+  '  EMP_NAME '+@EMP_NAME +'  EMP_SALARY '  +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  +  '  EMP_CITY ' +@EMP_CITY  
  26.   
  27.   
  28. CLOSE EMP_CURSOR  
  29. DEALLOCATE EMP_CURSOR  
Output:

    EMP_ID: 4 EMP_NAME Sanjeev EMP_SALARY 24000 EMP_CITY Alwar
    EMP_ID: 3 EMP_NAME Sandeep EMP_SALARY 25000 EMP_CITY Alwar
    EMP_ID: 1 EMP_NAME Pankaj EMP_SALARY 25000 EMP_CITY Alwar
    EMP_ID: 7 EMP_NAME Omi EMP_SALARY 23000 EMP_CITY Alwar
    EMP_ID: 6 EMP_NAME Naru EMP_SALARY 20000 EMP_CITY Alwar
    EMP_ID: 7 EMP_NAME Omi EMP_SALARY 23000 EMP_CITY Alwar

In this example we will use SCROLL to fetch the data. This example contains all six modes of SCROLL (FIRST, LAST, PRIOR, NEXT, RELATIVE and ABSOLUTE).

Example 3 (STATIC CURSOR)

  1. SET NOCOUNT ON  
  2. DECLARE @EMP_ID INT  
  3. DECLARE @EMP_NAME NVARCHAR(MAX)  
  4. DECLARE @EMP_SALARY INT  
  5. DECLARE @EMP_CITY NVARCHAR(MAX)  
  6.   
  7. DECLARE EMP_CURSOR CURSOR  
  8.  STATIC  FOR  
  9. SELECT * FROM Employee  
  10. OPEN EMP_CURSOR  
  11. FETCH NEXT FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY  
  12. WHILE @@FETCH_STATUS = 0  
  13. BEGIN  
  14. If @EMP_ID%2=0  
  15. BEGIN  
  16. PRINT  'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+  '  EMP_NAME '+@EMP_NAME +'  EMP_SALARY '  +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  +  '  EMP_CITY ' +@EMP_CITY  
  17. END  
  18. FETCH  FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY  
  19. END  
  20. CLOSE EMP_CURSOR  
  21. DEALLOCATE EMP_CURSOR  

Output

    EMP_ID: 2 EMP_NAME Rahul EMP_SALARY 26000  EMP_CITY Alwar
    EMP_ID: 4 EMP_NAME Sanjeev EMP_SALARY 24000 EMP_CITY Alwar
    EMP_ID: 6 EMP_NAME Naru EMP_SALARY 20000 EMP_CITY Alwar

Example 4

  1. SET NOCOUNT ON  
  2. DECLARE @EMP_ID INT  
  3. DECLARE @EMP_NAME NVARCHAR(MAX)  
  4. DECLARE @EMP_SALARY INT  
  5. DECLARE @EMP_CITY NVARCHAR(MAX)  
  6.   
  7. DECLARE EMP_CURSOR CURSOR  
  8.  STATIC  FOR  
  9. SELECT * FROM Employee  
  10. OPEN EMP_CURSOR  
  11. FETCH NEXT FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY  
  12. WHILE @@FETCH_STATUS = 0  
  13. BEGIN  
  14. If @EMP_ID%2=0  
  15. BEGIN  
  16. UPDATE Employee SET Emp_Salary=15000 WHERE CURRENT OF EMP_CURSOR  
  17. END  
  18. FETCH  FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY  
  19. END  
  20. CLOSE EMP_CURSOR  
  21. DEALLOCATE EMP_CURSOR  
Output

    Msg 16929, Level 16, State 1, Line 16
    The cursor is READ ONLY.
    The statement has been terminated.
    Msg 16929, Level 16, State 1, Line 16
    The cursor is READ ONLY.
    The statement has been terminated.
    Msg 16929, Level 16, State 1, Line 16
    The cursor is READ ONLY.
    The statement has been terminated.

When executing this cursor, we will get an error because the static cursor does not allow modifications in data.

Example 5 (DYNAMIC CURSOR)

  1. SET NOCOUNT ON  
  2. DECLARE @EMP_ID INT  
  3. DECLARE @EMP_NAME NVARCHAR(MAX)  
  4. DECLARE @EMP_SALARY INT  
  5. DECLARE @EMP_CITY NVARCHAR(MAX)  
  6.   
  7. DECLARE EMP_CURSOR CURSOR  
  8.  DYNAMIC  FOR  
  9. SELECT * FROM Employee  
  10. OPEN EMP_CURSOR  
  11. FETCH NEXT FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY  
  12. WHILE @@FETCH_STATUS = 0  
  13. BEGIN  
  14. If @EMP_ID%2=0  
  15. BEGIN  
  16. UPDATE Employee SET Emp_Salary=15000 WHERE CURRENT OF EMP_CURSOR  
  17. END  
  18. FETCH  FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY  
  19. END  
  20. CLOSE EMP_CURSOR  
  21. DEALLOCATE EMP_CURSOR  
  22. SELECT * FROM Employee  
Output

Output

Example 6
  1. SET NOCOUNT ON  
  2. DECLARE @EMP_ID INT  
  3. DECLARE @EMP_NAME NVARCHAR(MAX)  
  4. DECLARE @EMP_SALARY INT  
  5. DECLARE @EMP_CITY NVARCHAR(MAX)  
  6.   
  7. DECLARE EMP_CURSOR CURSOR  
  8. FAST_FORWARD  FOR  
  9. SELECT * FROM Employee  
  10. OPEN EMP_CURSOR  
  11. FETCH NEXT FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY  
  12. WHILE @@FETCH_STATUS = 0  
  13. BEGIN  
  14. If @EMP_ID%2=0  
  15. BEGIN  
  16. UPDATE Employee SET Emp_Salary=15000 WHERE CURRENT OF EMP_CURSOR  
  17. END  
  18. FETCH  FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY  
  19. END  
  20. CLOSE EMP_CURSOR  
  21. DEALLOCATE EMP_CURSOR  
Output

    Msg 16929, Level 16, State 1, Line 16
    The cursor is READ ONLY.
    The statement has been terminated.
    Msg 16929, Level 16, State 1, Line 16
    The cursor is READ ONLY.
    The statement has been terminated.
    Msg 16929, Level 16, State 1, Line 16
    The cursor is READ ONLY.
    The statement has been terminated.

A FAST_FORWARD cursor also reads as a static cursor. We cannot modify data in a FAST_FORWARD cursor.

Example 7

  1. SET NOCOUNT ON  
  2. DECLARE @EMP_ID INT  
  3. DECLARE @EMP_NAME NVARCHAR(MAX)  
  4. DECLARE @EMP_SALARY INT  
  5. DECLARE @EMP_CITY NVARCHAR(MAX)  
  6.   
  7. DECLARE EMP_CURSOR1 CURSOR  
  8. KEYSET scroll  
  9. FOR  
  10. SELECT  EMP_ID ,EMP_NAME,EMP_SALARY,EMP_CITY FROM Employee  order by Emp_Id  
  11. OPEN EMP_CURSOR1  
  12. IF @@CURSOR_ROWS > 0  
  13.      BEGIN   
  14. FETCH NEXT FROM EMP_CURSOR1 INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY  
  15. WHILE @@FETCH_STATUS = 0  
  16. BEGIN  
  17.   
  18. If @EMP_ID%2=0  
  19. UPDATE Employee SET EMP_NAME='PANKAJ KUMAR CHOUDHARY' WHERE CURRENT OF EMP_CURSOR1  
  20. FETCH NEXT FROM EMP_CURSOR1 INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY  
  21. END  
  22. END  
  23. CLOSE EMP_CURSOR1  
  24. DEALLOCATE EMP_CURSOR1  
  25. SET NOCOUNT OFF  
  26. SELECT * FROM Employee  
Output

result

Up Next
    Ebook Download
    View all
    Basic SQL Queries
    Read by 10.6k people
    Download Now!
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs