Cursors In SQL SERVER

EmpTable

Dept Table

Cursors

Is the pointer to the temporary memory where data retrieved from a table is stored.

Main purpose of cursors is to provide access to a rows of a table in sequence in a T-SQL programming.

The set of rows returned by a select statement consists of all the rows that satisfy the condition in where clause of the statement. This complete set of rows returned by the statement is known as result set.

Cursors are used to process the multiple records. To work with cursors, you have to perform following five steps

·      Declare a Cursor

·      Open a Cursor

·      Fetch data from the Cursor

·      Close the Cursor

·      De-allocate the Cursor

 Cursor Declaration:

The first step in working with cursors is declaring the cursor and in cursor declartion you have to specify name of the cursor ,type of the cursor, and associate a select statement to the cursor, so that cursor will provide access to the rows retrieved by the select statement.

Cursor declaration syntax:

DECLARE cursor nameCURSOR

[LOCAL | GLOBAL]

[FORWARD_ONLY | SCROLL]

[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]

[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]

[TYPE_WARNING]

FOR <select statement>

Example:

declare c1 cursor

for select * from emp

When cursor is declared, so context area is created.

Local: specifies the scope of the cursor is local to the program in which cursor was created.

Global: specifies that the scope of the cursor is global to the connection. The cursor name can be referenced in any program by the connection.

Note:

Neither GLOBAL nor LOCAL is specified, the default is taken as GLOBAL.

2 Opening cursor:

To access data from the cursor, it must be open. When you open the cursor then the select statement associated with the cursor will be executed, rows retrieved by that select statement will be stored into temporary memory and cursor will point to that temporary memory.

Syntax: OPEN <cursor_name>

When cursor is opened

1 select statement is submitted to database server.

2 The records return by the select statement are loaded in context Area.

3 Cursor will be pointing to that Context Area.

3 Fetching data from the Cursor

The process of retrieving data from the table and storing it into variable into program is called as fetching data.

  

Syntax:

       FETCH [NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n]

            FROM <cursor name> INTO @variable name [...n] 

FIRST: Returns the first row in the cursor and makes it the current row.

LAST: Returns the last row in the cursor and makes it the current row.

NEXT: Returns the result row immediately following the current row and increments the current row to the row returned. If FETCH NEXT is the first fetch against a cursor, it returns the first row in the result set. 

NEXT is the default cursor fetch option.

Relative: option will fetch relatively nth row from the cursor to the current row.

If n is positive, returns the row n row beyond the current row.

If n is negative, returns the row n row prior to the current row.

Absolute:

If n is positive, it returns the specified nth row from the front of the cursor.

If n is negative, it returns the specified nth row from the last of the cursor.

After writing the fetch statement to verify whether or not fetch was success use the system

@@FETCH_STATUS (this does not requires to be declared)

Which can be any of the following values:

  

 0    -  The FETCH statement was successful

   -1 -  The FETCH statement failed or the row was beyond the result set

   -2 -  The row fetched is missing

Prior: returns the result row immediately preceding the current row, and decrements the current row to the row returned.

If fetch prior is first against a cursor, no row is returned and the cursor is left positioned before the first row.

4 closing the cursor

After completing accessing data using cursor, you can close the cursor and when you close the cursor than link to cursor from your program will be closed, but memory created for the cursor will not be deallocated.

5 Deallocate the cursor

This step will deallocate the memory allocated for the cursor and also data structures created for the cursor. Once the cursor is deallocated, you cannot open it again without executing the declaration statement.

Syntax:

Deallocate <cursorname>

Examples:

Write a program to display all employee names & salaries.

declare c1 cursor for

select ename,sal from emp

declare @ename varchar(20)

declare @sal smallmoney

open c1

fetch next from c1 into @ename,@sal

while(@@FETCH_STATUS=0)

begin

print @ename +'earns' + cast(@sal as varchar)

fetch next from c1 into @ename,@sal

end

close c1

deallocate c1

Output:

SMITH earns 800.00

ALLEN earns 1600.00

WARD earns 1250.00

JONES earns 2975.00

MARTIN earns 1250.00

BLAKE earns 2850.00

CLARK earns 2450.00

SCOTT earns 3000.00

KING earns 5000.00

TURNER earns 1500.00

ADAMS earns 1100.00

JAMES earns 950.00

FORD earns 3000.00

MILLER earns 1300.00

