Filter the return of Stored Procedure

Question

Filter the return of Stored Procedure

Navigation

#1 by (1 votes)

0

I created the following SP:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER='root'@'localhost' PROCEDURE 'sp_processoFinalizado'(IN mantenedor int(11))
BEGIN

select 
        'p'.'protocolo' AS 'protocolo',
        'p'.'id' AS 'idProcesso',
        'p'.'der' AS 'der',
        'p'.'data_habilitacao' AS 'dhab',
        'p'.'id_usuario' AS 'idUsuario',
p.mantenedor as mantenedor,
        'ap'.'id_andamento' AS 'codAndamento',
        'ap'.'data' AS 'dataAndamento',
        'ap'.'observacao' AS 'observacao',
        'ap'.'id_usuario' AS 'idUsuarioEvento',
        'u'.'nome' AS 'nome',
        'u'.'matricula' AS 'matricula',
        'u'.'lotacao' AS 'lotacao',
        (to_days('ap'.'data') - to_days('p'.'der')) AS 'tempoConclusao',
        (to_days('ap'.'data') - to_days('p'.'data_habilitacao')) AS 'tempoDecisao',
        'a'.'descricao' AS 'desAndamento'
    from
        ((('processo' 'p'
        join 'andamento_processo' 'ap' ON (('p'.'id' = 'ap'.'id_processo')))
        join 'usuario' 'u' ON (('u'.'id' = 'p'.'id_usuario')))
        join 'andamento' 'a' ON (('ap'.'id_andamento' = 'a'.'id')))
    where
        ('p'.'bol_finalizado' = 'S')
  and p.mantenedor  = mantenedor
    order by 'p'.'der' , 'p'.'data_habilitacao';


END

Calling her by code CALL sp_processoFinalizado(2225566) they return the data to me correctly.

My question is how to filter this data before arriving in PHP.

For example:

CALL sp_processoFinalizado(2225566) where tempoDecisao < 30;

In this case I want to inform a parameter to the SP and with this return I want to apply a filter, which at that time will be the where clause.

I've been able to do this using views but when the mass of data is too large, we have a bottleneck, because the processes are loaded regardless of what is in the case is the parameter informed, and then apply the filter. So I would like to improve the performance by listing only the ones that are interesting to later apply the filter so I thought about using SP.

mysql stored-procedures

asked by anonymous 27.11.2014 / 02:31

1 answer

Run JavaFX application on Android [closed] Change and delete objects before persisting in DB

score 1 · Accepted Answer

What you want to do is impossible via Stored Procedures because of the way it works, as if you are a closed function.

If you need to increase your query you can:

1 - Enter this parameter as your Stored Procedure argument

DELIMITER $$

CREATE DEFINER='root'@'localhost' PROCEDURE 'sp_processoFinalizado'(IN mantenedor int(11), IN tempo int(11))
BEGIN

select 
        'p'.'protocolo' AS 'protocolo',
        'p'.'id' AS 'idProcesso',
        'p'.'der' AS 'der',
        'p'.'data_habilitacao' AS 'dhab',
        'p'.'id_usuario' AS 'idUsuario',
p.mantenedor as mantenedor,
        'ap'.'id_andamento' AS 'codAndamento',
        'ap'.'data' AS 'dataAndamento',
        'ap'.'observacao' AS 'observacao',
        'ap'.'id_usuario' AS 'idUsuarioEvento',
        'u'.'nome' AS 'nome',
        'u'.'matricula' AS 'matricula',
        'u'.'lotacao' AS 'lotacao',
        (to_days('ap'.'data') - to_days('p'.'der')) AS 'tempoConclusao',
        (to_days('ap'.'data') - to_days('p'.'data_habilitacao')) AS 'tempoDecisao',
        'a'.'descricao' AS 'desAndamento'
    from
        ((('processo' 'p'
        join 'andamento_processo' 'ap' ON (('p'.'id' = 'ap'.'id_processo')))
        join 'usuario' 'u' ON (('u'.'id' = 'p'.'id_usuario')))
        join 'andamento' 'a' ON (('ap'.'id_andamento' = 'a'.'id')))
    where
        ('p'.'bol_finalizado' = 'S')
  and p.mantenedor  = mantenedor
  and (to_days('ap'.'data') - to_days('p'.'data_habilitacao')) < tempo 
    order by 'p'.'der' , 'p'.'data_habilitacao';


END

2 - Create a pseudo Materialized View . Conceptually it would be a View with a concrete table behind in order to improve performance. Because MySQL does not have this functionality by default, you can implement that kind of mechanism by hand.

I will not go into detail because it is something complex and would lengthen my answer a lot, however you can see how to do this here < sup> in .