Link to home
Start Free TrialLog in
Avatar of CMDAI
CMDAI

asked on

MYSQL Cursor will store data

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

Avatar of glasnhost
glasnhost

MySql cursors are "asensitive": you should avoid to update the table otherwise you can get unexpected results...
ASKER CERTIFIED SOLUTION
Avatar of glasnhost
glasnhost

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of CMDAI

ASKER

Thanks , appreciate the help
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;