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
|
Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..
|
