Cursor in SQL – Types, Uses, and Terminologies

1. Objective – Cursor in SQL

In our last SQL Tutorial, we discussed SQL Query Optimization. Today, we will see Cursor in SQL. Moreover, in this SQL Cursor tutorial, we will see parts and terminologies of SQL Cursor. Also, we will see different types of Cursor in SQL and Uses of SQL Cursor. At last, we will learn different kinds of lock with the help of SQL Cursor example.

So, let’s start the SQL Cursor tutorial.

Cursor in SQL - Types, Uses, and Terminologies

Cursor in SQL – Types, Uses, and Terminologies

2. What is the Cursor in SQL?

It is a temporary area for work in memory system while the execution of a statement is done. A Cursor in SQL is an arrangement of rows together with a pointer that recognizes a present row. It is a database object to recover information from a result set one row at once. It is helpful when we need to control the record of a table in a singleton technique, at the end of the day one row at any given moment. The arrangement of columns the cursor holds is known as the dynamic set.

Let’s discuss SQL Create, Alter, and Drop Table

Types of Cursors in SQL 

  • Implicit Cursor
  • Explicit Cursor

a. Implicit Cursor

These sorts of Cursors in SQL are produced and utilized by the framework amid the control of a DML inquiry (INSERT, UPDATE and DELETE). A certain cursor is likewise created by the framework when a solitary row is chosen by a SELECT charge.

b. Explicit Cursor

This kind of cursor is produced by the user utilizing a SELECT charge. This cursor contains in excess of one row. However, just a single row can be prepared at once. An express cursor moves one by one finished the records. It uses a pointer that holds the record of a column. Subsequent to bringing a row, the cursor pointer moves to the following column.
Do you know about SQL Date Functions

3. Parts of a Cursor in SQL

Every Cursor in SQL contains the followings 5 sections:

  • Declare Cursor: In this part, we declare variables and restore an arrangement of values.
  • Open: This is the entering some portion of the cursor.
  • Fetch: Used to recover the information push by push from a cursor.
  • Close: This is a leave some portion of the cursor and used to close a cursor.
  • Deallocate: In this part, we erase the cursor definition and discharge all the resources related to the cursor.

a. Syntax of a SQL Cursor

DECLARE @Variable  nvarchar(50) /* Declare All Required Variables */
DECLARE Cursor_Name CURSOR       /* Declare Cursor Name*/
[LOCAL | GLOBAL]               /* Define Cursor Scope */
[FORWARD_ONLY | SCROLL]                /* Define Movement Direction of Cursor  */
[ KEYSET | DYNAMIC |STATIC | FAST_FORWARD] /* Define basic type of cursor   */
[  SCROLL_LOCKS | OPTIMISTIC |READ_ONLY ]    /* Define Locks */
OPEN Cursor_Name               /* Open Cursor */
FETCH NEXT FROM Cursor_Name    /* Fetch data From Cursor */
Implement SQL QUery
CLOSE Cursor_Name              /* Clsoe The Cursor */
DEALLOCATE Cursor_Name          /* Deallocate all resources and Memory */

Let’s revise SQL Null Functions

4. Terminologies in SQL Cursors

Following are the terms in SQL Cursor –

i. Cursor Scope

Microsoft SQL Server underpins the GLOBAL and LOCAL keywords on the DECLARE CURSOR explanation to characterize the extent of the cursor name.

  • GLOBAL: Determines that the cursor name is worldwide to the association.
  • LOCAL: Indicates that the cursor name is neighborhood to the Stored Procedure, Trigger or inquiry that holds the cursor.

ii. Data Fetch Option in SQL Cursors

Microsoft SQL Server underpins the accompanying two get choices for information:

  • FORWARD_ONLY: Specifies that the cursor must be looked from the first to the last line.
  • Parchment: It gives 6 choices to bring the information (FIRST, LAST, PRIOR, NEXT, RELATIVE and ABSOLUTE).

You must read about SQL Operators

5. Types of Cursor in SQL Server

Below are the types of SQL Cursor, let’s discuss them one by one –

Types of Cursor in SQL Server

Types of Cursor in SQL Server

  • STATIC CURSOR: A static cursor populates the outcome set amid cursor creation and the object result is reserved for the lifetime of the cursor. A static cursor can push ahead and in reverse.
  • FAST_FORWARD: This is the default sort of cursor. It is indistinguishable from the static with the exception of that you can just look forward.
  • DYNAMIC: In a dynamic cursor, increases and deletes are noticeable for others in the data source while the cursor is open.
  • KEYSET: This is like a dynamic cursor aside from we can’t see records others include. On the off chance that another client deletes a table, it is distant from our table set.

6. Kinds of Locks in Cursor in SQL

