How to write a complex stored procedure?

Question

MySQL

Hello.
There is a table backet. It contains the fields id, description and all sorts of garbage.
There is a second table, backet_state, with fields id_backet, id_user, id_status.
There is a third table, orders, with id, timestamp, phone, comment.
A fourth table, order_state, with id_backet, id_order.
I want to do the following. Using a stored procedure in the backet_state table for a given id_user, change all id_status values, say, from 1 to 2, Add a new record to the orders table. In the order_state table, add records with id_backet, on which operations were performed to change the status, with a freshly added id from the operation of creating a new record in orders.
How do I imagine it

DELIMITER $$

CREATE
    
    PROCEDURE `toOrder`(IN var_user_id INT, IN var_phonenumber VARCHAR(255), IN var_comment TEXT)
    
    BEGIN
  UPDATE backet_state SET  id_status=2 WHERE id_user= var_user_id  AND id_status=1;
  INSERT INTO orders (phone, `comment`) VALUES (var_phonenumber, var_comment);
  INSERT INTO order_state (id_order, id_backet) SELECT  LAST_INSERT_ID(), id_backet FROM backet_state WHERE id_status=2 AND id_user= var_user_id;
    END$$

But. Will the LAST_INSERT_ID() function return to me every time the id that was received during the first insert? Or not?
If not, then you need to save the result of LAST_INSERT_ID () somewhere after the first insert, and get this value in the second insert and insert it into the SELECT subquery. How is it better to do that?

Answer 1

Will the LAST_INSERT_ID() function return to me every time the id that was received during the first insert?

Last insert.
In this example, everything seems to be correct. For at the time of the LAST_INSERT_ID() call, the last one was INSERT INTO orders.
PS Personally, I don't like stored procedures: it's hard to debug, rooted, and there are many other non-obvious problems.