Cursors vs Procedures in SQL

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 c开发者_如何学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

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.

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.

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

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

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.

Stored procedures are pre-compiled objects and executes as bulk of statements, whereas cursors are used to execute row by row.

For Example: You can take cursors like a bag (cursors are similar to pointer that points out on a single row out of so many rows) and put all the result of a query run from within procedures, whenever you will need the results, just open the bag and find out the results row by row.