Cursors vs Procedures in SQL

Question

So, I just learned about CURSORS but still don't exactly grasp them. What is the difference between a cursor and procedure or even a function?

So far from the various examples (DECLARE CURSOR ... SELECT ... FROM ...) It seems at most its a variable to hold a query. Is the data real time, or a snapshot of when the cursor was declared?

i.e. I have a table with one row and one col with a value of 2. I do DECLARE CURSOR ... SELECT * FROM table1 I then insert a new row with a value of 3.

When I run the cursor, would I Just get the one row from before the cursor was declared, or both rows?

Thanks

Answer 1

CURSOR: do something that is to be done row by row and not possible with simple query for example, if you have a temporary table to store hierarchy of categories, cursor can be useful to populate it

procedures or stored procedures are something that can have collection of sql statements (including cursor) in it. when executed by passing params (if any) it will execute the statements in it

Answer 2

A function or procedure is a set of instructions to perform some task. A cursor is an array that can stores the result set of a query.

Answer 3

I believe the documentation will answer some of your questions. Read through the different options like "Insensitive" to see what they mean.

Also, as a general rule, it is frowned upon to use cursors. You should always try to find a "set based" solution before going the cursor route. There is much debate and documentation on this subject as well that is easily accessible.

Answer 4

I would recommend researching a bit on "Set based" versus "Row Based". This article does a decent job.

Most database systems are geared toward performing set based operations. Because of this you will often see performance problems when you perform row based operations (like using a cursor). In my experience A LOT of sql that uses cursors can be rewritten without cursors.

In the example you asked about your cursor would only have one record in it.

Also, keep in mind that a stored procedure can make use of a cursor.

Answer 5

My advice would be to forget you learned the syntax for cursors. Cursors are the last resort technique and should only be used by an expert who understnds what impact the cursor will have on performance and why the set-based alternatives won't work in his specific case. Most things done in cursors are far more easily done in a set-based fashion if you understand set operations. This link will help you learn what you need to know so that you will only rarely have to write a cursor:

http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them