Locking in SQL is the procedure by which a DBMS confines access to a column in a multi-user condition. At the point when a line or segment is only locked, different clients are not allowed to get to the locked information until the point when the lock is discharged. It is used for information respectability. This guarantees two clients can’t all the while refresh a similar section consecutively.
Let’s revise the SQL Create Database
Microsoft SQL Server bolsters the accompanying three kinds of Locks.

  • READ ONLY: Specifies that the cursor can’t be refreshed.
  • SCROLL_LOCKS: Provides integrity into the cursor. It determines that the cursor will bolt the columns as they are perused into the cursor to guarantee that updates or erases influenced using the cursor to will succeed.
  • Optimistic: Specifies that the cursor does not lock pushes as they are used into the cursor. Along these lines, the updates or deletes influenced using the cursor to won’t succeed if the line has been refreshed outside the cursor.

Cursor in SQL Example

GO
CREATE TABLE [dbo].[Employee](
   [Emp_ID] [int] NOT NULL,
   [Emp_Name] [nvarchar](50) NOT NULL,
   [Emp_Salary] [int] NOT NULL,
   [Emp_City] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
   [Emp_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Now insert some values into the table as in the following:

Insert into Employee
Select 1,'Pankaj',25000,'Alwar' Union All
Select 2,'Rahul',26000,'Alwar' Union All
Select 3,'Sandeep',25000,'Alwar' Union All
Select 4,'Sanjeev',24000,'Alwar' Union All
Select 5,'Neeraj',28000,'Alwar' Union All
Select 6,'Naru',20000,'Alwar' Union All
Select 7,'Omi',23000,'Alwar'  

Select all values from the table as in the following examples_
Have a look at SQL Distinct Keyword

a. SQL Cursor Example 1

SET NOCOUNT ON
DECLARE @EMP_ID INT
DECLARE @EMP_NAME NVARCHAR(MAX)
DECLARE @EMP_SALARY INT
DECLARE @EMP_CITY NVARCHAR(MAX)
DECLARE EMP_CURSOR CURSOR
LOCAL  FORWARD_ONLY  FOR
SELECT * FROM Employee
OPEN EMP_CURSOR
FETCH NEXT FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT  'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+  ' EMP_NAME '+@EMP_NAME +' EMP_SALARY ' +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  + ' EMP_CITY ' +@EMP_CITY
FETCH NEXT FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
END
CLOSE EMP_CURSOR
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.
Do you know about SQL Indexes

b. SQL Cursor Example 2 (SCROLL)

SET NOCOUNT ON
DECLARE @EMP_ID INT
DECLARE @EMP_NAME NVARCHAR(MAX)
DECLARE @EMP_SALARY INT
DECLARE @EMP_CITY NVARCHAR(MAX)
DECLARE EMP_CURSOR CURSOR
LOCAL  SCROLL FOR
SELECT * FROM Employee
OPEN EMP_CURSOR
FETCH NEXT FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
FETCH RELATIVE 3 FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
PRINT  'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+  ' EMP_NAME '+@EMP_NAME +' EMP_SALARY ' +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  + ' EMP_CITY ' +@EMP_CITY
FETCH ABSOLUTE  3 FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
PRINT  'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+  ' EMP_NAME '+@EMP_NAME +' EMP_SALARY ' +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  + ' EMP_CITY ' +@EMP_CITY
FETCH FIRST FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
PRINT  'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+  ' EMP_NAME '+@EMP_NAME +' EMP_SALARY ' +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  + ' EMP_CITY ' +@EMP_CITY
FETCH LAST FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
PRINT  'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+  ' EMP_NAME '+@EMP_NAME +' EMP_SALARY ' +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  + ' EMP_CITY ' +@EMP_CITY
FETCH PRIOR FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
PRINT  'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+  ' EMP_NAME '+@EMP_NAME +' EMP_SALARY ' +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  + ' EMP_CITY ' +@EMP_CITY
FETCH NEXT FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
PRINT  'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+  ' EMP_NAME '+@EMP_NAME +' EMP_SALARY ' +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  + ' EMP_CITY ' +@EMP_CITY
CLOSE EMP_CURSOR
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 the SCROLL to fetch the data. This example contains all six modes of SCROLL (FIRST, LAST, PRIOR, NEXT, RELATIVE and ABSOLUTE).
Let’s take a tour to SQL Server Transaction
So, this was all in Cursor in SQL. Hope you like our explanation.

7. Conclusion – SQL Cursor

Hence, in this SQL Cursor tutorial, we discussed Cursor in SQL. Moreover, we learned parts, terms, and use of SQL Cursor. Also, we discussed types of Cursors in SQL. Along with this, we saw SQL Lock with SQL Cursors example. Tell us about the experience of reading the above article through comments.
See also –
SQL Interview Questions
For reference

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.