Reader Level:
ARTICLE

Cursor in SQL Server

Posted by Deepak Arora Articles | SQL October 20, 2012
This article describes cursors, declaration of cursors, opening of cursors, fetching from cursors, closing of cursors and deallocating cursors.
  • 0
  • 0
  • 5039

Introduction

Today we are going to learn about cursors. In this article I describe cursors, declaration of cursors, opening of cursors, fetching from cursors, closing of cursors and deallocating cursors.

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:

  1. Declare
  2. Open
  3. Fetch
  4. Close
  5. 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 cursor:

  1. Forward Only Cursor
  2. Scroll Cursor
  3. Static Cursor
  4. Dynamic Cursor
  5. Keyset Driven Cursor

Forward only and Scroll Cursors are important So I explain them in detail. First of all we create a table for applying our Cursor.

Creation of table

First we create a table named emp to apply a cursor on this table:

create table emp(emp_id int,em_name varchar(10))

Insertion of data in table

After the creation we insert data as follows:
 

insert into emp

select 1,'d'union all

select 2,'deepak'union all

select 3,'gourav'union all

select 4,'mahi'union all

select 5,'gill'union all

select 6,'singh'

 

We will see the table as:

select * from emp


Output:

        cursor-in-sql.jpg

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 spesific record.

Declaration of Forward Only cursor:

We declare a forward-only Cursor as in the following:

declare curscr  cursor

for

select * from emp

Opening the forward-only Cursor

We open a Forward Only Cursor as in the following:


open
curscr

Fetching data from a Forward Only cursor

We can fetch only next data from the Forward Only Cursor as in the following:

fetch next from curscr

If we execute this query three times than we get the following output:

Output:
forward-cursor-in sql.jpg

forwerd-cursor-in-sql-server.jpg

forward-cursor-in-sql-sarver.jpg


Closing the Forward Only Cursor

We close the Forward Only Cursor as in the following:

close curscr

Dealoting the Forward Only Cursor :

We delete a Forward Only Cursor as in the following:

deallocate curscr

Scroll Cursor:

We can fetch any record as first, last, prior and specific record from the table.

Declaration of Scroll cursor:

We declare a Scroll Cursor as in the following:

declare scroll_cursor cursor
scroll
for
select
* from emp

Opening the Scroll Cursor:

We open a Scroll Cursor as in the infollowing:

open scroll_cursor

Fetching data from Scroll cursor

Fetching the first data

fetch first from scroll_cursor

Output:
             scroll-cursor-in-sql-server-first.jpg

Fetching the next data

fetch last from scroll_cursor

Output:

scroll-cursor-in-sql-serever-nest.jpg

Fetching previous data

fetch prior from scroll_cursor

Output:

scroll-cursor-in-sql-serever-priveos.jpg

Fetching Last data

fetch last from scroll_cursor

Output:

scroll-cursor-in-sql-serever-last.jpg

Fetching absolute data

This fetches the specific data giving absolute position:

fetch absolut 4 from scroll_cursor

Output:

scroll-cursor-in-sql-serever-absolute.jpg

Fetching relative data

fetch relative 2 from scroll_cursor

Output:

scroll-cursor-in-sql-serever-relative.jpg


Closing the Scroll Cursor

We close Scroll Cursor as in the following

close scroll_cursor

Dealoting the Scroll Cursor :

We delete Scroll Cursor as following

deallocate scroll_cursor

Summary

In this article I described Cursors. I hope this article has helped you in understanding this topic. Please share it. If you know more about this, your feedback and constructive contributions are welcome.

COMMENT USING

Trending up