A cursor is a set of rows together with a pointer that identifies a current row.
| Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. For example, you can use cursor to include a list of all user databases and make multiple operations against each database by passing each database name as a variable. it's like record-set in ASP and Visual Basic Declaring a Cursor Before using cursor, you first must declare the cursor, i.e. define its scrolling behavior and the query used to build the result set on which the cursor operates. To declare cursor, you can use a syntax based on the SQL-92 standard and a syntax using a set of Transact-SQL extensions.
cursor_name - the name of the server side cursor, must contain from 1 to 128 characters. INSENSITIVE - specifies that cursor will use a temporary copy of the data instead of base tables. This cursor does not allow modifications and modifications made to base tables are not reflected in the data returned by fetches made to this cursor. SCROLL - specifies that cursor can fetch data in all directions, not only sequentially until the end of the result set. If this argument is not specified, FETCH NEXT is the only fetch option supported. select_statement - the standard select statement, cannot contain COMPUTE, COMPUTE BY, FOR BROWSE, and INTO keywords. READ ONLY - specifies that cursor cannot be updated. UPDATE [OF column_name [,...n]] - specifies that all cursor's columns can be updated (if OF column_name [,...n] is not specified), or only the columns listed in the OF column_name [,...n] list allow modifications. |
Opening a Cursor
Once a cursor has been declared, you must open it to fetch data from it. To open a cursor, you can use the following syntax:OPEN { { [GLOBAL] cursor_name } | cursor_variable_name}
|
GLOBAL - if this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be opened; otherwise, the global cursor will be opened.
cursor_name - the name of the server side cursor, must contain from 1 to 128 characters.
cursor_variable_name - the name of a cursor variable that references a cursor.
After a cursor is opening, you can determine the number of rows that were found by the cursor. To get this number, you can use @@CURSOR_ROWS scalar function.
Fetching a Cursor
Once a cursor has been opened, you can fetch from it row by row and make multiple operations on the currently active row in the cursor. To fetch from a cursor, you can use the following syntax:
FETCH
[ [ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE {n | @nvar}
| RELATIVE {n | @nvar}
]
FROM
]
{ { [GLOBAL] cursor_name } | @cursor_variable_name}
[INTO @variable_name[,...n] ]
|
NEXT - the default cursor fetch option. FETCH NEXT returns the next row after the current row.
PRIOR - returns the prior row before the current row.
FIRST - returns the first row in the cursor.
LAST - returns the last row in the cursor.
ABSOLUTE {n | @nvar} - returns the nth row in the cursor. If a positive number was specified, the rows are counted from the top of the data set; if 0 was specified, no rows are returned; if a negative number was specified, the number of rows will be counted from the bottom of the data set.
RELATIVE {n | @nvar} - returns the nth row in the cursor relative to the current row. If a positive number was specified, returns the nth row beyond the current row; if a negative number was specified, returns the nth row prior the current row; if 0 was specified, returns the current row.
GLOBAL - if this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be fetched; otherwise, the global cursor will be fetched.
cursor_name - the name of the server side cursor, must contain from 1 to 128 characters.
cursor_variable_name - the name of a cursor variable that references a cursor.
INTO @variable_name[,...n] - allows data returned from the cursor to be held in temporary variables. The type of variables must match the type of columns in the cursor select list or support implicit conversion. The number of variables must match the number of columns in the cursor select list.
Closing a Cursor
When you have finished working with a cursor, you can close it to release any resources and locks that SQL Server may have used while the cursor was open.
To close a cursor, you can use the following syntax:
CLOSE { { [GLOBAL] cursor_name } | cursor_variable_name }
|
GLOBAL - if this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be closed; otherwise, the global cursor will be closed.
cursor_name - the name of the server side cursor, must contain from 1 to 128 characters.
cursor_variable_name - the name of a cursor variable that references a cursor.
Note. If you have closed a cursor, but have not deallocated it, you can open it again when needed.
Deallocating a Cursor
When you have finished working with a cursor and want to completely release SQL Server resources that were used by a cursor, you can deallocate a cursor.
To deallocate a cursor, you can use the following syntax:
DEALLOCATE { { [GLOBAL] cursor_name } | @cursor_variable_name}
|
GLOBAL - if this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be deallocated; otherwise, the global cursor will be deallocated.
cursor_name - the name of the server side cursor, must contain from 1 to 128 characters.
cursor_variable_name - the name of a cursor variable that references a cursor.
Note. Deallocating a cursor completely removes all cursor references. So, after a cursor is deallocated, it no longer can be opened.
Example
DECLARE @ColExpir datetime
DECLARE @ColFallprotec datetime
DECLARE @ColWorkid int
--------------------------------------------------------
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT Table_Training_Detalis.DateExpires,Table_Training_Detalis.Worker_ID
FROM Table_Courses
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @ColExpir,@ColWorkid
WHILE @@FETCH_STATUS = 0
BEGIN
update Table_Workers set WHIMIS= @ColExpir where Worker_ID=@ColWorkid
FETCH NEXT FROM @MyCursor
INTO @ColExpir,@ColWorkid
END
CLOSE @MyCursor
DEALLOCATE @MyCursor