MySQL stored procedure cursor for prepared statements

Question

I have two tables:

people_en: id, name people_es: id, name 

(please don’t worry about normalization, the design is normalized. The tables are much more complicated than that, but this is just a way to simplify my problem).

Then I have a stored procedure:

 CREATE PROCEDURE myproc(lang char(2)) BEGIN set @select = concat('SELECT * FROM ', lang, ' limit 3'); PREPARE stm FROM @select; EXECUTE stm; DEALLOCATE PREPARE stm; SET @cnt = FOUND_ROWS(); SELECT @cnt; IF @cnt = 3 THEN //Here I need to loop through the rows ELSE //Do something else END IF; END$$ 

More or less, the logic in the procedure:

If the choice gives 3 lines, we have to go through the lines and do something with the value in each line. Otherwise, something else (it doesn’t matter what, but I put this so that you understand that I need to have an if statement before the loop.

I saw and read about cursors, but couldn't find much for the favorites created by concat (does that matter?) And especially created with a prepared expression. How can I iterate over a list of results and use the values ​​from each row? Thanks.

Answer 1

I have some good and bad news for you.

Bad news first.

The MySQL manual states that the cursor cannot be used for a dynamic statement that is prepared and executed using PREPARE and EXECUTE. The statement for the cursor is checked at the time the cursor is created, so the statement cannot be dynamic.

So, while there are no dynamic cursors ... Here you will need something like this.

But now the good news: there are at least two ways to get around this - using vw or tbl.

Below I rewrote your code and application view to make a dynamic cursor.

 DELIMITER // DROP PROCEDURE IF EXISTS myproc; CREATE PROCEDURE myproc(IN lang VARCHAR(400)) BEGIN DECLARE c VARCHAR(400); DECLARE done BOOLEAN DEFAULT FALSE; DECLARE cur CURSOR FOR SELECT name FROM vw_myproc; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; SET @select = concat('CREATE VIEW vw_myproc as SELECT * FROM ', lang, ' limit 3'); PREPARE stm FROM @select; EXECUTE stm; DEALLOCATE PREPARE stm; SET @select = concat('SELECT * FROM ', lang, ' limit 3'); PREPARE stm FROM @select; EXECUTE stm; DEALLOCATE PREPARE stm; SET @cnt = FOUND_ROWS(); SELECT @cnt; IF @cnt = 3 THEN OPEN cur; read_loop: LOOP FETCH cur INTO c; IF done THEN LEAVE read_loop; END IF; #HERE YOU CAN DO STH WITH EACH ROW eg UPDATE; INSERT; DELETE etc SELECT c; END LOOP read_loop; CLOSE cur; DROP VIEW vw_myproc; ELSE SET c = ''; END IF; END// DELIMITER ; 

And to check the procedure:

 CALL myproc('people_en'); 

Answer 2

@clickstefan, you will have problems with two or more users trying to execute your script at the same time. The second user will receive the error message "View vw_myproc already exists" for the line:

 SET @select = concat('CREATE VIEW vw_myproc as SELECT * FROM ', lang, ' limit 3'); 

The solution is a temporary table - it exists only for the lifetime of the current connection, and users can simultaneously create temporary tables with the same name. So the code might look like this:

 DROP TABLE IF EXISTS vw_myproc; SET @select = concat('CREATE TEMPORARY TABLE vw_myproc AS SELECT * FROM ', lang, ' limit 3');