Explain about the cursor?

cursors are used to process multiple records(row by row processing)

A cursor is a pointer or handler to context area which stores records returned select statement.

Using cursors in a single request we can get required data from server can be loaded into client side temporary memory called context area.

Steps to create a cursor
1.declare a cursor

syntax: declare cursorname cursor for select statement.
Ex
Declare c1 cursor for select eno, ename, sal from emp

Some memory is created or allocated when we declare a cursor. that memory is called context area.

2. Open a cursor

Syntax: open <cursor name>
open c1;

when we open a cursor.
1.select statement is submitted to db server.
2dbserver executes the select statement and returns data and data returned by select statement is loaded into context area.
3.cursor will point to a context area.

3.Fetch the records from cursor

Syntax:
Fetch first\next\last\prior\absolute\relative n from <cursorname> into variables

Ex

Fetch next from c1 into @eno, @sal, @comm

Fetch statement can fetches only one record at a time to process multiple records fetch should be executed number of times
Fetch statement should be placed inside a loop.
4.Closing a cursor

syntax:
close cursorname;
ex
close c1;

5. Deallocate cursor

When the cursor is closed the point to context area is removed.
when the cursor is de allocated the memory allocated for cursor is released.

deallocate cursorname
deallocate c1;


Ex

declare @ename varchar(50)
declare @sal smallmoney

declare c1 cursor for select ename,sal from emp
open c1
fetch next c1 into @ename,@sal
while(@@Fetch_status=0)
begin
print @ename +'earns' +cast(@sal as varchar(20))
Fetch next from c1 into @ename, @sal
end
close c1
deallocate c1