While to Analyze Select Return

Question

good afternoon!

I’m having my first contact with Mysql, before I worked with Oracle, and I came across a problem that on Oracle, I had the following structure:

for a in (select id, nome, salario from usuario)
loop
/* 
  Meu código aqui. Realizar um novo select, insert, update e etc.
*/
end loop;

With this structure I was able to work line by line of the return of select, but in Mysql I searched and I did not find anything similar, it seems that it does not even implement the command for. I wonder if anyone ever needed something like this and how it was that solved the problem, or even ideas to get around the problem would already make it much easier.

Thank you

Answer 1

To iterate on a result of a SELECT use a CURSOR. Applied in your example would look like this:

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE id INT;
  DECLARE nome CHAR(16);
  DECLARE cur CURSOR FOR SELECT id, nome FROM usuario;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;
  
  read_loop: LOOP
    FETCH cur INTO id, nome;

    IF done THEN
      LEAVE read_loop;
    END IF;
    
    // Aqui seu código
  END LOOP;

  CLOSE cur;
END;

CURSORS

Mysql Supports Cursors Inside stored Programs. The syntax is as in Embedded SQL. Cursors have These properties:

Asensitive: The server may or may not make a copy of its result table

Read only: Not updatable

Nonscrollable: Can be traversed only in one Direction and cannot Skip Rows

Cursor declarations must appear before Handler declarations and after variable and condition declarations.

In free translation:

Mysql supports cursors within stored procedures. Syntax is like in standard SQL. Cursors have these properties:

Asensitive: The server can or cannot make a copy of the table results

Read only: Not upgradable

Nonscrollable: Can be traversed in only one direction and cannot skip lines

Cursor declaration must appear before Handler declension and after conditional variables and declarations.