how to do a dynamic Stored Procedure picking up data from other database tables?

Navigation
9

I'm in a social networking project with @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 in MySQL and then I went back! I read about concept, tutorials and etc, however, in none of them (nor google) I found what I need: Update the fields within MySQL itself . For example: when a user registers in the system I call a Procedure that updates / inserts in the compatibility table the amount that exists between it and the other users. In PHP I know how to do it, but we already left a lot of weight on top of it and would like to see if I could do this in mysql, as I said before ... 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 Portuguese errors, agreement and redundancy in sentences, unfortunately I had to write this topic with a headache)

    
asked by anonymous 07.03.2014 / 13:57

2 answers

8

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

I've worked hard with batch processing optimizations in the database and if you can narrow your logic to a INSERT ... SELECT would be much faster. I've already discussed this a bit in other answer .

Let's take an example: 

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

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

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

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

    
07.03.2014 / 15:42
4

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;

The cursor is a powerful feature of DBMS. However, it can greatly impact the performance of your stored procedures. So test the implementation carefully.

    
07.03.2014 / 14:47
Why can unboxing only be done for the type that was previously boxed? What is the difference between String name="test" and String s4 = new String ("Peter");