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 lastBy 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
7902Absolute:
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
close empcursor65
deallocate empcursor65
output:
7369
7934
7902
7369
7900
7654
7788
7521
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 resultStatic, 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
update dept set DNAME='test' ,LOC='test' where DEPTNO=40
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=7369declare @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
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
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
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
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.
