MySQL Stored Procedure Loop Problem

S开发者_如何学编程o I have a cursor called open_loop that does a query and now I want to loop through it, check if the value it holds exists in the database, if so I need to update that entry, if not insert it.

The problem is, the way I quit the loop is by waiting for a state 02000 which is: Success, but no rows found. Unfortunately, that also occurs if there is no entry in the user table, so the loop is cut right then! How can I make it not trigger for that event, but still trigger if loop_cur runs out of entries, or perhaps find a different way of exiting the loop?

My code is here:

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
OPEN loop_cur;

REPEAT

    FETCH  loop_cur
    INTO   user_id, user_info

    SELECT Id INTO user_id  FROM users WHERE Id = user_id;

    IF user_id IS NOT NULL THEN

      UPDATE users
      SET info = user_info
      WHERE Id = user_id;

    ELSE
      INSERT INTO users (user_info);
    END IF;
  UNTIL done END REPEAT;
CLOSE loop_cur;

Please not that this is simplified since I'm really inserting and fetching many more values and the query for loop_cur is pretty complicated so I didn't include that, but it does work.

Finally, I cannot use INSERT ON DUPLICATE KEY UPDATE, what I'm checking isn't really a user_id, it's a different column, I used user_id for simplicity.

---

Use a boolean variable that is set to true within the loop.

Irrelevant code removed for simplicity:

DECLARE found boolean default false;

...

REPEAT

    FETCH  loop_cur ...

    SET found = true;

    ...

UNTIL done END REPEAT;

-- found is now true if you got some rows, false otherwise