View with store procedure, mysql

Question

View with store procedure, mysql

Navigation

#1 by (1 votes)

1

I have a procedure correct and a view that uses the parameter of the procedure, except that the view does not see the parameter of the procedure, how to solve this problem?

Create a store procedure that, from the customer's code, can get the total discount value of 10%

delimiter $$
drop view if exists visao  $$
create view visao as select n.cod_cliente as codCli, (sum(i.qtd_vedida * i.pco_recebido)*0.9) as valorTotal
        from nota_fiscal as n, item_nota_fiscal as i
        where n.numero_nf = i.numero_nf and n.cod_cliente = AQUI SERIA O PARÂMETRO DA PROCEDURE, SÓ QUE A PROCEDURE VEM DEPOIS E ELE NÃO CONSEGUE PEGAR O PARÂMETRO, EXISTE ALGUMA SOLUÇÃO??(pCodCli )
        group by codCli $$

DROP PROCEDURE IF EXISTS uspDesconto $$
CREATE PROCEDURE uspDesconto (pCodCli int(11))

BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE vCodCli, vQtd_vedida int(11);  
  DECLARE vValorTotal decimal(10,2);
  DECLARE cont integer;

  declare cursor_a cursor for  select * from visao;

  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;
  set cont = 0;

  DROP TEMPORARY TABLE IF EXISTS tmp_Prod_Forn;
  CREATE TEMPORARY TABLE tmp_Prod_Forn (
        tmpCodCli int(11),
        tmpValorTotal decimal(10,2)
  );

  OPEN cursor_a;
  REPEAT FETCH cursor_a INTO vCodCli, vValorTotal;
     IF NOT done THEN
        insert into tmp_Prod_Forn values(vCodCli, vValorTotal);
        -- set cont = cont + 1;
     END IF;

  UNTIL (done) END REPEAT;
  close cursor_a;

  SELECT * FROM tmp_Prod_Forn;
END $$

delimiter ;

mysql

asked by anonymous 01.12.2015 / 20:59

1 answer

Define multiple styles for a UserControl through StaticResource's in WPF Error with CSS application

score 1 · Accepted Answer

Knowing that a view is the same as a table, as I explained in the answer from your previous question the only one thing to do is to use this parameter as if it were a filter for the query in the view, so at the time of creating the cursor, simply use the parameter in the where of the query in the view:

declare cursor_a cursor for  select * from visao where codCli = pCodCli;

EDIT

Viewing your edition I noticed where the parameter code is.

Your view should then be created as follows:

create view visao as 
    select n.cod_cliente as codCli, 
          (sum(i.qtd_vedida * i.pco_recebido)*0.9) as valorTotal
      from nota_fiscal as n, 
           item_nota_fiscal as i
     where n.numero_nf = i.numero_nf 
     group by codCli $$

You do not need to add the direct parameter in the view, this view will work as a table that will have the following format:

"Tabela" visao
  codCli
  valorTotal

So all you have to do is use the codCli field which is the same n.cod_cliente field you were trying to use. What you're doing here is putting the filter to be used out of view. So just use the view as previously reported here:

declare cursor_a cursor for  select * from visao where codCli = pCodCli;