none
Procedure com vários comandos. RRS feed

  • Pergunta

  • Tenho um problema...

    E gostaria de uma orientação dos mais experientes em SQL Server (2008 R2)...

    Percebo que uma procedure com varios comandos nao executam de forma eficaz.
    Devo estar esquecendo de algo... Devo estar errando em algo.

    Por exemplo, uma mesma procedure pode conter varios blocos de comandos:
    - INSERT...
    - UPDATE...
    - DELETE...
    - MERGE...
    - e assim por diante...

    No Firebird existe um comando chamado Commit Work; que voce executa no final de cada bloco de comando antes de passar para o proximo na mesma procedure. o Commit Work grava fisicamente os processos. Permitindo criar uma unica procedures com diversos comandos.

    Para contornar meu problema no SQL Server, ha casos que estou tendo que criar uma Procedure para cada bloco de comandos.
    Se eu continuar desse jeito, ao final do desenvolvimento meu sistema E.R.P. poderá vir a ter mais de 200 procudures.

    Abaixo esta um exemplo de procedure que não executa de forma eficaz (e tive que dividir cada bloco de comando em procedures diferentes):

    Seria o caso de usar "BEGIN TRANSACTION"? Ou existe outra forma?

    CREATE PROCEDURE [DBO].[SPVENDA_PEDIDOAPROVAR]  (
        @NEMPRESA INTEGER,
        @NPEDIDO INTEGER,
        @ACAO VARCHAR(10)
        )
    AS
    BEGIN
    
       IF @ACAO='APROVAR'
       BEGIN
           UPDATE PEDIDOS  SET APROVADO='S'
           WHERE
              EMPRESA=@NEMPRESA
           AND
              CODIGO=@NPEDIDO
       END;
    
    
       IF @ACAO='DESAPROVAR'
       BEGIN
           UPDATE PEDIDOS  SET APROVADO='N'
           WHERE
              EMPRESA=@NEMPRESA
           AND
              CODIGO=@NPEDIDO
       END;
    
       ------------------------------------------------------------------------
       -- ATUALIZAR TABELA MOVIMENTAÇÃO DE PEDIDOS (PEDIDOS_MOVIMENTO)
       ------------------------------------------------------------------------
       MERGE
          PEDIDOS_MOVIMENTO AS DESTINO
       USING
          ( SELECT *  FROM PEDIDOS_ITENS_GRADE
            WHERE EMPRESA=@NEMPRESA AND CODIGO=@NPEDIDO AND APROVADO='S' AND COR_ID>0 AND TAMANHO_ID>0 ) AS ORIGEM
       ON
          (DESTINO.EMPRESA = ORIGEM.EMPRESA
           AND
           DESTINO.CODIGO = ORIGEM.CODIGO
           AND
           DESTINO.REFERENCIA = ORIGEM.REFERENCIA
           AND
           DESTINO.COR_ID = ORIGEM.COR_ID
           AND
           DESTINO.TAMANHO_ID = ORIGEM.TAMANHO_ID
            )
             
    
       WHEN NOT MATCHED BY TARGET THEN
                INSERT (
                         EMPRESA
                        ,CODIGO
                        ,ORDEMPRODUCAO
                        ,REFERENCIA
                        ,APROVADO
                        ,IDREFERENCIA
                        ,COR_ID
                        ,COR
                        ,TAMANHO_ID
                        ,TAMANHO
                        ,ORDEM_GRADE
                        ,CELULA_COL
                        ,COLUNA_ROW
                        ,QTDE_PECAS
                        ,QTDE_ENTREGUE
                        ,QTDE_RESTANTE
                        ,VALOR_UNIT
    
                      )
                VALUES (
                         EMPRESA
                        ,CODIGO
                        ,0
                        ,REFERENCIA
                        ,APROVADO
                        ,''
                        ,COR_ID
                        ,COR
                        ,TAMANHO_ID
                        ,TAMANHO
                        ,ORDEM_GRADE
                        ,CELULA_COL
                        ,COLUNA_ROW
                        ,QUANTIDADE
                        ,0             -- ESTA COLUNA SERA ATUALIZADA POR OUTROS COMANDOS
                        ,QUANTIDADE    -- ESTA COLUNA SERA ATUALIZADA POR OUTROS COMANDOS
                        ,VALOR_UNIT
    
                )
    
    
       WHEN MATCHED THEN
          UPDATE  SET
             DESTINO.APROVADO=ORIGEM.APROVADO,
             DESTINO.QTDE_PECAS=ORIGEM.QUANTIDADE,
             DESTINO.QTDE_RESTANTE=(DESTINO.QTDE_PECAS-DESTINO.QTDE_RESTANTE),
             DESTINO.VALOR_UNIT=ORIGEM.VALOR_UNIT
    
    
       WHEN NOT MATCHED BY SOURCE AND (DESTINO.APROVADO='N' )  THEN
          DELETE
          
       OUTPUT $ACTION, INSERTED.*,    DELETED.*;
    
    
       ------------------------------------------------------------------------
       -- NÃO PERMITIR VALORES NULOS
       ------------------------------------------------------------------------
    
       UPDATE PEDIDOS_MOVIMENTO SET QTDE_PECAS=0
       WHERE
          (QTDE_PECAS IS NULL) OR (QTDE_PECAS < 1);
    
       UPDATE PEDIDOS_MOVIMENTO SET QTDE_ENTREGUE=0
       WHERE
          (QTDE_ENTREGUE IS NULL) OR (QTDE_ENTREGUE < 1);
    
       UPDATE PEDIDOS_MOVIMENTO SET QTDE_RESTANTE=0
       WHERE
          (QTDE_RESTANTE IS NULL) OR (QTDE_RESTANTE < 1);
    
    
       ------------------------------------------------------------------------
       -- ATUALIZAR CAMPOS
       ------------------------------------------------------------------------
       UPDATE PEDIDOS_MOVIMENTO SET
          EMPRESA      =  ( SELECT PEDIDOS.EMPRESA     FROM  PEDIDOS WHERE  PEDIDOS.CODIGO=PEDIDOS_MOVIMENTO.CODIGO ),
          DATA         =  ( SELECT PEDIDOS.DATA        FROM  PEDIDOS WHERE  PEDIDOS.CODIGO=PEDIDOS_MOVIMENTO.CODIGO ),
          CLIENTE      =  ( SELECT PEDIDOS.CLIENTE     FROM  PEDIDOS WHERE  PEDIDOS.CODIGO=PEDIDOS_MOVIMENTO.CODIGO ),
          VENDEDOR     =  ( SELECT PEDIDOS.VENDEDOR    FROM  PEDIDOS WHERE  PEDIDOS.CODIGO=PEDIDOS_MOVIMENTO.CODIGO )
    
       WHERE
          PEDIDOS_MOVIMENTO.EMPRESA=@NEMPRESA
       AND
          PEDIDOS_MOVIMENTO.CODIGO=PEDIDOS_MOVIMENTO.CODIGO;
    
    
       ------------------------------------------------------------------------
       -- ATUALIZAR TABELA MOVIMENTAÇÃO DE PRODUTOS (MOVIMENTOS_PRODUTOS)
       ------------------------------------------------------------------------
       MERGE
          MOVIMENTOS_PRODUTOS AS DESTINO
       USING
          ( SELECT *  FROM PEDIDOS_MOVIMENTO
            WHERE EMPRESA=@NEMPRESA AND APROVADO='S'  ) AS ORIGEM
       ON
          (DESTINO.EMPRESA = ORIGEM.EMPRESA
           AND
           DESTINO.REFERENCIA = ORIGEM.REFERENCIA
           AND
           DESTINO.COR_ID = ORIGEM.COR_ID
           AND
           DESTINO.TAMANHO_ID = ORIGEM.TAMANHO_ID
           AND
           ORIGEM.APROVADO='S'  )
    
       WHEN NOT MATCHED BY TARGET THEN
                INSERT (
                         EMPRESA
                        ,APROVADO
                        ,DATAMOVIMENTO
                        ,REFERENCIA
                        ,FASEATUAL
                        ,CELULAATUAL
                        ,MAQUINAATUAL
                        ,OPERADORATUAL
                        ,PROCESSOATUAL
                        ,TURNOATUAL
                        ,DEFEITOATUAL
                        ,QUALIDADEATUAL
                        ,COR_ID
                        ,COR
                        ,TAMANHO_ID
                        ,TAMANHO
                        ,ORDEM_GRADE
                        ,QTDE_VENDIDO
                        ,QTDE_DEMANDA
                        ,QTDE_PRODUZIR
                        ,QTDE_PRODUZIDO
                        ,QTDE_PRODUZINDO
                        ,QTDE_FISICO
                        ,QTDE_ESTOQUE
                        ,QTDE_PERDAS
                        ,QTDE_DEFEITOS                    
                      )
                VALUES (
                         EMPRESA
                        ,APROVADO
                        ,GETDATE()
                        ,REFERENCIA
                        ,0  
                        ,0  
                        ,0  
                        ,0  
                        ,0  
                        ,0  
                        ,0  
                        ,0  
                        ,COR_ID
                        ,COR
                        ,TAMANHO_ID
                        ,TAMANHO
                        ,ORDEM_GRADE
                        ,QTDE_PECAS
                        ,0  
                        ,0  
                        ,0  
                        ,0  
                        ,0  
                        ,0  
                        ,0  
                        ,0  
    
                )
    
    
       WHEN MATCHED THEN
          UPDATE  SET
             DESTINO.QTDE_VENDIDO=ORIGEM.QTDE_PECAS
    
    
       WHEN NOT MATCHED BY SOURCE  AND (DESTINO.QTDE_PRODUZIR<1)   THEN
          DELETE
          
       OUTPUT $ACTION, INSERTED.*,    DELETED.*;
    
    
    
       -- ***************************************************************
       -- ***             ATUALIZA MOVIMENTAÇÃO (PRODUZIR)         ******
       -- ***************************************************************
       UPDATE MOVIMENTOS_PRODUTOS   SET MOVIMENTOS_PRODUTOS.QTDE_PRODUZIR=COALESCE(( SELECT SUM(QTDE_TOTAL) AS QTDE_TOTAL FROM PCP_ORDEMPRODUCAO_MOVIMENTO
                                                                         WHERE
                                                                            PCP_ORDEMPRODUCAO_MOVIMENTO.EMPRESA=MOVIMENTOS_PRODUTOS.EMPRESA
                                                                         AND
                                                                            PCP_ORDEMPRODUCAO_MOVIMENTO.REFERENCIA=MOVIMENTOS_PRODUTOS.REFERENCIA
                                                                         AND
                                                                            PCP_ORDEMPRODUCAO_MOVIMENTO.COR_ID=MOVIMENTOS_PRODUTOS.COR_ID
                                                                         AND
                                                                            PCP_ORDEMPRODUCAO_MOVIMENTO.TAMANHO_ID=MOVIMENTOS_PRODUTOS.TAMANHO_ID
                                                                         AND 
                                                                            PCP_ORDEMPRODUCAO_MOVIMENTO.APROVADO='S'  ) ,0)
       WHERE
          MOVIMENTOS_PRODUTOS.EMPRESA=MOVIMENTOS_PRODUTOS.EMPRESA
       AND
          MOVIMENTOS_PRODUTOS.REFERENCIA=MOVIMENTOS_PRODUTOS.REFERENCIA
       AND
          MOVIMENTOS_PRODUTOS.COR_ID=MOVIMENTOS_PRODUTOS.COR_ID
       AND
          MOVIMENTOS_PRODUTOS.TAMANHO_ID=MOVIMENTOS_PRODUTOS.TAMANHO_ID;
       
    
       -- ***************************************************************
       -- ***             ATUALIZA MOVIMENTAÇÃO (VENDIDOS)         ******
       -- ***************************************************************
       UPDATE MOVIMENTOS_PRODUTOS   SET MOVIMENTOS_PRODUTOS.QTDE_VENDIDO=COALESCE(( SELECT SUM(QTDE_PECAS) AS QTDE_PECAS FROM PEDIDOS_MOVIMENTO
                                                                         WHERE
                                                                            PEDIDOS_MOVIMENTO.EMPRESA=MOVIMENTOS_PRODUTOS.EMPRESA
                                                                         AND
                                                                            PEDIDOS_MOVIMENTO.REFERENCIA=MOVIMENTOS_PRODUTOS.REFERENCIA
                                                                         AND
                                                                            PEDIDOS_MOVIMENTO.COR_ID=MOVIMENTOS_PRODUTOS.COR_ID
                                                                         AND
                                                                            PEDIDOS_MOVIMENTO.TAMANHO_ID=MOVIMENTOS_PRODUTOS.TAMANHO_ID
                                                                         AND 
                                                                            PEDIDOS_MOVIMENTO.APROVADO='S'  ) ,0)
       WHERE
          MOVIMENTOS_PRODUTOS.EMPRESA=MOVIMENTOS_PRODUTOS.EMPRESA
       AND
          MOVIMENTOS_PRODUTOS.REFERENCIA=MOVIMENTOS_PRODUTOS.REFERENCIA
       AND
          MOVIMENTOS_PRODUTOS.COR_ID=MOVIMENTOS_PRODUTOS.COR_ID
       AND
          MOVIMENTOS_PRODUTOS.TAMANHO_ID=MOVIMENTOS_PRODUTOS.TAMANHO_ID;
       
       
    END;


     x


    • Editado Adriano Zanini segunda-feira, 20 de fevereiro de 2012 18:34 formatação do script SQL
    segunda-feira, 20 de fevereiro de 2012 18:31

Todas as Respostas

  • Andriano,

    No SQL Server voce pode utilizar o SAVE TRANSACTION, exemplos:


    CREATE

    PROCEDURE dbo.TesteSavePoint


    AS


    BEGIN


    BEGIN

    TRAN a


    INSERT

    INTO Teste SELECT NEWID()


    SAVE

    TRANSACTION a


    BEGIN

    TRAN b


    INSERT

    Teste SELECT NEWID()


    SAVE

    TRANSACTION b


    END

    Maiores informações: http://msdn.microsoft.com/en-us/library/ms188378.aspx


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Blog Empresa: www.tripletech.com.br/blog
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com

    terça-feira, 21 de fevereiro de 2012 01:39
    Moderador
  • vc pode ter uma procedure genérica ou procedures especificas, mas quanto mais genérica mais difícil será para o MSSQL criar/manter um plano de execução otimizado e com isso problemas de desempenho podem aparecer.

    sobre comandos de controle de transação, todo banco de dados possue os comandos semelhantes e podem ser usados.

    vc citou o Commit Work, no MSSQL vc também tem o Commit Tran.

    eu não recomendo a criação de procedures genéricas e para um ERP ter várias procedures é algo normal.

    terça-feira, 21 de fevereiro de 2012 18:35
  • Oi,

    Comparando com o que citou sobre o firebird, pode ser necessário um begin transaction/commit transaction.

    Porém o ponto principal da questão é o que você citou em "Percebo que uma procedure com varios comandos nao executam de forma eficaz."

    O que você quer dizer com não executar de forma eficaz ?

    É difícil indicar a solução para o problema, nos diga primeiramente qual foi o exato problema que identificou na execução da procedure.

    Abs,

    Dennes
    ****** Evento : Inscreva-se já para o SQL Saturday #127 no Rio : http://www.sqlsaturday.com/127/eventhome.aspx - vagas limitadas !

     


    Dennes - Se resolveu, classifique a mensagem, por favor - [http://www.bufaloinfo.com.br] NOVO DVD Segurança no ASP.NET : http://www.bufaloinfo.com.br/LearingSeriesSegurancaASPNET2.asp

    quinta-feira, 23 de fevereiro de 2012 15:23
  • Adriana,

    Basicamente uma Stored Procedure, é um recurso criado no SQL Server com objetivo de automatizar, acelerar e melhorar todo processo de execução, desenvolvimento e administração de funcionalidades que podem demandar processamento e complexidade de execução.

    Você esta dizendo que Stored Procedures com diversos comandos se torna lenta, eu particularmente já desenvolvi e trabalhei com Stored Procedure de 150 á 2000 linhas e esta análise da quantidade de comandos nunca me chamou a atenção.

    Vale ressaltar que quanto mais executamos a mesma Stored Procedure, mais dinâmica e rápida a mesma é analisada e processada.

    Eu concordo com as observações do Dennes, acredito que você poderia esclarecer um pouco mais sobre as suas percepções para que possamos ajudar.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    segunda-feira, 27 de fevereiro de 2012 00:16
  • -- Algumas mudanças que vão ajudar a melhorar a execução de procedure pelo SQL
    1) Substituir @ACAO VARCHAR(10) por @APROVADO CHAR(1) -- Somente S ou N
       e Substituir "IF @ACAO='APROVAR' ...." e "IF @ACAO='DESAPROVAR'..." por um UPDATE só.
       Ex.:
        UPDATE PEDIDOS  SET APROVADO=@APROVADO
        WHERE
              EMPRESA=@NEMPRESA
           AND
              CODIGO=@NPEDIDO


    Obs.: São dois Comandos (IF) a menos para executar.


    3) 
       Substituir vários SELECTs da mesma tabela na mesma condição por somente um select.
       Os campos diferentes podem ser armazenados em variáveis.
       "UPDATE PEDIDOS_MOVIMENTO SET
          EMPRESA      =  ( SELECT PEDIDOS.EMPRESA     FROM  PEDIDOS WHERE  PEDIDOS.CODIGO=PEDIDOS_MOVIMENTO.CODIGO ) ..."
       
       Ex.: 
       DECLARE  
    @EMPRESA [TIPO],
    @DATA [TIPO],
    @CLIENTE [TIPO],
    @VENDEDOR [TIPO];


       SELECT
    @EMPRESA = EMPRESA,
    @DATA = DATA,
    @CLIENTE = CLIENTE,
    @VENDEDOR = VENDEDOR
       FROM PEDIDOS 
       WHERE  PEDIDOS.CODIGO=@PEDIDOS_CODIGO; -- Variável fictícia
       
       UPDATE PEDIDOS_MOVIMENTO SET
          EMPRESA      =  @EMPRESA,
          DATA         =  @DATA,
          CLIENTE      =  @CLIENTE,
          VENDEDOR     =  @VENDEDOR
       WHERE
          PEDIDOS_MOVIMENTO.EMPRESA=@NEMPRESA
       AND
          PEDIDOS_MOVIMENTO.CODIGO=@PEDIDOS_CODIGO; -- Variável fictícia


    Obs.: Notei que você está comparando [CAMPO] = [CAMPO] no WHERE do "UPDATE PEDIDOS_MOVIMENTO SET ...". 
    Essa condição será sempre verdadeira, já que é como comparar 1=1.
    Não seria atualizar apenas um pedido pelo código? Se for, primeiro é necessário obter o código do pedido.
    quinta-feira, 29 de março de 2012 16:21
  • (...)

    Porém o ponto principal da questão é o que você citou em "Percebo que uma procedure com varios comandos nao executam de forma eficaz."

    O que você quer dizer com não executar de forma eficaz ?

    É difícil indicar a solução para o problema, nos diga primeiramente qual foi o exato problema que identificou na execução da procedure.

    (a todos dsculpem a demora em responder, eu havia perdido a senha do acesso ao forum e não conseguia responder )

    Prezado Dennes,

    no caso de não executar de forma eficaz, quero dizer que nem todos os comando na mesma procedures (no exemplo inicial) são executados.

    Exemplo: Como voce pode observar, eu comentei cada bloco de comando. Ocorre que alguns desses blocos, em algum momento não são executados. Simplesmente não executa (até brinco aqui, que parece coisa sobrenatural).

    E esse erro ocorre PRINCIPALMENTE nos comandos UPDATE (quando preciso determinada tabela) conforme está exemplo inicial. Simplesmente não executa.

    E foi o caso, criei uma procedure para cada UPDATE. Por isso até "brinquei" que daqui a pouco vou ter mais de 200 procedures só para uma mesma transação. Sendo que eu poderia ter tudo numa mesma procedure (como  estou tentando fazer).

    Por isso comentei que "Percebo que uma procedure com varios comandos nao executam de forma eficaz."

    É nesse sentido.


    sábado, 14 de abril de 2012 14:20
  • Fabrizzio,

    vou seguir sua indicação. E postarei aqui sobre os resultados.
    Creio que possa resolver.

    Obrigado pela ajuda.

    sábado, 14 de abril de 2012 14:23
  • Obs.: Notei que você está comparando [CAMPO] = [CAMPO] no WHERE do "UPDATE PEDIDOS_MOVIMENTO SET ...". 
    Essa condição será sempre verdadeira, já que é como comparar 1=1.
    Não seria atualizar apenas um pedido pelo código? Se for, primeiro é necessário obter o código do pedido.

    Alex,

    nesse caso sim...  de certa forma voce tem toda razão.

    No entanto, foi uma forma que descobri (em estudos pessoais) anos atras sobre como aplicar UPDATE  sem afetar os outros registros.
    É algo que funciona em qualquer banco de dados SQL.

    Dessa forma fazendo ele comparar (ligar) a outra tabela cujas estruturas sejam semelhantes e com dados semelhantes sem afetar outras linhas.

    E no caso em questão, eu solicitei ao SQL que atualize o campo "QTDE_VENDIDO" restringindo-se ao que solicitei.

    Pode parecer um comando meio "absurdo". Mas acredite, funciona.

    Veja um exemplo gráfico:

    Formas de aplicar UPDATE atrasvés de tabelas diferentes. Sem afetar outras linhas.

     

    sábado, 14 de abril de 2012 14:49