A cursor is a database object, having set of rows with a pointer that identify current row. So one can manipulate data row by row basis. Its like recordset in .net.
A cursor can be declared as forward only or scroll. It can be declared as read only.
A Static cursor has a separate copy of data in tempdb and serve all request from there so changes made to DB does not affect result of cursor, It does not allow modifications.
A Local cursor has scope in area where it is declared like in trigger or in stored procedure, while a Global cursor name have scope throughout the connection.
A Fast_forward cursor specifies forward_only, read_only cursor with performance optimization.
Ex.
declare @emp varchar(10), @sal money
declare myCursor cursor for
select empname, salary from tbl_employee
open mycursor
fetch next from mycursor into @emp, @sal
print @emp + ' getting salary ' + cast(@sal as varchar(10))
while @@FETCH_STATUS = 0
begin
fetch next from mycursor into @emp,@sal
print @emp + ' getting salary ' + cast(@sal as varchar(10))
end
close mycursor
deallocate mycursor
A cursor can be declared as forward only or scroll. It can be declared as read only.
A Static cursor has a separate copy of data in tempdb and serve all request from there so changes made to DB does not affect result of cursor, It does not allow modifications.
A Local cursor has scope in area where it is declared like in trigger or in stored procedure, while a Global cursor name have scope throughout the connection.
A Fast_forward cursor specifies forward_only, read_only cursor with performance optimization.
Ex.
declare @emp varchar(10), @sal money
declare myCursor cursor for
select empname, salary from tbl_employee
open mycursor
fetch next from mycursor into @emp, @sal
print @emp + ' getting salary ' + cast(@sal as varchar(10))
while @@FETCH_STATUS = 0
begin
fetch next from mycursor into @emp,@sal
print @emp + ' getting salary ' + cast(@sal as varchar(10))
end
close mycursor
deallocate mycursor
No comments:
Post a Comment