Hello All, We are going to start new batch from next week. message/call or mail us for more details.

8 September 2013

Cursor in SQL

In this blog I am trying to explain the concept of Cursor in SQL Server.

Definition:

Cursor is an object of database which is used to retrieve data from result set row by row. In other words a cursor is a set of rows together with a pointer that identifies a current row. We use cursor when we need to update records in a table row by row.

Cursor Life Cycle:

1-Declare Cursor
A cursor is declared by DECLARE CURSOR SQL command. We can declare a cursor by following syntax:

DECLARE cur_name CURSOR
[LOCAL | GLOBAL] --define the scope of cursor
[FORWARD_ONLY | SCROLL] --define cursor movements
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD] -- type of cursor
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks
FOR select_statement -- SQL Select statement
FOR UPDATE [column1,column2,...columnn] -- columns that to be updated
 
2-Open Cursor
A cursor can be open in two way either locally or globally. Syntax for open cursor:

OPEN [GLOBAL] cur_name --by default it is local
 
3-Fetch Cursor
After opening a you can fetch cursor. Syntax for fetch cursor:

FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]
FROM [GLOBAL] cur_name
INTO @var1,@var2…..@varn
 
4-Close Cursor
Close statement close cursor explicitly. Syntax for close cursor:

CLOSE  cur_name
 
5-Deallocate Cursor
To free the resources allocated by cursor we deallocate the cursor.Syntax for deallocate cursor

DEALLCATE  cur_name
 

Example of Cursor

DECLARE @FirstName varchar(50)
DECLARE @LastName varchar(50)
DECLARE cur_name CURSOR
STATIC FOR SELECT FirstName,LastName FROM user1
OPEN cur_name
if @@CURSOR_ROWS>0
BEGIN
FETCH NEXT FROM cur_name INTO @FirstName,@LastName
WHILE @@FETCH_STATUS=0
BEGIN
PRINT 'First Name:'+@FirstName+',Last Name:'+@LastName
FETCH NEXT FROM cur_name INTO @FirstName,@LastName
END
END
CLOSE cur_name
DEALLOCATE cur_name

1 comment: