Cursor is a database objects used to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time. Cursor is used when user needs to update records in a database table in row by row fashion.
Life Cycle of Cursor
1. Declare Cursor
A cursor is declared by defining the SQL statement that returns a result set.
Syntax:
Declare Cursor SQL Comaand is used to define the cursor with many options that impact the scalablity and loading behaviour of the cursor.
The basic syntax is given below:
DECLARE cursor_name CURSOR
[LOCAL | GLOBAL] -- define cursor scope
[FORWARD_ONLY | SCROLL] -- define cursor movements (forward/backward)
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD] -- basic type of cursor
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] -- define locks FOR select_statement --define SQL Select statement
FOR UPDATE [col1,col2,...coln] -- define columns that need to be updated
2. Open
A Cursor is opened by executing the SQL statement defined by the cursor.
Syntax:
A Cursor can be opened locally or globally. By default it is opened locally.
The basic syntax to open cursor is given below:
OPEN [GLOBAL] cursor_name -- by default it is local
3. Fetch
When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
Syntax:
Fetch statement provides the many options to retrieve the rows from the cursor. NEXT is the default option.
The basic syntax to fetch cursor is given below:
FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]FROM [GLOBAL] cursor_name INTO @Variable_name[1,2,..n]
4. Close
After data manipulation, we should close the cursor explicitly.
Syntax:
Close statement closed the cursor explicitly.
The basic syntax to close cursor is given below:
CLOSE cursor_name -- after closing it can be reopen
5. Deallocate
Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.
Syntax:
DEALLOCATE cursor_name -- after deallocation it can't be reopen
Simple Example of Cursor
Consider the following table titled 'Employee'.
| EmpID | Name | Salary | Address |
|---|
| 1 | Jay | 12000 | Mumbai |
| 2 | Ryan | 25000 | Delhi |
| 3 | Anna | 22000 | Baroda |
| 4 | Albert | 22000 | Mumbai |
| 5 | Satya | 28000 | Pune |
Write a T-SQL Query to display the record of employees using cursor.
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE @salary int
DECLARE cur_emp CURSOR
STATIC FOR SELECT EmpID, EmpName, Salary from Employee
OPEN cur_emp
IF @@CURSOR_ROWS > 0
BEGIN
FETCH NEXT FROM cur_emp INTO @Id, @name, @salary
WHILE @ @Fetch_status = 0
BEGIN
PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary)+'
FETCH NEXT FROM cur_emp INTO @Id, @name, @salary
END
END
CLOSE cur_emp
DEALLOCATE cur_emp
SET NOCOUNT OFF
ID: 1, Name: Jay, Salary: 12000
ID: 1, Name: Ryan, Salary: 25000
ID: 1, Name: Anna, Salary: 22000
ID: 1, Name: Albert, Salary: 22000
ID: 1, Name: Satya, Salary: 28000
