CODEWITHSUNDEEP
mysqlsqldatabase
KaKa
KaKa

Reputation: 559

Why we need SQL's cursor?

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??

Upvotes: 1

Views: 320

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44941

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.

Upvotes: 2

Related Questions