T/SQL - Part Two

In this article, we Learn about Cursor, types of Cursor in details with example.

In this article we will learn about cursor and types of cursors in detail, with examples.

• T/SQL Part - One
  

Introduction 

Cursor:cursor is a memory location for storing database tables. Cursor is a temporary work area allotted to the client at the server when a select stint is executed. A cursor contains information on a select stint and the rows of data accessed by it. This temporary work area is used to store the data retrieved from the database and manipulate this data.

A cursor can hold more than one row but can process only one row at a time The set of rows the cursor holds is called the result set.

Types of cursor

• Implicit cursor
  These cursors will be created by SQL Server by default when select stint executes.
  
  
• Explicit Cursor
  When user creates a memory location to store the tables then it is called  Explicit cursor

Note
Whenever we want to do record by record manipulation the explicit cursor will be used.

Steps to create Explicit Cursor

1. Declare a cursor
   In this process we define a cursor
   Syntax - Declare <cursor name> cursor for <Select stint>
   
   
2. Open a cursor
   When we open a cursor it will internally execute the select stint that is associated with the cursor declaration and load the data into the cursor.
   Syntax - Open <Cursor name>
   
   
3. Fetching Data from the cursor
   In this process we access row by row from cursor
   Syntax - Fetch first/last/next/prior /absolute n/relative n from <cursor name> [into <variables>]
   
   
4. Closing a cursor
   In this process it releases the cursor result set of the cursor leaving the data structure available for reporting.
   Syntax - close <cursor name>
   
   
5. Deallocate a cursor
   It removes the cursor reference and deallocates it by destroying the data structure.
   Syntax - Deallocate <cursor name>
   
   @@Fetch_status
   It is a global variable use to check whether cursor variable contains records or not. If the record is there the value will be zero, and otherwise the value will be -1

Fetching Methods in Cursor

1. Next -It is a default method of cursor which can be used for fetching the records in forward directional only
2. First - Fetching first record from the table
3. Last - Fetching Last record from the table.
4. Prior - Fetching the current record to previous record (backward directional)
5. Absolute n - Fetching exact position of the record from the Table.n is the number of records.
6. Relative n - Fetching the records either in incremental or decremental order.

Note
The cursors are again classified into 2 types,

1. Forward-only cursor
2. Scroll cursor

If a cursor is declared as forward only, it allows you to navigate only to the next records in sequential order, and moreover it supports only a singleton fashion that is fetched next (in a one-by-one way)

Whereas a scroll cursor allows you to Navigate /Fetch bi-directionally; that is, top-bottom or bottom-top, and it supports six different fetch methods:

1. Fetch Next
2. Fetch First
3. Fetch Last
4. Fetch Prior
5. Fetch absolute n
6. Fetch relative n

Example

Write a cursor to update Employee Salary as per given criteria. 

For each department name, the salary increases in the given percentage:

 

Department Name	Increment
Technology	5
Sales	7
Hr	3

 

Create table Empdetails and insert some values

create table Empdetails(id int,dname varchar(50),salary int)

insert into Empdetails values(1,'Technology',2000)
insert into Empdetails values(2,'Sales',25000)
insert into Empdetails values(3,'Hr',12000)

 

Now we create a cursor.

declare @id int,@dname varchar(50)
declare cur cursor for select id,dname from Empdetails
open cur
fetch next from cur into @id,@dname
while @@FETCH_STATUS=0
begin
if @dname='Development'
update Empdetails set salary=salary*5 where id=@id
else if @dname='sales'
update Empdetails set salary=salary*7 where id=@id
else
update Empdetails set salary =salary*3 where id=@id
fetch next from cur into @id,@dname
end
close cur
deallocate cur

Check the table,

 

Example

Write a cursor to fetch records from last to first:

declare c1 cursor scroll for select * from company
open c1
fetch last from c1
while @@FETCH_STATUS=0
begin
fetch prior from c1
end
close c1
deallocate c1

Result

Summary

In this article we learned about cursor in T/SQL. Cursor is a memory location for storing database tables.