Depending on the availability of the cursor, cursors are classified into

Local

Global

Local cursors: are local to the program in which it is declared and will be automatically deallocated once the execution of the program is completed. Hence closing and deallocating cursor are not required for local cursor.

Write a program to increment salaries of employee based on job by accepting empno

President: 30%

Manager: 25%

Analyst: 20%

Salesman: 15%

Other job: 10%

declare empcursor12 cursor for

select empno,job,sal from emp

declare @eno int

declare @job varchar(20)

declare @salary money

open empcursor12

fetch next from empcursor12 into

@eno,@job,@salary

while(@@FETCH_STATUS=0)

begin

if @job='PRESIDENT'

set @salary=@salary+@salary+30/100

else if @job='MANAGER'

set @salary=@salary+@salary+25/100

else if @job='ANALYST'

set @salary=@salary+@salary+20/100

else if @job='SALESMAN'

set @salary=@salary+@salary+15/100

else

set @salary=@salary+@salary+10/100

update emp set sal=@salary where empno=@eno

fetch next from empcursor12 into @eno,@job,@salary

end

deallocate empcursor12

Example2:

declare empcursor1 cursor

for

select empno,job from emp

declare @empno int

declare @job varchar(30)

declare @sal money

open empcursor1

fetch next from empcursor1 into @empno,@job

begin

if @job='PRESIDENT'

update emp set sal=sal+sal*0.1 where empno=@empno

else if @job='MANAGER'

update emp set sal=sal+sal*0.08 where empno=@empno

else if @job='ANALYST'

update emp set sal=sal+sal*0.06 where empno=@empno

else

update emp set sal=sal+sal*0.05 where empno=@empno

fetch next from empcursor1 into @empno,@job

end

close empcursor1

deallocate empcursor1

Using a Global Cursor:

To calculate the total salary.

declare empcursor cursor global

for select ename,sal,comm from emp

declare @ename varchar(50),@sal money,@comm money,@totalsalary money

open empcursor

fetch next from empcursor into @ename,@sal,@comm

while(@@FETCH_STATUS=0)

begin

set @totalsalary=@sal+ISNULL(@comm,0)

print @ename + ' earns ' + cast(@totalsalary as varchar) + 'every month'

fetch next from empcursor into @ename,@sal,@comm

end

close empcursor

output:

SMITH earns 800.00every month

ALLEN earns 1900.00every month

WARD earns 1750.00every month

JONES earns 2975.00every month

MARTIN earns 2650.00every month

BLAKE earns 2850.00every month

CLARK earns 2450.00every month

SCOTT earns 3000.00every month

KING earns 5000.00every month

TURNER earns 1500.00every month

ADAMS earns 1100.00every month

JAMES earns 950.00every month

FORD earns 3000.00every month

MILLER earns 1300.00every month

In the above case, because it was a global cursor we are not using any deallocate cursor statement, now we use the same cursor in other program as follows:

declare @ename varchar(50),@sal money,@comm money,@annualsalary money

open empcursor

fetch next from empcursor into @ename,@sal,@comm

while(@@FETCH_STATUS=0)

begin

set @annualsalary=(@sal+ISNULL(@comm,0))*12

print @ename + ' earns'  + cast(@annualsalary as varchar) +  ' everyyear'

fetch next from empcursor into @ename,@sal,@comm

end

close empcursor

Types of Cursors:

Forward only

Scroll cursor

Forward only:

By default the cursor is forward only.

When a cursor is declared without any keyword related to the type of cursor, then by default the cursor is forward only cursor.

When the cursor is forward only cursor, then only option that work in fetch is next and you cannot fetch any other row from the cursor.

Scroll:

Specifies that the cursor can scroll from first to last row as well as last to first row also

It supports fetch methods like

1 fetch next

2 fetch prior

3 fetch first

4 fetch last

By default cursor supports forward only scrolling and supports fetch next statement.

Before learning scroll cursor let us learn the fetch options 

fetch prior example:

Prior: returns the result row immediately preceding the current row, and decrements the current row to the row returned.

These are the following values of empno:

7369

7499

7521

7566

7654

7698

7782

7788

7839

7844

7876

7900

7902

Example for fetch prior:

declare empcur12 cursor scroll

for select empno from emp

declare @empno int

open empcur12

fetch prior from empcur12 into @empno

print @empno

fetch next from empcur12 into @empno

print @empno

