none
Procedure para deletar registros de mais de uma tabela RRS feed

  • Pergunta

  • Boa tarde,

    pessoal.

     

    Como posso deletar registros de mais de uma tabela através de uma procedure?

    Eu vi em alguns tópicos que poderia utilizar cursor, mas consome muitos recursos do MSSQL, correto? Uma outra solução seria utilizar um laço, pelo que li e então fiz a procedure abaixo, mas não estou sabendo como poderia percorrer através do laço para pegar um ID por vez, alguém saberia me dizer:

     

    Code Snippet

    CREATE PROCEDURE dbo.STP_ExcluirAparelho

    (

          @CodAparelho int

    )

    AS

          SET NOCOUNT ON

         

          DECLARE @CodHistorico int

          DECLARE @Contador int

     

          BEGIN TRY

          BEGIN TRAN

              SET @Contador = (SELECT COUNT(CodHistorico) FROM TB_Historico WHERE CodAparelho = @CodAparelho)

     

              SET @CodHistorico = (SELECT CodHistorico FROM TB_Historico WHERE CodAparelho = @CodAparelho)

     

              WHILE (@Contador > 0)

              BEGIN

                    DELETE FROM TB_Leitura WHERE CodHistorico = @CodHistorico

                    DELETE FROM TB_Manutencao WHERE CodHistorico = @CodHistorico

                    DELETE FROM TB_TempoConsumivel WHERE CodHistorico = @CodHistorico

                    -- próximo CodHistorico???

                    SET @Contador = @Contador - 1

              END

             

              DELETE FROM TB_Historico WHERE CodAparelho = @CodAparelho

              DELETE FROM TB_Aparelho WHERE CodAparelho = @CodAparelho

          COMMIT TRAN

          END TRY

          BEGIN CATCH

          ROLLBACK TRAN

               SELECT -ERROR_NUMBER()

          END CATCH

     

     

    Acabei fazendo 2 Select na procedure, um para o laço e outra seria para ficar pegando o ID. Sei que vai dar errado a procedure acima, coloquei o 2º Select apenas para mostrar o que eu estou querendo fazer.

    Se alguém souber outra forma de fazer também, eu agradeço, estou aberto a sugestões.

    terça-feira, 16 de dezembro de 2008 18:30

Respostas

  • cara, pelo que vi da tua procedure, ficaria assim... 

    REATE PROCEDURE dbo.STP_ExcluirAparelho

    (

          @CodAparelho int

    )

    AS

          SET NOCOUNT ON

         

          DECLARE @CodHistorico int

          DECLARE @Contador int

     

          BEGIN TRY

          BEGIN TRAN

    DECLARE Cur CURSOR FOR SELECT CodHistorico FROM TB_Historico WHERE CodAparelho = @CodAparelho
    FETCH NEXT FROM Cur INTO @CodHistorico
    WHILE @@FETCH_STATUS = 0
    BEGIN
    OPEN Cur
    encao WHERE CodHistorico = @CodHistorico DELETE FROM TB_T
    DELETE FROM TB_Leitura WHERE CodHistorico = @CodHistorico DELETE FROM TB_Manu
    END
    CLOSE Cur
    DEALLOCATE Cur
    tempoConsumivel WHERE CodHistorico = @CodHistorico FETCH NEXT FROM Cur INTO @CodHistorico

    DELETE FROM TB_Historico WHERE CodAparelho = @CodAparelho
    DELETE FROM TB_Aparelho WHERE CodAparelho = @CodAparelho

          COMMIT TRAN

          END TRY

          BEGIN CATCH

          ROLLBACK TRAN

               SELECT -ERROR_NUMBER()

          END CATCH


    desculpe a formatação... rs

    terça-feira, 16 de dezembro de 2008 19:31
  • Boa Noite Marcelo,

     

    Não sei se entendi sua necessidade direito. Seria passar um código (@CodAparelho), recuperar os históricos e com bases neles fazer as exclusões em TB_Leitura, TB_Manutencao e TB_TempoConsumivel ?

     

    Se for isso, até é possível fazer com laços ou cursores, mas creio que seriam alternativas bem ineficientes já que estariam operando linha a linha e a operação em conjunto da SQL é na esmagadora maioria das vezes mais eficiente. Será que os códigos abaixo não seriam suficientes ?

     

    Code Snippet

    DECLARE @CodAparelho int

    SET @CodAparelho = 1

     

    DELETE FROM TB_Leitura

    FROM TB_Leitura AS L

    INNER JOIN TB_Historico AS H ON L.CodAparelho = H.CodAparelho

    WHERE H.CodAparelho = @CodAparelho

     

    DELETE FROM TB_Manutencao

    FROM TB_Manutencao AS M

    INNER JOIN TB_Historico AS H ON M.CodAparelho = H.CodAparelho

    WHERE H.CodAparelho = @CodAparelho

     

    DELETE FROM TB_TempoConsumivel

    FROM TB_Leitura AS T

    INNER JOIN TB_Historico AS H ON T.CodAparelho = H.CodAparelho

    WHERE H.CodAparelho = @CodAparelho

     

    [ ]s,

     

    Gustavo

     

    terça-feira, 16 de dezembro de 2008 22:27

Todas as Respostas

  • cara, faz um cursor e testa a performance... eu costumo usar cursores e não me dão problemas na maioria das vezes... a menos que seja um grande volume de dados...
    terça-feira, 16 de dezembro de 2008 18:34
  • Bem, vou testar então.

    Mas como ficaria a chamada do cursor no lugar do while nesta minha procedure?

    terça-feira, 16 de dezembro de 2008 19:22
  • cara, pelo que vi da tua procedure, ficaria assim... 

    REATE PROCEDURE dbo.STP_ExcluirAparelho

    (

          @CodAparelho int

    )

    AS

          SET NOCOUNT ON

         

          DECLARE @CodHistorico int

          DECLARE @Contador int

     

          BEGIN TRY

          BEGIN TRAN

    DECLARE Cur CURSOR FOR SELECT CodHistorico FROM TB_Historico WHERE CodAparelho = @CodAparelho
    FETCH NEXT FROM Cur INTO @CodHistorico
    WHILE @@FETCH_STATUS = 0
    BEGIN
    OPEN Cur
    encao WHERE CodHistorico = @CodHistorico DELETE FROM TB_T
    DELETE FROM TB_Leitura WHERE CodHistorico = @CodHistorico DELETE FROM TB_Manu
    END
    CLOSE Cur
    DEALLOCATE Cur
    tempoConsumivel WHERE CodHistorico = @CodHistorico FETCH NEXT FROM Cur INTO @CodHistorico

    DELETE FROM TB_Historico WHERE CodAparelho = @CodAparelho
    DELETE FROM TB_Aparelho WHERE CodAparelho = @CodAparelho

          COMMIT TRAN

          END TRY

          BEGIN CATCH

          ROLLBACK TRAN

               SELECT -ERROR_NUMBER()

          END CATCH


    desculpe a formatação... rs

    terça-feira, 16 de dezembro de 2008 19:31
  • Ok, sem problemas. Obrigado.

     

    Bem, vou testar aqui por enquanto e esperar para ver se alguém posta também uma sugestão com laço. Assim já testo para ver se vai ter alguma diferença.

     

     

    terça-feira, 16 de dezembro de 2008 19:59
  • Boa Noite Marcelo,

     

    Não sei se entendi sua necessidade direito. Seria passar um código (@CodAparelho), recuperar os históricos e com bases neles fazer as exclusões em TB_Leitura, TB_Manutencao e TB_TempoConsumivel ?

     

    Se for isso, até é possível fazer com laços ou cursores, mas creio que seriam alternativas bem ineficientes já que estariam operando linha a linha e a operação em conjunto da SQL é na esmagadora maioria das vezes mais eficiente. Será que os códigos abaixo não seriam suficientes ?

     

    Code Snippet

    DECLARE @CodAparelho int

    SET @CodAparelho = 1

     

    DELETE FROM TB_Leitura

    FROM TB_Leitura AS L

    INNER JOIN TB_Historico AS H ON L.CodAparelho = H.CodAparelho

    WHERE H.CodAparelho = @CodAparelho

     

    DELETE FROM TB_Manutencao

    FROM TB_Manutencao AS M

    INNER JOIN TB_Historico AS H ON M.CodAparelho = H.CodAparelho

    WHERE H.CodAparelho = @CodAparelho

     

    DELETE FROM TB_TempoConsumivel

    FROM TB_Leitura AS T

    INNER JOIN TB_Historico AS H ON T.CodAparelho = H.CodAparelho

    WHERE H.CodAparelho = @CodAparelho

     

    [ ]s,

     

    Gustavo

     

    terça-feira, 16 de dezembro de 2008 22:27
  • Bom dia,

    Gustavo.

     

    Teria que ser assim então:

    Code Snippet

    DELETE FROM TB_Leitura

    FROM TB_Leitura AS L

    INNER JOIN TB_Historico AS H ON L.CodHistorico = H.CodHistorico

    WHERE H.CodAparelho = @CodAparelho

     

     

    Pois eu não tenho CodAparelho, nas tabelas Manutenção, Leitura e TempoConsumivel. Assim é possível?

    E esse 2º FROM que você coloca, seria um SELECT * FROM TB_Leitura AS L ou é assim mesmo a sintaxe?

     

    quarta-feira, 17 de dezembro de 2008 10:24
  • Olá Marcelo,

     

    Sim de fato, você não possui o código do aparelho nas tabelas de manutenção, leitura e tempo consumível. Em todo caso, você o possui na tabela de aparelho e se você consegue determinar os registros de um determinado aparelho você consegue determinar o histórico e consegue recuperar os registros das tabelas de manutenção, leitura e Tempo Consumível.

     

    O que o DELETE faz é deletar os registros que estejam nas tabelas de manutenção, leitura e tempoconsumivel cujo o histórico esteja na tabela de históricos cujo os registros da tabela de histórico sejam identificados através da variável @codaparelho.

     

    Se esse é o entendimento, o DELETE irá funcionar. Se esse não é o entendimento, por favor corrija-me.

     

    [ ]s,

     

    Gustavo

    quarta-feira, 17 de dezembro de 2008 15:45
  •  

    Fiz o teste aqui, mas eu retirei a comparação do CodAparelho e coloquei o CodHistorico, pois o sql server nem deixa salvar com o CodAparelho, já que as tabelas leituras, manutenção, etc, não tem o campo CodAparelho.

    Minha modelagem está da seguinte maneira:

    Tabela Aparelho(CodAparelho) 

     1 ---> n  Tabela Historico(CodAparelho, CodHistorico)

                 1 ---> n Tabela Leitura(CodHistorico), Tabela Manutenção(CodHistorico)...

     

    Mas eu fiz a procedure da seguinte maneira:

    Code Snippet

    DELETE FROM TB_Leitura

    FROM TB_Leitura AS L

    INNER JOIN TB_Historico AS H ON L.CodHistorico = H.CodHistorico

    WHERE H.CodAparelho = @CodAparelho

     

    DELETE FROM TB_Manutencao

    FROM TB_Manutencao AS M

    INNER JOIN TB_Historico AS H ON M.CodHistorico = H.CodHistorico

    WHERE H.CodAparelho = @CodAparelho

     

    DELETE FROM TB_Historico WHERE CodAparelho = @CodAparelho

    DELETE FROM TB_Aparelho WHERE CodAparelho = @CodAparelho

     

    A princípio parace que funcionou, mas eu só tinha um equipamento para excluir, tenho que testar com mais de um.

     

    E quanto ao DELETE, minha dúvida é porque eu nunca tinha visto essa sintaxe com dois FROM:

    DELETE "FROM" TB_Manutencao

    "FROM" TB_Manutencao AS M

     

    Eu imaginava que você tinha digitado errado. Não sabia que podia fazer assim.

     

    quarta-feira, 17 de dezembro de 2008 16:47
  • Olá Marcelo,

     

    A princípio o comando é esse mesmo. Uma vez que você tenha escrito tudo em um único comando, a exclusão será feita mais rapidamente do que fazer um loop para excluir registro a registro.

     

    Essa sintaxe não é muito comum mesmo. Na verdade ela é proprietária do SQL Server, mas normalmente você também poderia utilizar uma subquery. Ex:

    Code Snippet

    DELETE FROM TB_Leitura

    WHERE CodHistorico IN

    (SELECT CodHistorico FROM TB_Historico

    WHERE CodAparelho = @CodAparelho)

     

    DELETE FROM TB_Manutencao

    WHERE CodHistorico IN

    (SELECT CodHistorico FROM TB_Historico

    WHERE CodAparelho = @CodAparelho)

     

    DELETE FROM TB_Historico WHERE CodAparelho = @CodAparelho

    DELETE FROM TB_Aparelho WHERE CodAparelho = @CodAparelho

     

    [ ]s,

     

    Gustavo

    quarta-feira, 17 de dezembro de 2008 16:58
  • Bem, no fim aprendi mais uma sintaxe!

    Muito obrigado pela ajuda!

     

     

    quarta-feira, 17 de dezembro de 2008 17:34