MYSQL Cursor will store data

CMDAI
CMDAI used Ask the Experts™
on
Hi !

Im trying to understand how to use MYSQL cursros in stored procedures.

If i have a cursor which fetches some data from table "orders"
and in the loop like below i am adjusting the same table that the cursor is fetching data from.

has my cursor recorded the data on open or will i get the update result.

Appreciate cas i could really understand the documentation on this point.

 

CREATE PROCEDURE processorders()
BEGIN

   -- Declare local variables
   DECLARE done BOOLEAN DEFAULT 0;
   DECLARE o INT;

   -- Declare the cursor
   DECLARE ordernumbers CURSOR
   FOR
   SELECT order_num FROM orders;

   -- Declare continue handler
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

   -- Open the cursor
   OPEN ordernumbers;

   -- Loop through all rows
   REPEAT

      -- Get order number
      FETCH ordernumbers INTO o;
--    **** I DO ADJUSTMENTS TO THE ORDERS TABLE HERE ***


   -- End of loop
   UNTIL done END REPEAT;

   -- Close the cursor
   CLOSE ordernumbers;

END;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
MySql cursors are "asensitive": you should avoid to update the table otherwise you can get unexpected results...
Reference:
http://dev.mysql.com/doc/refman/5.0/en/cursors.html
"Asensitive: The server may or may not make a copy of its result table "
that means you cannot count on the table stored in the cursor...

Author

Commented:
Thanks , appreciate the help

Commented:
simple test case :

DROP TABLE IF EXISTS tabletest;
CREATE TABLE tabletest (id int, a char(20));
INSERT INTO tabletest VALUES (1, ''), (2, ''), (3, ''), (10, ''), (11, ''), (12, ''), (22, ''), (35, ''), (44, '');

DELIMITER $$

DROP PROCEDURE IF EXISTS tmp_cur$$

CREATE PROCEDURE `tmp_cur`()
BEGIN
    DECLARE x int;
    DECLARE done INT DEFAULT 0;
    DECLARE cur_list_id_to_update CURSOR FOR
    SELECT id FROM tabletest;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN cur_list_id_to_update;
    REPEAT
        FETCH cur_list_id_to_update INTO x;
        UPDATE tabletest SET id = id+10, a=concat(a, '.') WHERE id = x;
    UNTIL done END REPEAT;
    CLOSE cur_list_id_to_update;
END$$

DELIMITER ;

SELECT * FROM tabletest;
CALL tmp_cur;
SELECT * FROM tabletest;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial