The Cursor is database object use for getting data and manipulating particular row at a time. It is associated with select query it processes each row returned by select query. When we have to update record we use a cursor. With the help of cursor we can verify every row data and modify it or perform calculations which are not possible when we get all records at once.

It uses following keyword

1-Declare Cursor                

2-Open                             

3-Fetch

4-Close

5-Deallocate 

Declare Cursor-

Syntax for declaring cursor

DECLARE cursor_name CURSOR
 [LOCAL | GLOBAL]  it define cursor scope
 [FORWARD_ONLY | SCROLL]  --it define cursor movements (forward/backward)
 [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] --basic type of cursor
 [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks
 FOR select_statement --define SQL Select statement
 FOR UPDATE [col1,col2,...coln] --define columns that need to be updated

 

 Open-

Syntax for opening cursor

It can be of two type

1-Locally

2-Globally


By default it is open locally

OPEN [GLOBAL] cursor_name 
  Open[Local] cursor_Name --it is by defult set

 

  

Fetch-

For fetching record we use fetch statement, fetch provide many option for getting record from the cursor.

Syntax is 

FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]
FROM [GLOBAL] cursor_name

 

Close-

 

Close is use for close the cursor

 

Syntax for close statement

 

CLOSE cursor_name 

 

Deallocate

 

-Deallocate is use for deleting cursor.

 

Syntax for Deallocate is

 

DEALLOCATE cursor_name --After deallocation it can not be reopen

 

 

 Example


First we create table and name product

 

CREATE TABLE Product
(
 ProductID int PRIMARY KEY,
 Name varchar (50) NOT NULL,
 Price int NOT NULL,
 
)

 

Then insert some records.

 

 

INSERT INTO Product(ProductID,Name,Price) VALUES(1,'Ramesh',12000)
INSERT INTO Product(ProductID,Name,Price) VALUES(2,'Pavan',25000)
INSERT INTO Product(ProductID,Name,Price) VALUES(3,'Suresht',22000)
INSERT INTO Product(ProductID,Name,Price) VALUES(4,'Sonu',22000)
INSERT INTO Product(ProductID,Name,Price) VALUES(5,'Deepak',28000)

 

View record

 select * from Product

Cursor in Sql server database

Complete example of cursor             

DECLARE @Id int
DECLARE @EmpName varchar(50)
DECLARE @ProductID int
DECLARE @Price int
 
 DECLARE cur_emp CURSOR
STATIC FOR
SELECT ProductID,Name,Price from Product
OPEN cur_emp
IF @@CURSOR_ROWS > 0
 BEGIN
 FETCH NEXT FROM cur_emp INTO @ProductID,@EmpName,@Price
 WHILE @@Fetch_status = 0
 BEGIN
 PRINT 'ProductID : '+ convert(varchar(20),@ProductID)+', EmpName : '+@EmpName+ ', Price : '+convert(varchar(20),@Price)
 FETCH NEXT FROM cur_emp INTO @ProductID,@EmpName,@Price
 END
END


Output

Cursor in Sql server database

You can also check this related site

Implementing Concept of Cursor in SQL Server

  Modified On Mar-17-2018 04:16:30 AM

Leave Comment