MySQL Forums
Forum List  »  Stored Procedures

Using LOOP and cursor
Posted by: penn rabb
Date: October 04, 2017 03:04PM

Hi all,

I am trying to update in table1 by checking if a number is between other numbers in columns of table2. I came across the following stored procedure example, but cannot figure out how to craft it to make it work.

In table1 there can be any number of rows, so I need to step through each and retrieve a result. This example looks pretty close to what I need:

DECLARE cur CURSOR FOR
SELECT `number`
FROM `table1`;

DECLARE done INT DEFAULT 0;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur;

LOOP

//this is the example, probably this works for it. but what I want to do is shown after it://
FETCH cur INTO userID; //Fetch one record from CURSOR and set to some variable(If not found then done will be set to 1 by continue handler)

// Here is what I need it to do. the sql isn't right, just reflects what I'm trying to do://

UPDATE table1 SET id_table2 = SELECT id FROM table2 WHERE (cur - or SELECT number FROM table1 - one at a time) BETWEEN table2.number_column1 AND table2.number_column2

// Of course if this is tried without a loop it tells you that 'subquery returns more than 1 row' //

IF done THEN
LEAVE read_loop; //If done set to 1 then exit the loop else continue
END IF;

/* Do your work * - this from the example, not sure what I should do here/

END LOOP;
CLOSE cur; //Closing the cursor


When I tried to load the procedure with this example in a real database, it said:
ERROR 1337 (42000) at line 18: Variable or condition declaration after cursor or handler declaration
Line 18 of the stored procedure is
/*!50003 CREATE*/ /*!50020 DEFINER=`utility`@`localhost`*/ /*!50003 PROCEDURE `update_table1`()

Options: ReplyQuote


Subject
Views
Written By
Posted
Using LOOP and cursor
6966
October 04, 2017 03:04PM
778
October 04, 2017 07:07PM
1261
October 05, 2017 08:55AM
632
October 05, 2017 09:01AM
776
October 05, 2017 12:19PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.