Is it possible to go through all the lines of a select, through some loop in the Mysql database?

Question

I have an application that I developed a few years ago in a hurry, and then I started working to improve its performance. In this app, there is a part of the process that I perform several actions followed in the database, initially my plan was to put in a trial but I had a problem, which was not able to handle line by line a select in Mysql (for report view)and due to lack of time to study the resolution dealing in the backend directly with python.

Today I have the same scenario, I need to generate a report from data from another table but I need to read line by line and I didn’t want to do it with python, because it would increase processes have some way to do it in a process?

I could make a loop defining the limit each turn to treat the lines, but wanted to know if it is possible to do with less code, within a process, type:

while (select `acao`,`regiao`,`id`,`data` into @acao, @regiao, @id, @data 
       from `historico` where `contrato` = '142';) Do

       if (@acao == '1') then 
           insert into movimentacao_acesso (`idm`, `data`, `regiao`) values (@id, @data, @regiao); 
       end if;

end while;

select * from movimentacao_acesso;

Thanks in advance. I just need to run this loop in the process

Answer 1

What can be done is to insert on the basis of a select, for this, it is not necessary to even create a procedure.

---

You create the structure for the insert:

insert into movimentacao_acesso (idm, data, regiao)

But the values and yes a select, where the order of the camps in the select needs to be the same as you created for the insert, would soon be as follows:

select id
       , data
       , regiao
    from historico
   where contrato = 142
     and acao = '1';

Putting it all together, you would get the following SQL statement:

insert into movimentacao_acesso (idm, data, regiao)
  select id
       , data
       , regiao
    from historico
   where contrato = 142
     and acao = '1';

See online: http://sqlfiddle.com/#! 9/603e706/1