fetch prior from empcur12 into @empno

print @empno

close empcur12

output:

  -

7369

7369

Returns the result row immediately preceding the current row, and decrements the current row to the row returned.

If fetch prior is first, fetch against a cursor no row is returned & the cursor is left positioned before the first row.

2 Example on fetch prior:

declare empcur1234 cursor scroll

for select empno from emp

declare @empno int

open empcur1234

fetch prior from empcur1234 into @empno

print @empno

fetch last from empcur1234 into @empno

print @empno

fetch prior from empcur1234 into @empno

print @empno

close empcur1234

output:

-

7934

7902

Note:

If we are not closing the cursor, then observe the output

declare empcur124 cursor scroll

for select empno from emp

declare @empno int

open empcur124

fetch prior from empcur124 into @empno

print @empno

fetch last from empcur124 into @empno

print @empno

fetch prior from empcur124 into @empno

print @empno

output:

-

7934

7902

Now again run the cursor and see the output with error message as follows:

Msg 16915, Level 16, State 1, Line 2

A cursor with the name 'empcur124' already exists.

Msg 16905, Level 16, State 1, Line 4

The cursor is already open.

7900

7934

7902

fetch_first

declare empcur125 cursor scroll

for select empno from emp

declare @empno int

open empcur125

fetch first from empcur125 into @empno

print @empno

fetch last from empcur125 into @empno

print @empno

fetch prior from empcur125 into @empno

print @empno

close empcur125

output:

7369

7934

7902

Absolute:

If n is positive, it returns the specified nth row from front of the cursor.

If n is negative, it returns the specified nth row from the last of the cursor.

These are the following values of empno:

7369

7499

7521

7566

7654

7698

7782

7788

7839

7844

7876

7900

7902

7934

declare empscroll cursor scroll for

select empno from emp

declare @empno int

open empscroll

fetch absolute -4 from empscroll into @empno

print @empno

fetch absolute 7 from empscroll into @empno

print @empno

fetch absolute 2 from empscroll into @empno

print @empno

close empscroll

Output:

7876

7782

7499

declare empscroll12 cursor scroll for

select empno from emp

declare @empno int

open empscroll12

fetch absolute -0 from empscroll12 into @empno

print @empno

fetch absolute -1 from empscroll12 into @empno

print @empno

fetch absolute 2 from empscroll12 into @empno

print @empno

close empscroll12

Output:

-

7934

7499

Note: absolute -0 or absolute 0 the output is empty

Relative:

Option will fetch relatively nth row from the cursor to the current row.

If n is positive, returns the rows n rows beyond the current row.

If n is negative, returns the rows n rows prior to the current row.

These are the following values of empno:

7369

7499

7521

7566

7654

7698

7782

7788

7839

7844

7876

7900

7902

7934

declare empcursor451 cursor scroll for

select empno from emp

declare @empno int

open empcursor451

fetch next from empcursor451 into @empno

print @empno

fetch relative 3 from empcursor451 into @empno

print @empno

close empcursor451

output:

-

7369

7566

declare empcursor45111 cursorscroll for

select empno from emp

declare @empno int

open empcursor45111

fetch relative 0 from empcursor45111 into @empno

print @empno

fetch relative 3 from empcursor45111 into @empno

print @empno

close empcursor45111

output:

-

7521

declare empcursor451111 cursorscroll for

select empno from emp

declare @empno int

open empcursor451111

fetch relative -0 from empcursor451111 into @empno

print @empno

fetch relative 3 from empcursor451111 into @empno

print @empno

close empcursor451111

output:

-

7521

declare empcursor458 cursor scroll for

select empno from emp

declare @empno int

open empcursor458

fetch relative -1 from empcursor458 into @empno

print @empno

fetch relative 3 from empcursor458 into @empno

print @empno

close empcursor458

output:

-

7521

declare empcursor459 cursor scroll for

select empno from emp

declare @empno int

open empcursor459

fetch relative 6 from empcursor459 into @empno

print @empno

fetch relative -3 from empcursor459 into @empno

print @empno

close empcursor459

output:

7698

7521

declare empcursor4591 cursor scroll for

select empno from emp

declare @empno int

open empcursor4591

fetch relative 8 from empcursor4591 into @empno

print @empno

fetch relative -3 from empcursor4591 into @empno

print @empno

close empcursor4591

output:

7788

7654

declare empcursor4 cursor scroll for

