Transact-SQL Cursors follows a general process that is used with all SQL Server cursors:
- Associate a cursor with the result set of a Transact-SQL statement, and define characteristics of the cursor, such as whether the rows in the cursor can be updated.
- Execute the Transact-SQL statement to populate the cursor.
- Retrieve the rows in the cursor you want to see. The operation to retrieve one row or one block of rows from a cursor is called a fetch. Performing a series of fetches to retrieve rows in either a forward or backward direction is called scrolling.
- Optionally, perform modification operations (update or delete) on the row at the current position in the cursor.
- Close the Cursor
The Cursor Process has the following steps involved in it:
- Declare a Cursor
- Open a Cursor
- Fetch data from the Cursor
- Close the Cursor
- De-allocate the Cursor
Declaring a Cursor:
Defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates.
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: Specifies that the scope of the cursor is local to the program in which the cursor was created.
GLOBAL: Specifies that the scope of the cursor is global to the connection. The cursor name can be referenced in any program by the connection. The cursor is only implicitly deallocated at disconnect.
If neither GLOBAL nor LOCAL is specified, the default is taken as GLOBAL.
FORWARD_ONLY: Specifies that the cursor can only be scrolled from the first to the last row. FETCH NEXT is the only supported fetch option. When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified. STATIC, KEYSET, and DYNAMIC cursors default to SCROLL.
SCROLL: Specifies that the cursor can scroll from first to the last row as well as last to first row also. It Supports 6 fetch methods like FETCH NEXT, FETCH PRIOR, FETCH FIRST, FETCH LAST, FETCH ABSOLUTE n and FETCH RELATIVE n.
STATIC: Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications
KEYSET: Specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. The set of keys that uniquely identify the rows is built into a table in tempdb known as the keyset. Changes to nonkey values in the base tables, either made by the cursor or committed by other users, are visible as we scroll around the cursor. Inserts made by other users are not visible. If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2. Updates of key values from outside the cursor resemble a delete of the old row followed by an insert of the new row.
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.
FAST_FORWARD: Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified.
READ_ONLY: Prevents updates made through this cursor. This option overrides the default capability of a cursor to be updated.
SCROLL_LOCKS: Specifies that positioned updates or deletes made through the cursor are guaranteed to succeed. Microsoft SQL Server locks the rows as they are read into the cursor to ensure their availability for later modifications. SCROLL_LOCKS cannot be specified if FAST_FORWARD is also specified.
OPTIMISTIC: Specifies that positioned updates or deletes made through the cursor do not succeed if the row has been updated in the table since it was read into the cursor. SQL Server does not lock rows as they are read into the cursor. It instead uses comparisons of timestamp column values, or a checksum value if the table has no timestamp column, to determine whether the row was modified after it was read into the cursor. If the row was modified, the attempted positioned update or delete fails. OPTIMISTIC cannot be specified if FAST_FORWARD is also specified.
TYPE_WARNING: Specifies that a warning message is sent to the client if the cursor is implicitly converted from the requested type to another.
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.
Opening a Cursor: Opens a Transact-SQL server cursor and populates the cursor by executing the Transact-SQL statement specified on the DECLARE CURSOR.
Syntax: OPEN <cursor_name>
Fetching data from the Cursor: Retrieves a specific row from a Transact-SQL server cursor into specified variables.
Syntax:
FETCH [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n ]
FROM <cursor_name> INTO @variable_name [ ,...n ]
NEXT: Returns the result row immediately following the current row and increments the current row to the row returned. If FETCH NEXT is the first fetch against a cursor, it returns the first row in the result set. NEXT is the default cursor fetch option.
PRIOR: Returns the result row immediately preceding the current row, and decrements the current row to the row returned. If FETCH PRIOR is the first fetch against a cursor, no row is returned and the cursor is left positioned before the first 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: If n is positive, it returns the specified nth row from the front of the cursor. If n is negative, it returns the specified nth row from the back of the cursor.
RELATIVE n: If n is positive, returns the row n rows beyond the current row. If n is negative, returns the row n rows prior to the current row.
If any of the used fetch statement is successful it returns the status of it which will be stored in a implicit variable @@FETCH_STATUS (this does not requires to be declared) which can be any of the following values:
0 - The FETCH statement was successful
-1 - The FETCH statement failed or the row was beyond the result set
-2 - The row fetched is missing
Closing a 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>
Deallocating a Cursor: Removes a cursor reference. When the last cursor reference is deallocated, SQL Server releases the data structures comprising the cursor.
Syntax: Deallocate <cursor_name>
Using a Simple Cursor:
DECLARE EMPCUR CURSOR FOR SELECT ENAME, SAL FROM EMP
DECLARE @ENAME VARCHAR(50), @SAL MONEY
OPEN EMPCUR
FETCH NEXT FROM EMPCUR INTO @ENAME, @SAL
WHILE @@FETCH_STATUS=0
BEGIN
PRINT 'SALARY OF ' + @ENAME + ' IS ' + CAST(@SAL AS VARCHAR)
FETCH NEXT FROM EMPCUR INTO @ENAME, @SAL
END
CLOSE EMPCUR
DEALLOCATE EMPCUR
Using a Cursor to Update all the rows of the Table:
This program will explain you how we can update all the rows of the table basing on some conditions, similar to the program we have written before discussing cursors but there only a single row has been modified.
DECLARE EMPCUR CURSOR FOR SELECT EMPNO, JOB FROM EMP
DECLARE @EMPNO INT, @JOB VARCHAR(50)
OPEN EMPCUR
FETCH NEXT FROM EMPCUR INTO @EMPNO, @JOB
WHILE @@FETCH_STATUS=0
BEGIN
IF @JOB='PRESIDENT'
UPDATE EMP SET SAL = SAL + SAL * 0.1 WHERE EMPNO=@EMPNO
ELSE IF @JOB='MANAGER'
UPDATE EMP SET SAL = SAL + SAL * 0.08 WHERE EMPNO=@EMPNO
ELSE IF @JOB='ANALYST'
UPDATE EMP SET SAL = SAL + SAL * 0.06 WHERE EMPNO=@EMPNO
ELSE
UPDATE EMP SET SAL = SAL + SAL * 0.05 WHERE EMPNO=@EMPNO
FETCH NEXT FROM EMPCUR INTO @EMPNO, @JOB
END
CLOSE EMPCUR
DEALLOCATE EMPCUR
Using a Global Cursor:
Program 1:
DECLARE EMPCUR CURSOR GLOBAL
FOR SELECT ENAME, SAL, COMM FROM EMP
DECLARE @ENAME VARCHAR(50), @SAL MONEY, @COMM MONEY, @TOTSAL MONEY
OPEN EMPCUR
FETCH NEXT FROM EMPCUR INTO @ENAME, @SAL, @COMM
WHILE @@FETCH_STATUS=0
BEGIN
SET @TOTSAL=@SAL + ISNULL(@COMM, 0)
PRINT @ENAME + ' EARNS ' + CAST(@TOTSAL AS VARCHAR) + ' EVERY MONTH'
FETCH NEXT FROM EMPCUR INTO @ENAME, @SAL, @COMM
END
CLOSE EMPCUR
-In the above case because it was a Global cursor we are not using any Deallocate Cursor statement, now we use the same cursor in other programs with of declaring it as following:
Program 2:
DECLARE @ENAME VARCHAR(50), @SAL MONEY, @COMM MONEY, @ANNSAL MONEY
OPEN EMPCUR
FETCH NEXT FROM EMPCUR INTO @ENAME, @SAL, @COMM
WHILE @@FETCH_STATUS=0
BEGIN
SET @ANNSAL=(@SAL + ISNULL(@COMM, 0)) * 12
PRINT @ENAME + ' EARNS ' + CAST(@ANNSAL AS VARCHAR) + ' EVERY YEAR'
FETCH NEXT FROM EMPCUR INTO @ENAME, @SAL, @COMM
END
CLOSE EMPCUR
-We don't require to Deallocate the Cursor any where it gets deallocated when we close the connection.
Using a Static Cursor:
DECLARE EMPCUR CURSOR STATIC
FOR SELECT SAL FROM EMP WHERE EMPNO=1005
DECLARE @SAL MONEY
OPEN EMPCUR
UPDATE EMP SET SAL=6000 WHERE EMPNO=1005
FETCH NEXT FROM EMPCUR INTO @SAL
PRINT @SAL
CLOSE EMPCUR
DEALLOCATE EMPCUR
-In this case after opening the cursor we have performed an update of Sal on the EMP table but still the cursor contains the old value but not the new value, so it prints the old Salary value only.
Using a Dynamic Cursor:
DECLARE EMPCUR CURSOR DYNAMIC
FOR SELECT SAL FROM EMP WHERE EMPNO=1005
DECLARE @SAL MONEY
OPEN EMPCUR
UPDATE EMP SET SAL=4000 WHERE EMPNO=1005
FETCH NEXT FROM EMPCUR INTO @SAL
PRINT @SAL
CLOSE EMPCUR
DEALLOCATE EMPCUR
-In this case after opening the cursor we have performed an update of Sal on the EMP table but the cursor contains the new value but not the new value, so it prints the new Salary value only.
Using Scroll Cursor:
DECLARE EMPCUR CURSOR
SCROLL
FOR SELECT EMPNO FROM EMP
DECLARE @EMPNO INT
OPEN EMPCUR
FETCH NEXT FROM EMPCUR INTO @EMPNO
PRINT @EMPNO
FETCH LAST FROM EMPCUR INTO @EMPNO
PRINT @EMPNO
FETCH PRIOR FROM EMPCUR INTO @EMPNO
PRINT @EMPNO
FETCH FIRST FROM EMPCUR INTO @EMPNO
PRINT @EMPNO
FETCH ABSOLUTE 12 FROM EMPCUR INTO @EMPNO
PRINT @EMPNO
FETCH ABSOLUTE -10 FROM EMPCUR INTO @EMPNO
PRINT @EMPNO
FETCH RELATIVE 3 FROM EMPCUR INTO @EMPNO
PRINT @EMPNO
FETCH RELATIVE -5 FROM EMPCUR INTO @EMPNO
PRINT @EMPNO
CLOSE EMPCUR
DEALLOCATE EMPCUR
-As we have declared the cursor as scroll all the Fetch methods can be used on it.
No comments:
Post a Comment