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.
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;
MySql cursors are "asensitive": you should avoid to update the table otherwise you can get unexpected results...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
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;