----------- Cursor -------------------
Microsoft SQL Server statements produce a complete result set, but there are times when the results are best processed one row at a time. Opening a cursor on a result set allows processing the result set one row at a time. You can assign a cursor to a variable or parameter with a cursor data type.
Some time we need to do operation on a particular row of result set or small block of rows at a time. Cursors are an extension to result sets that provide that mechanism.
To use cursors in SQL procedures, you need to do the following:
1:Declare a cursor that defines a result set.
2:Open the cursor to establish the result set.
3:Fetch the data into local variables as needed from the cursor, one row at a time.
4:Close the cursor when done
5:Deallocate the cursor
1:DECLARE CURSOR(T-SQL)
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 ] ] ]
[;]
LOCAL:
Scope of Local cursor is only to batch , stored procedure , or trigger in which it is declared. And LOCAL cursor automatically deallocated when container block ,sp or trigger terminated.
GLOBAL:
Specifies that the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection. The cursor is onl implicitly deallocated at disconnect.
FORWARD_ONLY
- Specifies that cursor can only fetch data sequentially from the first to the last row. FETCH NEXT is the only fetch option supported. It is the fastest cursor that you can update.
The effects of all INSERT, UPDATE, and DELETE statements made by the current user or committed by other users that affect rows in the result set are visible as the rows are fetched from the cursor. Because the cursor cannot be scrolled backward, changes made to rows in the database after the row was fetched are not visible by using the cursor. Note: If cursor is not declared forward-only OR scroll , then by default it is forward-only.
STATIC: Defines a cursor that makes a temporary copy of the data in tempdb to be used by the cursor. Therefore modification made to base table does not reflect to temporary table , and this cursor does not allow modification.
DYNAMIC: Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch. The FETCH ABSOLUTE option is not supported with dynamic cursors.
FAST_FORWARD: Specify forward-only , read-only cursor. FAST_FORWARD can not be applied if SCROLL or FOR_UPDATE is specified.
READ_ONLY: Prevent updates made through this cursor.
SCROLL_LOCK: specify that updation or deletion made through this cursor guarantee to succeed. Because SQL server lock the corresponding row.
OPTIMISTIC: Specifies that positioned updates or deletes made through the cursor do not succeed if the row has been updated since it was read into the cursor. SQL Server does not lock rows as they are read into the cursor.
TYPE_WARNING - Specifies that if the cursor will be implicitly converted from the requested type to another, a warning message will be sent to the client.
FOR UPDATE [OF column_name [,...n]]
Defines updatable columns within the cursor. If OF column_name [,...n] is supplied, only the columns listed allow modifications. If UPDATE is specified without a column list, all columns can be updated, unless the READ_ONLY concurrency option was specified.
2:OPEN CURSOR
Syntax for opening cursor is
Open <cursor_name>
3:FETCH ROW FROM CURSOR
FETCH [NEXT | PRIOR | FIRST | LAST |ABSOLUTE{n}|RELATIVE{n}] [FROM] <cursor_name> [INTO @variable_name[1,2,…n variable]]
Let us define the terms used in syntax---
NEXT: Returns the immediate next row .And increment current row pointer to that row.
PRIOR: Returns the immediate previous row .And increment current row pointer to that row.
FIRST: Returns the first row in the cursor and makes it the current row.
LAST: Returns the last row in the cursor and makes it the current row.
ABSOLUTE{ n} :Return nth row of result set. If n is positive, counting start from top of result set .If n is negative , counting start from bottom of result set. In both situation returned row is made as new current row. If n is 0 no row is returned.
REALTIVE { n }: Same functionality as absolute , but only difference is that counting start from current row ,forward of backward ,according to sign of n.
NOTE:
J If either FORWARD_ONLY or FAST_FORWARD is specified, NEXT is the only FETCH option supported.
JIf DYNAMIC, FORWARD_ONLY or FAST_FORWARD are not specified, and one of KEYSET, STATIC, or SCROLL are specified, all FETCH options are supported.
JDYNAMIC SCROLL cursors support all the FETCH options except ABSOLUTE.
4: CLOSE CURSOR
Closes an open cursor by releasing the current result set and freeing any cursor locks held on the rows on which the cursor is positioned. CLOSE leaves the data structures available for reopening, but fetches and positioned updates are not allowed until the cursor is reopened. CLOSE must be issued on an open cursor; CLOSE is not allowed on cursors that have only been declared or are already closed.
SYNTAX:
CLOSE <cursor_name>
5: DEALLOCATE CURSOR
Removes a cursor reference. When the last cursor reference is deallocated, the data structures comprising the cursor are released by Microsoft SQL Server.
SIMPLE EXAMPLE:
DECLARE @a nvarchar(50)
DECLARE @myCursor CURSOR
SET @myCursor=CURSOR FOR SELECT TOP 10 propertyid FROM properties
OPEN @myCursor
FETCH NEXT
FROM @myCursor into @a
WHILE @@FETCH_STATUS =0
BEGIN
PRINT @a
FETCH NEXT
FROM @myCursor into @a
END
CLOSE @myCursor
DEALLOCATE @myCursor
WHAT @@FETCH_STATUS DO HERE?
@@FETCH_STATUS is called cursor function, which is in build function.
It return the status of last cursor FETCH statement.
If it is 0; the FETCH statement was successful
If it is -1; failed or row was beyond the result set
If it is -2;the row fetched is missing