select empno from emp

declare @empno int

open empcursor4

fetch relative 14 from empcursor4 into @empno

print @empno

fetch relative -3 from empcursor4 into @empno

print @empno

close empcursor4

output:

7934

7876

declare empcursor41 cursor scroll for

select empno from emp

declare @empno int

open empcursor41

fetch relative 15 from empcursor41 into @empno

print @empno

fetch relative -3 from empcursor41 into @empno

print @empno

close empcursor41

output:

-

7900

declare empcursor78 cursor scroll for

select empno from emp

declare @empno int

open empcursor78

fetch relative 17 from empcursor78 into @empno

print @empno

fetch relative -3 from empcursor78 into @empno

print @empno

fetch relative 12 from empcursor78 into @empno

print @empno

close empcursor78

output:

-

7900

7900

declare empcur911 cursor scroll for

select empno from emp

declare @empno int

open empcur911

fetch relative 17 from empcur911 into @empno

print @empno

fetch relative -3 from empcur911 into @empno

print @empno

fetch relative 9 from empcur911 into @empno

print @empno

fetch relative 4 from empcur911 into @empno

print @empno

close empcur911

output:

-

7900

7900

7900

declare empcur911 cursor scroll for

select empno from emp

declare @empno int

open empcur911

fetch relative 17 from empcur911 into @empno

print @empno

fetch relative -3 from empcur911 into @empno

print @empno

fetch relative 9 from empcur911 into @empno

print @empno

fetch relative 4 from empcur911 into @empno

print @empno

close empcur911

output:

7900

7900

7900

declare em111 cursor scroll for

select empno from emp

declare @empno int

open em111

fetch relative 3 from em111 into @empno

print @empno

fetch relative -1 from em111 into @empno

print @empno

fetch relative 4 from em111 into @empno

print @empno

close em111

output:

7521

7499

7698

declare er5218 cursor scroll for

select empno from emp

declare @empno int

open er5218

fetch relative 14 from er5218 into @empno

print @empno

fetch relative -1 from er5218 into @empno

print @empno

fetch relative 1 from er5218 into @empno

print @empno

close er5218

output:

7934

7902

7934

declare er52181 cursor scroll for

select empno from emp

declare @empno int

open er52181

fetch relative 14 from er52181 into @empno

print @empno

fetch relative -1 from er52181 into @empno

print @empno

fetch relative 7 from er52181 into @empno

print @empno

close er52181

output:

7934

7902

7902

Example by using scroll cursor and fetch options

These are the following values of empno:

7369

7499

7521

7566

7654

7698

7782

7788

7839

7844

7876

7900

7902

7934

declare empcursor65 cursor

scroll

for select empno from emp

declare @empno int

open empcursor65

fetch next from empcursor65 into @empno

print @empno

fetch last from empcursor65 into @empno

print @empno

fetch prior from empcursor65 into @empno

print @empno

fetch first from empcursor65 into @empno

print @empno

fetch absolute 12 from empcursor65 into @empno

print @empno

fetch absolute -10 from empcursor65 into @empno

print @empno

fetch relative 3 from empcursor65 into @empno

print @empno

fetch relative -5 from empcursor65 into @empno

print @empno
close empcursor65
deallocate empcursor65

output:

7369

7934
7902
7369
7900
7654
7788
7521

Write a program to display every 4 th record in the table.

declare c15 cursor scroll

for select ename from emp

declare @ename varchar(20)

open c15

fetch first from c15 into @ename

while(@@FETCH_STATUS=0)

begin

print @ename

fetch relative 3 from c15 into @ename

end

close c15

deallocate c15

output:

SMITH

JONES

CLARK

TURNER

FORD

Create a cursor for the table student which contains the following columns sno,sname,s1,s2,s3 and result table which contains sno,stot,savg,sres.

And display the details in result table with the status of sres  as pass or fail.

declarec1 cursor

forselect sno,s1,s2,s3 from student

declare@sno int,@s1 int,@s2 int,@s3 int

declare@stot int,@savg int

declare@sres char(4)

openc1

fetchnext from c1 into @sno ,@s1,@s2,@s3

while(@@FETCH_STATUS=0)

begin

set@stot=@s1+@s2+@s3

set@savg=CEILING((@stot)/3)

if@s1>35 and @s2>35 and @s3>35

set@sres='pass'

else

set@sres='fail'

