What is cursor 3

[mdwu]

I am new to SQl and I read many reference and books. However, I am still confuse on what is cursor. can someone explain in plain english. thanks.

 

[SQLSister]

A cursor is a way to loop through records. You select a group fo records and then perform some task against them, one record ata time. It is extremely inefficient and should be avoided at all costs. You should almost never need to use one. Suppose you do an update with a cursor, you select 20,000 records that need their price updated. The cursor then runs the update statment 20,000 times. A set-based solution will run one update statement. The time difference in running a cursor can range from seconds or milliseconds (in a small/very small data set) to hours (in a large dataset.)

Questions about posting. See faq183-874

 

[rac2]

A cursor is used in a procedure, a step-by-step process of retrieving and manipulating data.

The cursor is a pointer to a row in a table or in a set of rows. The cursor can be moved forward and backward. The data in one row can be combined with the data in another row. More than one operation can be performed for the row, then repeated on the next row.

I believe that cursors are not actually a formal part of the Structured Query Language. SQL always works with sets of rows, mini-tables we might say. SQl statements stand alone, they must be used one-at-time. For Microsoft SQL Server, the cursor is part of the Transact-SQL language, for Oracle it is part of PL*SQL.

Some problems require that several changes or calculations must be performed for each row in the set. Especially of interest are processes that compare data in different rows. Sometimes it can be quite difficult, even impossible to define a row that has all of the data needed for a calculation.

Typically when you need to know, "what happened next" you may need a cursor. Sometimes a database could have been defined differently and the cursor avoided, but we don't have the luxury to start over and do it right.

SQLSister is quite correct about the performance issue, whenever you can formulate a problem in terms of set of rows, use a query. I suspect she works with large datasets and heavy user loads where performance is a serious concern. I work with small datasets and light user loads, in the context of an intranet where the occasional cursor has no noticable effect.

 

[donutman]

Mdwu, to put the use of cursors into perspective, let me add that I've been programming with T-SQL for several years and have used them only once that I can remember. And that was before I knew better to avoid them. You will be amazed to find that set-based queries can solve almost all problems for which you would ordinarily think would require a cursor. This is particularly true when you come from a procedural programming background as I did.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mdwu]

Thanks all for the reply. you all mention about don't use cursor. then what should I use instead. "Set based Query" has been mention a few times, what is it?? Thanks for helping out a newbie.

 

[SQLSister]

A set-based query is a select, update, insert or delete which has all the criteria it needs within to do all the processing in one step. As to how to write that, it depends on what you are trying to do.

For instance you can use a cursor to select a group of records and then test each record to see if the value of field1 >100 and if it is then update that field by 10%
A set based query to do the same thing would be something like:

Update table1
set Field1 = field1 *1.1
where field1 >100

Often people use cursors becasue they don't know how to use an insert or update using a join to another table, so they run through the cursor to get the values for each record and do a value based insert or update.

Instead they could do something like

Insert into table1 (field1, field2)
Select field1, field2 from table2 where field1 >10000

Another way people use cursors is to do some comnditional processing - in one case do this in the other do that. You can use a Case statment instead within the select, insert update delte statament.

Questions about posting. See faq183-874

 

[mdwu]

Thanks all for the info..SQLSister, the example you show is simple and yet easy to understand. so basically, you don't have to be fancy such as cursor, triggers, etc to do complex queries.

 

[SQLSister]

That's true. Sometimes the SQL to avoid a cursor can get pretty complex using Case statements and erived tables and the like, but in that case the cursor was prolly pretty complicated too.

Questions about posting. See faq183-874

 

[johnherman]

CURSOR is an acronym for CURrent Set Of Rows. Cursors are useful for processing sets of data one (uaually) row at a time. Cursors are not part of ANSI-SQL, but almost every RDBMS supports cursors through their extended SQL language: PL/SQL for Oracle, Transact-SQL for Sybase and Microsoft, DB2/PL for IBM DB2, etc.

Cursors are almost always slower and more resource intensive than an equivalent SQL statement. While at Group 1 Software, I once met a guy who issued a challenge to everyone he had met; he said he cursors are never needed and that he could write ANSI-SQL to do anything anyone ever used a cursor to do. I don't know if he has been stumped.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright