select com loop em stored procedures

[tigredonorte 1]

Olá, comecei recentemente a trabalhar com stored procedures, transactions e loops no mysql (ou seja, estou apanhando bastante..). Preciso que uma stored procedure execute um select e itere sobre os elementos do mesmo; Para cada item selecionado será alterado um valor em outra tabela.

A aplicação é para o controle de estoque de uma loja virtual.

Segue o link no sqlfiddle com uma versão antiga da procedure e com o esquema de dados: http://sqlfiddle.com/#!2/7f66c/2

Segue o trecho que preciso fazer funcionar:

FOR 
            SELECT `produto` INTO @prod, `quantidade` INTO @qtd
            FROM `dcoracoes_item` 
            WHERE `pedido` = cod_pedido
        DO
          UPDATE `dcoracoes_produto` 
          SET `estoque` = `estoque` - @qtd
          WHERE `cod` = @prod; 
 
          UPDATE `dcoracoes_item` 
          SET `total` = 
          (   SELECT `preco`
              FROM `dcoracoes_produto` 
              WHERE `pedido` = cod_pedido AND `produto` = @prod
          ) ;
        END FOR;

Segue a procedure inteira..

drop procedure if exists myProc;
DELIMITER //
CREATE PROCEDURE myProc(IN cod_pedido INT (11))
BEGIN
    
    START TRANSACTION;
    
 select `status` INTO @v_status 
     from dcoracoes_pedido
     WHERE cod = cod_pedido;
    
    IF @v_status = 'criado' THEN
        FOR 
            SELECT `produto` INTO @prod, `quantidade` INTO @qtd
            FROM `dcoracoes_item` 
            WHERE `pedido` = cod_pedido
        DO
          UPDATE `dcoracoes_produto` 
          SET `estoque` = `estoque` - @qtd
          WHERE `cod` = @prod; 
 
          UPDATE `dcoracoes_item` 
          SET `total` = 
          (   SELECT `preco`
              FROM `dcoracoes_produto` 
              WHERE `pedido` = cod_pedido AND `produto` = @prod
          ) ;
        END FOR;
 
        UPDATE `dcoracoes_pedido` 
        SET `data` = NOW(), `total` = ( SELECT SUM(`total` * `quantidade`) FROM `dcoracoes_item` WHERE `pedido` = cod_pedido)         
        WHERE `cod` = cod_pedido ;
 END IF;
 
    SELECT estoque INTO @total
      FROM `dcoracoes_produto` 
      WHERE estoque < 0; 
 
    IF (@total >= 0) THEN 
       COMMIT; 
    ELSE 
       ROLLBACK; 
    END IF;
    SET AUTOCOMMIT=1;
END; //
 
DELIMITER ;
 
call myProc(1);

Agradeço pela paciência de você ter lido por aqui! :natalbiggrin:

[lokaodomau 61]

SELECT `produto`, `quantidade` INTO @prod, @qtd

[tigredonorte 1]

Olá lokaodomau, ainda não resolveu o problema. Na verdade, acho que ambas as sintaxes são aceitas. O meu problema em si é em executar o for...

[lokaodomau 61]

Pode explicar melhor esta PROCEDURE?

[tigredonorte 1]

lokaodomau, vou explicar melhor:

 

Tenho as tabelas: dcoracoes_produto, dcoracoes_venda e dcoracoes_item.

Cada produto vendido fica em dcoracoes_item e estes são agrupados em vendas na tabela dcoracoes_venda. Ou seja, em uma venda pode existir mais de um produto vendido.

 

O que eu preciso fazer?

Para cada item de uma venda, preciso atualizar o estoque da tabela produto, atualizar o valor do produto no momento que a venda foi fechada e atualizar o status e o total da venda ao executar a procedure.

 

Eu estava tentando fazer um for em mysql, porém, um amigo meu me deu uma dica: eu posso fazer update utilizando join (eu não sabia disto =] ), desta forma não é mais necessário executar um loop dentro da procedure, basta um update com join. Para os interessados o link do sqlfiddle (para testarem) e o código da procedure funcionando está abaixo:

drop procedure if exists proc_dcoracoes_stock;
DELIMITER //
CREATE PROCEDURE proc_dcoracoes_stock(IN cod_pedido INT (11))
BEGIN

	select `status` INTO @v_status 
    from dcoracoes_pedido
    WHERE cod = cod_pedido;
    
    IF @v_status = 'iniciado' THEN

        START TRANSACTION;
        SET autocommit=0;
	
		UPDATE `dcoracoes_item` as IT 
		inner join dcoracoes_produto as PROD on IT.produto = PROD.cod 
		SET IT.`total` = PROD.preco
		WHERE IT.pedido = cod_pedido;
        
		update dcoracoes_produto as PROD 
		inner join dcoracoes_item as IT on PROD.cod = IT.produto 
		set PROD.estoque = PROD.estoque - IT.quantidade
		WHERE IT.pedido = cod_pedido;
 
        UPDATE `dcoracoes_pedido` 
        SET `data` = NOW(), `status`='criado', `total` = ( SELECT SUM(`total` * `quantidade`) FROM `dcoracoes_item` WHERE `pedido` = cod_pedido)         
        WHERE `cod` = cod_pedido;

        SELECT estoque into @total
        FROM `dcoracoes_produto` as PROD
        inner join dcoracoes_item as IT on PROD.cod = IT.produto 
        WHERE PROD.estoque < 0 AND IT.pedido = cod_pedido 
        LIMIT 1;
     
        IF (@total < 0) THEN 
           ROLLBACK; 
           select -1;
        ELSE 
           COMMIT; 
           select 1;
        END IF;
        SET AUTOCOMMIT=1;

	END IF;
    
END; //

Problema do controle de estoque resolvido. Podem fechar o tópico.

 

Lokaodomau, obrigado pela disposição em ajudar-me!

[lokaodomau 61]

Marque seu post como resolvido! :D

[tigredonorte 1]

Não achei aonde marcar..

[Valter de Sousa 0]

Em 27/08/2014 at 10:46, tigredonorte disse:

Olá, comecei recentemente a trabalhar com stored procedures, transactions e loops no mysql (ou seja, estou apanhando bastante..). Preciso que uma stored procedure execute um select e itere sobre os elementos do mesmo; Para cada item selecionado será alterado um valor em outra tabela.

A aplicação é para o controle de estoque de uma loja virtual.

Segue o link no sqlfiddle com uma versão antiga da procedure e com o esquema de dados: http://sqlfiddle.com/#!2/7f66c/2

Segue o trecho que preciso fazer funcionar:

FOR 
            SELECT `produto` INTO @prod, `quantidade` INTO @qtd
            FROM `dcoracoes_item` 
            WHERE `pedido` = cod_pedido
        DO
          UPDATE `dcoracoes_produto` 
          SET `estoque` = `estoque` - @qtd
          WHERE `cod` = @prod; 
 
          UPDATE `dcoracoes_item` 
          SET `total` = 
          (   SELECT `preco`
              FROM `dcoracoes_produto` 
              WHERE `pedido` = cod_pedido AND `produto` = @prod
          ) ;
        END FOR;

Segue a procedure inteira..

drop procedure if exists myProc;
DELIMITER //
CREATE PROCEDURE myProc(IN cod_pedido INT (11))
BEGIN
    
    START TRANSACTION;
    
 select `status` INTO @v_status 
     from dcoracoes_pedido
     WHERE cod = cod_pedido;
    
    IF @v_status = 'criado' THEN
        FOR 
            SELECT `produto` INTO @prod, `quantidade` INTO @qtd
            FROM `dcoracoes_item` 
            WHERE `pedido` = cod_pedido
        DO
          UPDATE `dcoracoes_produto` 
          SET `estoque` = `estoque` - @qtd
          WHERE `cod` = @prod; 
 
          UPDATE `dcoracoes_item` 
          SET `total` = 
          (   SELECT `preco`
              FROM `dcoracoes_produto` 
              WHERE `pedido` = cod_pedido AND `produto` = @prod
          ) ;
        END FOR;
 
        UPDATE `dcoracoes_pedido` 
        SET `data` = NOW(), `total` = ( SELECT SUM(`total` * `quantidade`) FROM `dcoracoes_item` WHERE `pedido` = cod_pedido)         
        WHERE `cod` = cod_pedido ;
 END IF;
 
    SELECT estoque INTO @total
      FROM `dcoracoes_produto` 
      WHERE estoque < 0; 
 
    IF (@total >= 0) THEN 
       COMMIT; 
    ELSE 
       ROLLBACK; 
    END IF;
    SET AUTOCOMMIT=1;
END; //
 
DELIMITER ;
 
call myProc(1);

Agradeço pela paciência de você ter lido por aqui! :natalbiggrin:

  Tigre do Norte ,Muito boa sua procedure,mas percebi que se o pedido tiver mais de uma linha com o mesmo codigo de produto, ele só atualiza o valor correspondente a primeira linha.. você já tinha feito esse teste?