Cursor is the database object in MS SQL Server to retrieve the data from the result set one row at a time either in backward direction or forward direction. Generally in T-SQL command it will direct operates on all the rows of result at one time, so when ever we want to process based on the condition 1 by 1 for our process or data is dependent on the previous actions cursor is the suitable feature to use.
Now lets see the Life cycle of the cursor as below
Declare the cursor
Like any other object we need to declare the cursor first, we can declare the cursor by defininf SQL statement which returs a result set.
Open the cursor
Once cursor is declared we can open and populate by executing the SQL statement defined in the cursors.
Fetch records one by one from the Cursor
One cursor is opened we can fetch rows one by one or in a block for the data manipulation.
One we have fetched all the rows and manipulated data as required we need to close the cursor explicitly.
One we have close the cursor at the end, we need to delete the cursor definition and free all the system resources associated with the cursor.
Declare Cursor SQL Command is used to declare the cursor with provided options. There are some options we need to use while declaring the curosr which impact on the performance of cursor.
The complete syntax of declaring a cursor is as below
DECLARE name_of_cursor CURSOR [LOCAL | GLOBAL] -- Local or global define the scope of the cursor, default it is local only [FORWARD_ONLY | SCROLL] -- It defines the direction of the cursor either forward or backward [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] -- It define the basic type of cursor [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] -- It define the locks FOR Select_Query -- Here you can define select or update statement as required FOR UPDATE [field1,field2,field3 ....., fieldn] -- Here provide the list of fields/columns to updated
Syntax to Open a Cursor
A SQL cursor can be either opened locally or globally. By default it is opened locally.
OPEN [GLOBAL] name_of_cursor
Syntax to Fetch rows from Cursor
Fetch statement provides different different options to retrieve the rows from the cursor. Default NEXT option is applied.
FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n] FROM [GLOBAL] name_of_cursor INTO @Name_Of_Variable[1,2,3,4, .....n]
Syntax to Close the Cursor
Close statement closed the cursor explicitly.
CLOSE name_of_cursor -- Once it is closed then it can be reopen
Syntax to Deallocate Cursor at the end
Deallocate statement delete the cursor definition and free all the system resources associated with the cursor.
DEALLOCATE name_of_cursor -- Once cursor is deallocated it can not be open again.
See the sample data as below from which we will retrieve the data using cursor.
The full cursor example
SET NOCOUNT ON DECLARE @ID int DECLARE @Title varchar(100) DECLARE @ISBN varchar(100) Declare @Price Decimal DECLARE all_book CURSOR STATIC FOR SELECT Id,Title,Isbn,Price from Books OPEN all_book IF @@CURSOR_ROWS > 0 BEGIN FETCH NEXT FROM all_book INTO @ID,@Title,@ISBN,@Price WHILE @@Fetch_status = 0 BEGIN Print 'Id :'+Convert(VarChar(20),@ID) + ', Price :'+Convert(VarChar(20),@Price)+ ', Title :'+@Title + ', ISBN :'+@ISBN FETCH NEXT FROM all_book INTO @ID,@Title,@ISBN,@Price END END CLOSE all_book DEALLOCATE all_book SET NOCOUNT OFF
When you run the cursor you will see the below out put.
in this article we have see the basic concept and syntax of the SQL Server Cursor. I hope it will help you to understand the cursors. i would like to have feedback from my blog readers