What is a SQL Server Cursor?
A SQL Server CURSOR is a set of T-SQL
logic to loop over a pre-determined number of rows one at a time and the purpose
for the cursor may be to update one row at a time.
OR
The major function of a cursor is to
retrieve data, one row at a time, from a result set, unlike the SQL commands
which operate on all the rows in the result set at one time. Cursors are used
when the user needs to update records in a singleton fashion or in a row by row
manner, in a database table.
SQL Cursor Life Cycle
The following steps are involved in a
SQL cursor life cycle.
1. Declare
Cursor - A cursor is declared by defining
the SQL statement.
2. Open Cursor- A
cursor is opened for storing data retrieved from the result set.
3. Fetch Cursor- When a cursor is opened, rows can be fetched
from the cursor one by one or in a block to do data manipulation.
4. Close Cursor - The cursor should be closed explicitly
after data manipulation.
5. Deallocate
Cursor – The cursors should be deallocated
to delete cursor definition and release all the system resources associated
with the cursor.
Syntax -
DECLARE
cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR
select_statement
[ FOR { READ ONLY | UPDATE [ OF
column_name [ ,...n ] ] } ]
[;]
--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 ] ]
]
[;]
As an Example,
------CREATE
PROCEDURE------------------
CREATE
PROCEDURE sp_Cursor_Employee
AS
BEGIN
DECLARE
@EmpName VARCHAR(50)
DECLARE
@EmpDepartment VARCHAR(50)
DECLARE
Cursor_Employee CURSOR FOR
SELECT EmpName, EmpDepartment FROM
Employee
-----OPEN
CURSOR--------
OPEN
Cursor_Employee
-----FETCH
CURSOR-------
FETCH
Cursor_Employee INTO @EmpName,@EmpDepartment
WHILE(@@fetch_status=0)
BEGIN
PRINT '*
EmpName= '+@EmpName
PRINT ' EmpDepartment= '+@EmpDepartment
--FETCH CURSOR FOR
NEXT ROWS
FETCH
Cursor_Employee INTO @EmpName, @EmpDepartment
END
-------CLOSE
CURSOR--------
CLOSE
Cursor_Employee
--
DEALLOCATE CURSOR
DEALLOCATE
Cursor_Employee
END