MySQL stored procedure cursor for prepared statements

Question

I have a two tables:

people_en: id, name
people_es: id, name

(please, dont bother about normalization. the design is normalized. the tables are much more complex than this but this is just a way to simplify my problem).

I then 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 is:

If the select gives 3 rows, then we have to loop through the rows and do something with the value in each row. Otherwise somwthing else (not important what, but I put this to make you understand that I need to have an if statement before looping.

I have seen and read about cursors, but couldnt find much for selects created by concat (does it matter?) and especially created with a prepared statement. How can I iterate through the result list and use the values from each row? Thanks.

score 17 · Accepted Answer · edited Jan 16 '15 at 08:24

I have some bad and good news for you.

First the bad news.

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

So there are no dynamical cursors so far... Here you would need something like this.

But now the good news: there are at least two ways to bypass it - using vw or tbl.

Below I rewrote your code and applied view to make 'dynamical' 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 e.g. 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 test the procedure:

CALL myproc('people_en');

For this scenario, I would assume that CREATE TEMPORARY TABLE privilege will be checked at the point of running the stored routine, rather than at the point of creating it. Right? — Ifedi Okonkwo, Feb 12 '17 at 18:18

score 7 · Answer 2 · answered Feb 02 '15 at 16:19

7

@clickstefan, you will have problems with two or more users trying to execute your script at the same time. The second user will get 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 temporary table - it exists for the lifetime of current connection only, and users may simultaneously create temporary tables with the same name. So, code may looks like:

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

answered Feb 02 '15 at 16:19

TheKeksov

71
1
1

For this scenario, I would assume that CREATE TEMPORARY TABLE privilege will be checked at the point of running the stored routine, rather than at the point of creating it? – Ifedi Okonkwo Feb 12 '17 at 18:17
Create view will also works, we need to create it based on timestamp, and after procedure execution it would be deleted. In below statement: DROP TABLE IF EXISTS vw_myproc; table will not create but data may be mixed. if multiple users will call at sametime. – Pratik Jan 17 '19 at 07:59

MySQL stored procedure cursor for prepared statements

2 Answers2

Linked

Related