Introduction:
What is a Cursor?
A Cursor is a SQL Object, or we can say like a virtual table that retrieves data from the table one row at a time. We use cursors when we need to update records in a database table in singleton fashion in other words row by row.
Life Cycle of Cursor:
Declare Cursor - > Open -> Fetch - > Close -> Deallocate
Before using a cursor, you first must declare the cursor. Once a cursor has been declared, you can open it and fetch from it. You can fetch row by row and make multiple operations on the currently active row in the cursor. When you have finished working with a cursor, you should close the cursor and Deallocate it to release SQL Server resources.
Type of cursors:
- Forward Only Cursor,
- Scroll Cursor,
- Static Cursor,
- Dynamic Cursor,
- Keyset Driven Cursor
First of all we create a table for applying our Cursor.
create table emp(emp_id int,em_name varchar(10))
After the creation we insert data as follows:
insert into emp(emp_id,em_name) VALUES(1,'d')
insert into emp(emp_id,em_name) VALUES(2,'deepak')
insert into emp(emp_id,em_name) VALUES(3,'gaurav')
insert into emp(emp_id,em_name) VALUES(4,'mahi')
insert into emp(emp_id,em_name) VALUES(5,'gill')
insert into emp(emp_id,em_name) VALUES(6,'singh')
Output:
Forward only and Scroll Cursors are important So I explain them in detail.
Forward Only cursor:
This type of Cursor fetches the next record only. In this type of cursor we can fetch only next record we cant fetch first, last and a specific record.
Declaration of Forward Only cursor:
declare curscr cursor
for
select * from emp
Opening the forward-only Cursor
open curscr
Fetching data from a Forward Only cursor
fetch next from curscr
If we execute this query two times than we get the following output:
Closing the Forward Only Cursor:
close curscr
Deallocate/Deleting the Forward Only Cursor:
deallocate curscr
Scroll Cursor:
We can fetch any record as first, last, prior and specific record from the table.
Declaration of Scroll cursor:
declare scroll_cursor cursor
scroll for
select * from emp
Opening the Scroll Cursor:
open scroll_cursor
Fetching data from Scroll cursor:
- fetch first from scroll_cursor (To fetching the first data)
- fetch last from scroll_cursor (To fetching the last data)
- fetch prior from scroll_cursor (To fetching the previous data)
- fetch absolute 4 from scroll_cursor (To fetching the absolute data)
- fetch relative 2 from scroll_cursor (To fetching the relative data)
Closing the Scroll Cursor:
close scroll_cursor
Deallocate/Deleting the Scroll Cursor :
deallocate scroll_cursor
Syntax to Declare Cursor:
Declare Cursor SQL Command is used to define the cursor with many options that impact the scalability and loading behavior 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
Example 1:
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
Example 2:
CREATE PROCEDURE Usp_cursor_test
AS
BEGIN
–Declaring the variables needed for cursor to store data
DECLARE @Name VARCHAR(50)
DECLARE @EmptypeID INT
–Declaring the Cursor cur_print For name and Emptypeid in the Employeedetails table
DECLARE cur_print CURSOR FOR
SELECT name,
emptypeid
FROM employee.employeedetails
–After declaring we have to open the cursor
OPEN cur_print
–retreives the First row from cursor and storing it into the variables.
FETCH NEXT FROM cur_print INTO @Name, @EmptypeID
– @@FETCH_STATUS returns the status of the last cursor FETCH statement issued against
– any cursor currently opened by the connection.
– @@FETCH_STATUS = 0 means The FETCH statement was successful.
– @FETCH_STATUS = -1 The FETCH statement failed or the row was beyond the result set.
– @@FETCH_STATUS = -2 The row fetched is missing.
WHILE @@FETCH_STATUS = 0
BEGIN
–Operations need to be done,Here just printing the variables
PRINT @Name
PRINT @EmptypeID
–retreives the NExt row from cursor and storing it into the variables.
FETCH NEXT FROM cur_print INTO @Name, @EmptypeID
END
–Closing the cursor
CLOSE cur_print
– removes the cursor reference and relase cursor from memory
– very Important
DEALLOCATE cur_print
END
Note:
- Once cursor is opened we have to close the cursor.
- After the usage cursor should be deallocated from the memory.
- I will not recommend the usage of cursors in all scenarios because it affects performance, since for each result it will have a network round trip which will cause a major performance issue in large data sets. You can make use of case statement instead of cursors for some scenarios.
