What is cursor? How many Types of cursor? how to create each one?

[mcasaurabhsumit]

Hello Friend,
can u send me about cursor? i find it most difficult.

What is cursor? How many Types of cursor? how to create each one?
what r the benefit of each cursor?

[useroracle]

Hello Friend,
can u send me about cursor? i find it most difficult.

What is cursor? How many Types of cursor? how to create each one?
what r the benefit of each cursor?

Cursors are special programming constructs that allow data to be manipulated on a row-by-row basis, similar to other structured programming languages. They are declared like a variable, and then move one record at a time using a loop for control. There are three types of cursors, DYNAMIC, STATIC, and KEYSET. There are also three types of locks for cursors, READ ONLY, SCROLL LOCKS, and OPTIMISTIC.
Benefit - Featch more records at time for that purpose you can use Cursor.

Eg:
USE pubs
GO
DECLARE get_price CURSOR FOR
SELECT price FROM titles
OPEN get_price
FETCH NEXT FROM get_price
WHILE @@FETCH_STATUS = 0
FETCH NEXT FROM get_price
CLOSE get_price
DEALLOCATE get_price

[useroracle]

Cursors are special programming constructs that allow data to be manipulated on a row-by-row basis, similar to other structured programming languages. They are declared like a variable, and then move one record at a time using a loop for control. There are three types of cursors, DYNAMIC, STATIC, and KEYSET. There are also three types of locks for cursors, READ ONLY, SCROLL LOCKS, and OPTIMISTIC.

Eg:
USE pubs
GO
DECLARE get_price CURSOR FOR
SELECT price FROM titles
OPEN get_price
FETCH NEXT FROM get_price
WHILE @@FETCH_STATUS = 0
FETCH NEXT FROM get_price
CLOSE get_price
DEALLOCATE get_price

[iburyak]

Cursor doesn't have benefits. Main idea is not to use it.

It is the worst and slowest type of programming you can get and used as a last resort when nothing could be done otherwise.


Good Luck.

[iburyak]

Cursors are special programming constructs that allow data to be manipulated on a row-by-row basis, similar to other structured programming languages. They are declared like a variable, and then move one record at a time using a loop for control. There are three types of cursors, DYNAMIC, STATIC, and KEYSET. There are also three types of locks for cursors, READ ONLY, SCROLL LOCKS, and OPTIMISTIC.

Eg:
USE pubs
GO
DECLARE get_price CURSOR FOR
SELECT price FROM titles
OPEN get_price
FETCH NEXT FROM get_price
WHILE @@FETCH_STATUS = 0
FETCH NEXT FROM get_price
CLOSE get_price
DEALLOCATE get_price

Don't you think you described above VB cursor types not SQL types.... :)

[SnehaAgrawal]

Hi I have a cursor in a Stored Procedure @OutputCursor.. It gives me error while executing tht The variable @outputcursor does not have a cursor allocated to it...plz help

[Delerna]

SnehaAgrawal
Post the code (but in your own thread, not here in someone elses.)

Absolutely agree with Iburyak. Steer clear of cursors unless there is no other way to achieve your objective.
I have seen/performed identical tasks 1 using cursors and the other using queries.
In every case the cursors taking many many minutes (even hours in some cases)
are achieved in seconds using queries and the end results are identical.

Don't get me wrong, cursors do have their place (very occasionally).
Just try and see if you can do it with a query first, and usually you can.


Cursors work through a recordset one row at a time. Slow, especially as recordsets get bigger.
Queries work on an entire recordset as a batch. Much much faster especially as recordsets get bigger.