select statement returns set of rows after satisfy the conditions in the WHERE clause, These set of rows return by select statement is known as the result set.
Interactive online applications, cannot always work effectively with the entire result set as a unit.
These applications need a mechanism, who provide a pointer which points one row in a small block of rows at a time.
Cursors are an extension to result sets that provide that mechanism.
Type of Cursors
SQL Server supports four cursor types.
1) Forward-only OR Firehose Cursors:
A forward-only cursor is specified as FORWARD_ONLY and READ_ONLY and does not support scrolling. These are also called firehose cursors. it is used only for fetching the rows serially from the start to the end of the cursor. when firehose cursor fetches rows, at the same time if users comit changes in database like INSERT, UPDATE, and DELETE , will also reflected by firehose cursor.
2) Static Cursors:
when the cursor is opened, Static cursor uses built in tempdb for result set. A static cursor does not display new rows inserted in the database after the cursor was opened, even if they match the search conditions of the cursor SELECT statement. Static cursors consume few resources while scrolling. if rows are deleted from database after cursor opening, it will show all deleted rows. if users comit changes in database like INSERT, UPDATE, and DELETE , will not reflected by static cursor (unless the cursor is closed and reopened) even with same conditions, which is used to open the cursor.
3) Keyset Cursors:
Keyset-driven cursors are controlled by a set of unique identifiers, keys, known as the keyset. The keyset is the set of the key values from all the rows that qualified for the SELECT statement at the time the cursor was opened. Keyset for a keyset-driven cursor is built in tempdb. when the cursor is opened, order of rows in a keyset-driven cursor are fixed.
4) Dynamic Cursors:
Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor. The data values, order, and membership of the rows in the result set can change on each fetch. All UPDATE, INSERT, and DELETE statements made by all users are visible through the cursor. Dynamic cursors are the opposite of static cursors. Updates are visible immediately if they are made through the cursor using T-SQL [WHERE CURRENT] OF clause. Updates made outside the cursor are not visible until they are committed
Life Cycle of Cursor
1) Declare Cursor: DECLARE cursor_name CURSOR
2) Open : OPEN cursor_name
3) Fetch : FETCH NEXT FROM cursor_name INTO local_variables
4) LOOP : WHILE @@Fetch_status = 0
5) TERMINATING LOOP : FETCH NEXT FROM cursor_name INTO local_variables
6) Close : CLOSE cursor_name
7) Deallocate: DEALLOCATE cursor_name
Cursor Syntax
a) ISO Syntax
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
[;]
b) Transact-SQL Extended Syntax
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]
Lets create a student table and insert few rows.
CREATE TABLE dbo.tbStudent
(
StudentID INT IDENTITY PRIMARY KEY,
StudentName VARCHAR(200),
ClassID INT,
SectionID INT,
SessionID int
)
Go
INSERT INTO dbo.tbStudent
SELECT 'Jack',1,1,1
UNION
SELECT 'Maria',2,1,1
UNION
SELECT 'Wiliam',1,1,1
UNION
SELECT 'Rock',2,1,1
UNION
SELECT 'Marina',1,1,1
UNION
SELECT 'Harish',2,1,1
UNION
SELECT 'Jhon',1,1,1
UNION
SELECT 'Raidu',2,1,1
Go
SELECT * FROM dbo.tbStudent
Go
StudentID StudentName ClassID SectionID SessionID ----------- --------------- ----------- ----------- ----------- 13 Harish 2 1 1 14 Jack 1 1 1 15 Jhon 1 1 1 16 Maria 2 1 1 17 Marina 1 1 1 18 Raidu 2 1 1 19 Rock 2 1 1 20 Wiliam 1 1 1 (8 rows affected)
Now we will create a temporary table to insert all rows after applying cursor on table tbStudent.
CREATE TABLE #tempStudent
(
StudentID INT,
StudentName VARCHAR(200),
)
Go
Following T-SQL will create a cursor cur_student for table tbStudent and copy StudentID, StudentName from table dbo.tbStudent into temp table #tempStudent
SET NOCOUNT ON;
-- Local variable declaration
DECLARE @StudentID int
DECLARE @StudentName varchar(200)
-- Declaring cursor for student
DECLARE cur_student CURSOR
FOR
SELECT StudentID,StudentName from dbo.tbStudent
-- Opening cursor
OPEN cur_student
-- Fetching first row
FETCH NEXT FROM cur_student INTO @StudentID,@StudentName
WHILE @@Fetch_status = 0
BEGIN
-- Printing contents while cursor fetching rows
PRINT 'StudentID : '+ convert(varchar(20),@StudentID)+', StudentName : '+@StudentName
-- Insert rows into temp table
INSERT INTO #tempStudent VALUES(@StudentID,@StudentName)
--For reach loop end point
FETCH NEXT FROM cur_student INTO @StudentID,@StudentName
END
-- Close Cursor
CLOSE cur_student
-- Deallocate Cursor
DEALLOCATE cur_student
SET NOCOUNT OFF
Go
The close statement closed the cursor explicitly.
Deallocate statement delete the cursor definition and free all the system resources associated with the cursor.
If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.
Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.