Why we need SQL's cursor?

Question

Image there are two tables:

Table_1:
id | name | phone
---| ---- | ----
1  | aaaa | 1111
2  | bbbb | 2222
3  | cccc | 3333
...| ...  | ...

Table_2 who is empty now:
id | name | phone
---| ---- | ----

If you want to select some rows from table_1 and insert them into table_2, we can do this(first way):

insert into table_2
  select * from table_1 where name='aaaa';

It's simple and easy! Just one single line code, but with cursor we need lines of codes(Second way use MySQL's syntax):

DECLARE done INT DEFAULT 0;  
DECLARE id int(3);  
DECLARE name char(11);  
DECLARE phone char(11);  
DECLARE mycur CURSOR FOR SELECT * FROM Table_1 where name='aaaa';  
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;  

OPEN mycur;  

REPEAT  
FETCH mycur INTO id,name,phone;  
IF NOT done THEN  
  INSERT INTO Table_2 VALUES (id,name,phone);  
END IF;    
UNTIL done END REPEAT;

CLOSE mycur;

I mean, in a normal producer, can we JUST use the first way to handle the the result set WITHOUT cursor? And when should we REALLY need a cursor??

Use cursors when whatever you are trying to do becomes so convoluted that you raise your hand and are willing to say "Yes, I don't care how it happens, how slow, even 1000 times slower, get er done" — Drew, Oct 25 '16 at 02:52
@Drew Could you please explain it with more details in an answer, maybe some examples, thanks~ — KaKa, Oct 25 '16 at 02:57
Maybe it would be easier to answer if you had something tricky that you are facing, and can't see a way clear of doing it without cursors. This is a common feeling early on in sql dev where the mind is geared toward procedural thinking, my way, my for loop versus sets and relations and the fast way sql engines work. So sql builds in a crutch (Cursors). As you go onward in your journey you will find ways to perform your tasks in sets which is what the engine is optimized for. Cursors are like terribly slow. — Drew, Oct 25 '16 at 03:03
[Here](http://stackoverflow.com/a/38693721) is a self-answer that I did using cursors. I don't doubt it could be done without them. But I just wanted to get that answer out and have people understand it if they changed it. So, for more methodical ordering and presentation peculiarities might be a reason to use cursors. Like pretty printing. — Drew, Oct 25 '16 at 03:12
MySQL and Oracle are two totally different DBMSes with different syntax and capabilities. Your question cannot possibly be about both at the same time. Please do not just randomly add tags because they have familiar or similar names. Tags here have meaning and relevance. If you're not absolutely positive a tag applies, read it's description. If you're still not positive, don't use it; someone here will add it for you if needed. — Ken White, Oct 25 '16 at 03:14

David דודו Markovitz · Answer 1 · 2016-10-25T07:34:19.493

Cursors are needed where SQL statement cannot be used, in most cases for operations other than data manipulation. e.g. -

Go over a list of tables and operate on each, e.g. collect statistics / add/drop partition.
Go over a list of customers and for each one of them send a mail / generate an invoice

P.s.

It is very, very, very rare that data cannot be (efficiently) manipulated by SQL statements and cursor is needed, especially when the database supports analytic functions.

Why we need SQL's cursor?

1 Answers1