insertinto result values(@sno,@stot,@savg,@sres)

fetchnext from c1 into @sno,@s1,@s2,@s3

end

closec1

output:

select* from student

select * from result

Static, dynamic, keyset

Static:

Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursors are answered from this temporary table in tempdb, therefore  any changes made to base table are not reflected to the result set.

 

Assume two different users working on two different systems

--Step1 first user

declare mycursor cursor

static for
select * from dept
open mycursor

--step2: first user
fetch last from mycursor

 --step3: second user
update dept set DNAME='test' ,LOC='test' where DEPTNO=40

 --step4: first user

fetch last from mycursor

--step5:first user
deallocate mycursor

Output:

40  OPERATIONS BOSTON

Explanation:

Step1: declare the cursor as static cursor and open

Step2: fetch the last row from the cursor and observe its value

Step3: update the row fetched from the cursor in second statement within the table.

Step4: fetch the last row from the cursor again and observe its value. The change made in the table in the third step will not reflect in the cursor because the cursor is static cursor.

Step5: deal locate the cursor.

Example 2:

declare empcursor871 cursor static

for select sal from emp where EMPNO=7369
declare @sal money
open empcursor871
update emp set sal=6000 where empno=1005
fetch next from empcursor871 into @sal
print @sal
close empcursor871
deallocate empcursor871

output:
800

 

Dynamic cursor

When a cursor is declared as dynamic cursor, than any changes made to the table after opening the cursor will reflect the cursor including the changes made to the primary key columns

Dynamic cursor is by default scroll cursor but absolute option in the fetch will not work with dynamic cursor.

--step 1

declare mycursor1 cursor

dynamic for

select * from DEPT

open mycursor1

--step 2

fetch last from mycursor1

--step3

update dept set DEPTNO=60 ,DNAME='purchases' ,LOC='hyd' where DEPTNO=50

--step4

fetch last from mycursor1

--step5

deallocate mycursor1

output:

50  fa  sdfa

Next output:

60  purchases  hyd

Steps:

1 declare the cursor as dynamic cursor and open
2 fetch the last row from the cursor and observe its values
3 update values of the row fetched in second step within the table including primary key columns
4 fetched the last row from the cursor again and observe its values. The changes made to the table in third step including the changes made to the primary key column will reflect in the cursor because of cursor is dynamic cursor.
5 deallocate the cursor

declare empcursor601 cursor dynamic

for select sal from emp where EMPNO=7369

declare @sal money

open empcursor601

update emp set sal=4000 where EMPNO=7369

fetch next from empcursor601 into @sal

print @sal

close empcursor601

deallocate empcursor601

Keyset

In case of keyset cursor any changes made the non-primary key columns of the table after opening the cursor will reflect in the cursor.

But any changes made to the primary key columns will make the row non available in the cursor, than we will get the value -2 within the system variable @@fetch_Status indicating the row was missing in the cursor.

Key set cursor also scroll cursor by default and every option in the fetch will work with keyset cursor.

declare mycursorkeyset1 cursor

keyset for

select * from DEPT

open mycursorkeyset1

fetch last from mycursorkeyset1

update dept set DNAME='operations',LOC='boston' where DEPTNO=60

deallocate mycursorkeyset1

Output:

60  purchases  hyd

Next output:

10  ACCOUNTING NEW YORK
20  RESEARCH   DALLAS
30  SALES  CHICAGO
40  test   test
60  operations boston

declare mycursorkeyset11 cursor

keyset for

select * from DEPT

open mycursorkeyset11

fetch last from mycursorkeyset11

update dept set DNAME='operations',LOC='boston' where DEPTNO=40

deallocate mycursorkeyset11

Output:

60  purchases  hyd

Next output:

10  ACCOUNTING NEW YORK
20  RESEARCH   DALLAS
30  SALES  CHICAGO
40  operations boston
60  operations boston

Steps:

Declare the cursor as keyset cursor and open it.

Fetch the last row from the cursor and observe its value

Update the values of the rows fetched from the cursor in second step within the table.

Update only non-primary key columns values, fetch the last row from the cursor again and observe its value. Change made to the non-primary key column in the table will reflect in the cursor because the cursor is keyset cursor.

Update primary key column value of the row fetched from the cursor in second step within the table.

Fetched the last row from cursor again and observe its values, rows will not be fetched indicating the rows was missing because the cursor is keyset cursor and changes are made to primary key column.