How to make a Stored Procedure dynamic by taking data from other tables in the database?

Question

I’m on a project to build a social network with the @Rodrigoborth, and we have the problem of How to index and update a user comparison system...
We were given the idea of working with Stored Procedure on Mysql and so I went after!
I read about concept, tutorials and etc, however, in none of them (nor in google) I found what I need: Update the fields within Mysql itself.
For example: when a user registers on the system I call a Procedure that updates/inserts in the compatibility table the amount that exists between him and other users.
In PHP I know how to do, but we have already left a lot of weight on it and try to see if it would be possible to do this in mysql, as I said earlier... I currently have the following code in Mysql:

CREATE PROCEDURE insertCompatibility(
IN varUsuario int(11)
)
BEGIN
     INSERT INTO compatibilidade (id,alvo,resultado) VALUES (varUsuario, varAlvo, varPorcentagem);
END;

I’d like to do something like this:

CREATE PROCEDURE insertCompatibility(
IN varUsuario int(11)
)
BEGIN    
    WHILE(linha = SELECT dadosDaTabela FROM usuarios WHERE id <> varUsuario){
        //depois eu colocaria o calculo aqui e então:
        INSERT INTO compatibilidade (id,alvo,resultado) VALUES (varUsuario, linha[id],resultadoDoCalculo);
    }
END;

(Sorry for the errors of Portuguese, of agreement and redundancies in the sentences, unfortunately I had to write this topic with a huge headache)

Answer 1

Cursors are really a powerful resource (see Tiago César Oliveira’s response). On the other hand, they are also too slow for certain types of operations.

I have worked a lot with optimization of batch processing in the bank and if it is possible to reduce its logic to one INSERT ... SELECT would be much faster. I’ve talked a little bit about it in another answer.

Let’s take an example:

INSERT INTO compatibilidade (id,alvo,resultado)
SELECT :varUsuario, id, <valor_calculado>
FROM usuarios 
WHERE id <> varUsuario;

In the query above :varUsuario is a parameter that can be passed via PHP.

Already <valor_calculado> depends on how you do the account. It can be a simple account, a subquery that consolidates data from another table or a Function.

Function is a good solution if you need some logic that you can’t do inline, but do not abuse the processing, as it will be executed for each selected line.

Answer 2

You can use cursores.

DECLARE a INT;
DECLARE cur CURSOR FOR SELECT i FROM test;

OPEN cur

read_loop: LOOP
    FETCH cur INTO a;

    //Operações
END LOOP;

---

Cursor is a powerful feature of Dbms. However, it can greatly impact the performance of your stored procedures. Therefore, test carefully the implementation of them.