Jeff Moden - Tuesday, May 22, 2018 9:19 PM

Lynn Pettis - Tuesday, May 22, 2018 1:07 PM

jcelko212 32090 - Tuesday, May 22, 2018 12:59 PM

Ronnie65 - Friday, May 18, 2018 10:22 AM

In what situations should i use cursors over joins. I know that joins are much more faster than using cursors, but I was thinking there has to be a situation when i would want to use cursors over joins. Thanks !

The main reason we have them in SQL is that the original SQL engines back in the 1980s were built on top of existing filesystems. Also, as Jim great once said, we didn't know what the hell we were doing 🙂 the filesystems already had cursors in them. And if you grew up with IBM tape filesystems, it would recognize all of the basic cursor syntax is literally the commands from the original IBM tape files with a little different syntax.

I've written five cursors in my career, and I know the three of them should not have been written because they could do it with declarative code. Back in the old Sybase days, when we were teaching cursors, the example was updating the bookstore sample table. The problem is to give a discount on the expensive books (price greater than $25) for advertising purposes, but to raise the price on the cheap books. The naïve code look like this:

BEGIN
IF book_price <= 25.00
THEN UPDATE Bookstore
  SET book_price = book_price * 1.10
END IF;
IF book_price > 25.00
THEN UPDATE Bookstore
  SET book_price = book_price * 0.90
END IF;
END;

now consider a book priced at exactly $25. The first update in the sequence raises the price to $27.50. But now, but now the code goes sequentially to the next update and adjust the price to $24.75. This is clearly wrong.

The training example solution was to write a simple cursor with a row by row piece of logic to control the update. Later, we got the CASE expression and could do this with a simple update statement after SQL–92.

If you can find a computer science major, he might be able to dig up one of those horrible and mostly useless formal proofs that it is always possible to translate in LALR (1) procedural language into declarative primitive recursive functions.

Well, Joe, that's because your code above is flawed, period.

I'm thinking that was the whole point.  He did call it  the "naïve" code, which I took as a much more gentle way of saying "This is an example of what can go wrong if you're an idiot". 😀  And, no... Joe didn't write that.  It's the example from the scenario he described.