Um caso de recursividade

Aproveitando um recente trabalho realizado, gostaria de descrever um relatório que rapidamente desenvolvi em uma base de dados SQL Server. A situação era a seguinte: eu havia importado a estrutura de um produto, e precisava exibir esta estrutura na forma de um relatório. Primeiro, vamos entender o termo “estrutura de um produto”. A empresa em questão é uma fábrica de impressoras de plotagem (1,8m a 3,2m). A ficha técnica da impressora é composta por vários níveis de componentes. Vou ilustrar uma composição:

 

1. Impressora XPTO modelo 1
1. Conjunto eletrônica XPTO 1804
1. Tomada de embutir 3 pinos
2. Cinta nylon média
3. Fusível pequeno 10A
4. Sensor de temperatura
5. Ventilador – secagem
1. Espaguete termo retrátil
2. Ventilador 120x120x38 c/ rosca
3. Conector modular miniatura H89F09
6. Blower – vácuo
1. Blower ( Siroco ) AD xxxx 24v
2. Conector modular miniatura H90F23
7. Aquecimento – cabeças
1. Resistor Fio 120R x 10W
2. Cabo paralelo 0,20mm
3. Apoio de silicone 13mm x 4mm
4. Terminal KK pequeno importado

 

O relatório precisa aparecer formatado como está acima (indentado nos níveis). Porém, a quantidade de níveis é indefinida. Desta forma, utilizei o conceito de “recursividade”.

Definições de recursividade na internet:

• Recursão é um método de programação no qual uma função pode chamar a si mesma. O termo é usado de maneira mais geral para descrever o processo de repetição de um objeto de um jeito similar ao que já fora mostrado. ...
  pt.wikipedia.org/wiki/Recursividade
  
• A recursividade na programação de computadores envolve a definição de uma função que pode invocar a si própria. Um exemplo de aplicação da recursividade pode ser encontrado nos analisadores gramaticais recursivos para linguagens de programação. ...
  pt.wikipedia.org/wiki/Recursividade_(ciência_da_computação)
  
• iteratividade; propriedade de formar infinitos elementos à partir de finitos aplicando-se regras lógicas ou matemáticas
  pt.wiktionary.org/wiki/Recursividade
  
• que chama a si mesmo (programa ou função)
  pt.wiktionary.org/wiki/Recursivo
  

Para desenvolver este relatório, eu utilizei este conceito de recursividade. Eu criei um procedimento insere os componentes, e para cada componente que está inserindo, chama a si mesmo para repetir a explosão, independente da quantidade de níveis que houver na estrutura.

O primeiro código que vamos analisar é o que retorna o relatório ao usuário. Ele somente recebe os parâmetros, e chama o procedimento de explosão da estrutura, chamado spcAdhExpEst (procedimento recursivo).

Code Snippet

1.  
2. CREATE proc spcAdhExplodeProduto
3.   @cd_item char(13)
4.   
5. as
6.  
7.   /*
8.    Autor: Fabiano Cores
9.    Data : 23/12/2010
10.    Site : http://sqlburger.blogspot.com
11.    Desenvolvido para demonstrar a utilização de procedimentos
12.    recursivos no SQL Server. A reprodução deste artigo só é
13.    permitida se forem mantidos os créditos do autor.
14.    */
15.  
16.   -- Esta tabela possui papel fundamental na recursividade. Esta
17.   -- tabela irá existir durante toda a execução deste procedimento
18.   -- mesmo quando chamamos executamos um segundo procedimento.
19.   create table #retorno (
20.     cd_componente char(18) collate database_default null
21.   , qt_aplicada decimal(10,4) null
22.   , nivel smallint null
23.   , ordem int not null identity)
24.   
25.   -- Insiro o item pai, para que o usuário saiba de qual produto
26.   -- está tirando o relatório
27.   insert into #retorno (cd_componente, qt_aplicada, nivel)
28.     select @cd_item, 1, 1
29.   
30.   -- Este é o procedimento recursivo. Ele fará referencia a ele
31.   -- mesmo, enquanto preenche a tabela #retorno
32.   exec spcAdhExpEst @cd_item
33.  
34.   -- Aqui, eu pego os registros da tabela #retorno, e retorno
35.   -- ao software .NET, para exibição ao usuário.
36.   select convert(char(18), space((nivel-1) * 2) + a.cd_Componente) as 'Código'
37.        , convert(char(65), space((nivel-1) * 2) + b.descricao) as 'Descrição'
38.        , a.qt_aplicada 'Quantidade'
39.        , a.nivel -1 as 'Nível'
40.     from #retorno a, item b
41.    where a.cd_componente = b.cd_item
42.    order by a.ordem
43.  
44. --

Este é o código do procedimento recursivo. Ele insere na tabela #retorno, que foi criada pelo seu procedimento antecessor (spcAdhExplodeEstrutura), e faz referência a si mesma:

Code Snippet

1. --
2.  
3. CREATE proc spcAdhExpEst
4.   @cd_item char(13)
5.   
6. as
7.  
8.    /*
9.    Autor: Fabiano Cores
10.    Data : 23/12/2010
11.    Site : http://sqlburger.blogspot.com
12.    Desenvolvido para demonstrar a utilização de procedimentos
13.    recursivos no SQL Server. A reprodução deste artigo só é
14.    permitida se forem mantidos os créditos do autor.
15.    */
16.  
17.   declare @vcd_componente char(13)
18.   declare @vqt_aplicada decimal(19,4)
19.  
20.   -- Insere os componentes simples (não possuem estrutura abaixo)
21.   insert into #retorno (cd_componente, qt_aplicada, nivel)
22.     select a.cd_componente, a.qt_aplicada, @@nestlevel
23.       from processoitem a, item b
24.      where a.cd_item = @cd_item
25.        and a.cd_componente = b.cd_item
26.        and not exists (select 0
27.                          from processoitem c
28.                         where c.cd_item = a.cd_componente)
29.      order by b.descricao
30.  
31.   -- Aqui é montado um cursor nos itens complexos (que possuem
32.   -- estrutura). É utilizado um cursor por que para cada item
33.   -- que possui estrutura, eu vou fazer a chamada recursiva.
34.   declare c1 cursor local fast_forward read_only for
35.     select a.cd_componente, a.qt_aplicada
36.       from processoitem a, item b
37.      where a.cd_item = @cd_item
38.        and a.cd_componente = b.cd_item
39.        and     exists (select 0
40.                          from processoitem c
41.                         where c.cd_item = a.cd_componente)
42.      order by b.descricao
43.      
44.   open c1
45.   
46.   while 1 = 1
47.   begin
48.     fetch next from c1 into @vcd_componente, @vqt_aplicada
49.     if @@fetch_status <> 0 break
50.     
51.     insert into #retorno (cd_componente, qt_aplicada, nivel)
52.       select @vcd_componente, @vqt_aplicada, @@nestlevel
53.       
54.     -- Chamada recursiva (executando ele mesmo)
55.     exec spcAdhExpEst @vcd_componente
56.     
57.   end
58.   close c1
59.   deallocate c1
60.  
61. --

 

Detalhes importantes que devem ser observados:

1. A tabela temporária foi criada no primeiro procedimento. Se ela fosse criada no procedimento recursivo, daria erro, pois ele tentaria criar mais de uma vez a mesma tabela, desta forma, ela sendo criada apenas uma vez pelo procedimento pai, não haverá problemas.

2. O procedimento filho (spcAdhExpEst) consegue enxergar a tabela #retorno.

3. A variável @@nestlevel é uma variável de ambiente, que retorna o nível de aninhamento* da execução do procedimento. Isto é uma forma de saber quantas vezes o procedimento já foi disparado por ele mesmo (na primeira execução, @@nestlevel é igual a 0). Aproveitei esta variável para incluir no relatório a que nível o componente pertence, e também a utilizei como parâmetro para adicionar espaços à esquerda do código e da descrição, para melhorar visualmente a identificação dos níveis na leitura do relatório.

*Definições de aninhar na internet:

• aninhado - Quando se tem um objeto dentro do outro, como uma tabela, por exemplo , você possui um aninhamento , ou seja, um objeto funcionando como ninho para outro objeto.
  www.juliobattisti.com.br/tutoriais/keniareis/dicionarioinfo001.asp

 

 

Eu estou postando esta dica por que hoje tive uma experiência muito desagradável… um certo programador teve a capacidade de fazer 9 cursores (loops), um dentro do outro, para tratar possíveis 9 níveis do produto. Além de ser um código absurdamente mal pensado, é gigantesco, lento, péssimo de realizar manutenção, entre outros comentários que não vêem ao caso. E como padrão, quando pego código assim, tenho o hábito de selecionar tudo, apagar, refazer… e claro, compartilhar!

Triggers para iniciantes no SQL Server – Parte 2/3

Este artigo se aplica ao Microsoft SQL Server a partir da versão 2000, porém o conceito de triggers se aplica à maioria dos sistemas gerenciadores de bancos de dados disponíveis, porém podem ocorrer diferença de sintaxe.

Olá caros sqlnautas, sejam bem vindos à segunda parte da saga sobre triggers (gatilhos) no SQL Server. Para quem deseja ler a primeira parte, segue aqui o link para a primeira parte:

Triggers para iniciantes no SQL Server - Parte 1/3

PARA COMEÇAR

Para esta segunda parte do nosso tutorial, vamos partir para a parte prática. Para começar, abra uma nova janela de Query no seu SQL Management Studio para criarmos duas tabelas, com estruturas idênticas, porém com diferentes propósitos, para que possamos executar nossos testes, e entender o comportamento das triggers em todas as operações.

Code Snippet

1. create table exTrigger1 (
2.   codigo     char(13) not null
3. , quantidade smallint not null
4.     , valor      moneynot null
5.     , total      moneynot null )
6.  
7. GO
8.  
9. create table exTrigger2 (
10.   codigo     char(13) not null
11. , quantidade smallint not null
12.     , valor      moneynot null
13.     , total      moneynot null )
14.  
15. GO
16.  
17.  
18. -- Execute estes inserts na tabela exTrigger2, vamos usar
19. -- mais pra frente.
20.  
21. insert into exTrigger2 (codigo, quantidade, valor, total)
22.   select '1', 1, 1, 1
23.  
24. insert into exTrigger2 (codigo, quantidade, valor, total)
25.   select '2', 2, 2, 4
26.  
27. insert into exTrigger2 (codigo, quantidade, valor, total)
28.   select '4', 3, 3, 9

EXPLORANDO AS TABELAS INSERTED E DELETED

As tabelas INSERTED e DELETED, como comentado no post anterior, são tabelas “virtuais”, que existem somente durante a execução de uma trigger. Na tabela INSERTED temos os registros inseridos na tabela, ou os novos valores atribuídos aos registros alterados durante um update. Na tabela DELETED, o conceito é o mesmo, porém com os registros excluídos, ou no caso do update, os valores anteriores ao update. A trigger que estamos criando, será disparada sob qualquer operação de manipulação de dados que houver na tabela (INSERT, DELETE, UPDATE). Como foi falado anteriormente, você pode ter uma trigger configurada para disparar somente no evento INSERT ou somente no DELETE, mas aqui vamos colocar todas as operações para explorá-las melhor.

Lembrando que sistematicamente, todo processo executado em um sistema, geralmente possui um fluxo de retrocesso, estorno, ou algo similar para esta mesma operação. É neste momento que se deve tomar muito cuidado ao se criar trigger. Se você criou uma trigger para quando inserisse na tabela, movimentasse estoque, você também deve ter uma trigger que ao excluir o registro dessa tabela, estorne esta movimentação, caso contrário seu banco de dados não ficará consistente, e assim por diante.

A trigger que vamos criar possui o propósito específico de mostrar o comportamento das tabelas INSERTED e DELETED. Quando realizarmos operações na tabela exTrigger1, na tela de resultado da Query irá aparecer a contagem de registros incluidos, excluídos e alterados.

Vamos então criar nossa trigger na tabela exTrigger1:

Code Snippet

1.  
2. create trigger tgExTrigger1 on exTrigger1
3. for insert, delete, update
4. as
5. begin
6.  
7.  
8.    /*
9.    Autor: Fabiano Cores
10.    Data : 20/12/2010
11.    Site : http://sqlburger.blogspot.com
12.    Desenvolvido para demonstrar a utilização de
13.    triggers no SQL Server. A reprodução deste
14.    artigo só é permitida se forem mantidos os
15.    créditos do autor.
16.    */
17.  
18.  
19.   declare @excluidos int
20.   declare @incluidos int
21.   declare @alterados int
22.   declare @mensagem  varchar(100)
23.    
24.   -- Quantidade de registros excluidos (delete)
25.   select @excluidos = count(*)
26.     from deleted
27.    where codigo not in (select codigo
28.                           from inserted)
29.  
30.   -- Quantidade de registros incluídos (insert)
31.   select @incluidos = count(*)
32.     from inserted
33.    where codigo not in (select codigo
34.                           from deleted)
35.  
36.   -- Quantidade de registros alterados (update)
37.   select @alterados = count(*)
38.     from inserted, deleted
39.    where inserted.codigo = deleted.codigo
40.     
41.   -- Monto a string de mensagem
42.   select @mensagem =
43.          'Incluídos: ' + rtrim(@incluidos) + char(13) + char(10)
44.        + 'Excluídos: ' + rtrim(@excluidos) + char(13) + char(10)
45.        + 'Alterados: ' + rtrim(@alterados)
46.  
47.   -- Exibo na tela do Query Result os calculos.
48.   print @mensagem
49.  
50. end
51. --

EXECUTANDO OS TESTES

Agora que toda a estrutura de objetos está criada, vamos iniciar os nossos testes. Ao executar cada script, observe a estrutura do código da trigger e assimile ambos os códigos ao mesmo tempo, com calma, e irá entender com facilidade.

O primeiro teste que vamos realizar é um insert na tabela exTrigger1 (que possui a trigger). O script e a figura com o resultado seguem abaixo:

Code Snippet

1. insert into exTrigger1 (codigo, quantidade, valor, total)
2.   select 'A', 30, 10, 300

Agora, vamos fazer um update na tabela:

Code Snippet

1. update exTrigger1
2.    set quantidade = 20
3.      , total = 200
4. where codigo = 'A'

E para finalizar a primeira parte dos exemplos, vamos excluir o registro que inserimos, e ver o resultado que a trigger irá imprimir na tela.

Code Snippet

1.  
2. delete from exTrigger1
3. where codigo = 'A'
4. --

Agora, vou mostrar o por que criar uma trigger exige que sejam observados vários aspectos. Vamos agora utilizar os dados da tabela exTrigger2, que possui 3 registros que inserimos para teste. Vamos inserir dados em massa na tabela exTrigger1 (a que possui a trigger). Mas o que é inserir dados em massa? É quando inserimos em uma tabela o resultado de um select, que pode retornar mais de um registro. Vamos observar o que acontece:

Code Snippet

1.  
2. insert into exTrigger1 (codigo, quantidade, valor, total)
3. select codigo, quantidade, valor, total
4.    from exTrigger2
5. --

Observe atentamente que inserimos 3 registros na tabela, porém a trigger foi disparada uma única vez. Muitas vezes, programadores amadores nem conhecem esta característica, ou as vezes o programador “acha” que só ocorrerão operações na tabela que incluam, alterem ou excluam apenas um registro, e é nesse momento que a trigger pode se tornar um problema. Na minha opinião, triggers são perigosas, e devem ser criadas a partir de muito planejamento e documentação. É importante lembrar que podem haver intervensões manuais na tabela, etc.

Um exemplo de como você pode seguramente tratar todos os registros de uma tabela, é utilizando um cursor:

Code Snippet

1.  
2. CREATE trigger tgExemplo on TabExemplo
3. for insert
4. as
5. begin
6.  
7.   declare @vcol1 int
8.   declare @vcol2 int
9.   
10.   declare cp1 cursor local fast_forward read_only for
11.     select col1, col2, ...
12.       from inserted
13.       
14.   open cp1
15.   
16.   while 1 = 1
17.   begin
18.     fetch next from cp1 into @vcol1, @vcol2
19.     if @@fetch_status <> 0 break
20.     
21.     -- sql_statements...
22.         
23.   end
24.   close cp1
25.   deallocate cp1
26.  
27. end
28. --

O único conselho que insisto em descrever é que se tome cuidado ao criar uma trigger. Procure projetar todas as possibilidades de operações que possam ocorrer na tabela. Também é importante que, se você fez uma trigger que faz uma operação, provavelmente você tenha que fazer uma trigger inversa, que desfaça a operação. Exemplo: você tem uma trigger na tabela de apontamento de ordem de produção, que ao gravar o apontamento, a trigger realiza a baixa de estoque dos materiais da ordem de produção. Lembre-se que se você excluir este apontamento, você deverá fazer uma trigger de delete, que irá estornar o movimento do estoque, e assim por diante. E é aí que se encontra a importância de tratar multiplos registros inseridos ou excluídos.

No próximo capítulo, vamos tratar abordagens mais avançadas nas triggers, como desabilitalas temporariamente, definição de sequencia de disparo (multiplas triggers em uma única tabela), controle de redundância (a trigger modificar dados em sua própria tabela, disparando de forma cíclica a trigger), e outros conceitos muito úteis.

Até a próxima!

Triggers para iniciantes no SQL Server – Parte 1/3

Este artigo se aplica ao Microsoft SQL Server a partir da versão 2000, porém o conceito de triggers se aplica à maioria dos sistemas gerenciadores de bancos de dados do mercado.

Olá caros sqlnautas. Neste artigo, trarei a vocês algumas dicas importantes sobre TRIGGERS no SQL Server. Este recurso nos possibilita disparar eventos programados ao se inserir, excluir ou alterar dados em uma tabela. Um exemplo de aplicação, que vamos abordar neste tópico, será a criação de uma trigger em uma tabela dos itens de uma nota fiscal. Quando os itens são gravados nesta tabela, automaticamente a trigger irá movimentar o estoque. Lembrando que nesta primeira parte, os scripts de exemplo foram desenvolvidos apenas para ilustrar. Na segunda parte, fornecerei scripts completos para realizar as simulações em seu próprio SQL Server.

CONHECENDO A ESTRUTURA DE UMA TRIGGER

Uma trigger (gatilho) é um bloco de código T-SQL (como um procedimento) que é executado no momento em que ocorre um INSERT, UPDATE e/ou DELETE na tabela onde foi criada.

Vamos então começar visualizando uma trigger. Para abrir uma trigger, podemos faze-lo de duas formas:

1. Localizar a trigger através do Object Explorer do Microsoft SQL Server Management Studio (imagem abaixo), clicar com o botão direito sobre ela e clicar em MODIFY (modificar).

 

2. Utilizando o comando sp_helptext

 
Vamos agora rapidamente olhar um código documentado de uma trigger completa (baixa estoque ao inserir itens na nota):

Code Snippet

1. CREATE trigger tgiNotaItem on NotaItem
2. for insert
3. as
4. begin
5.   /*
6.    
7.   Autor: Fabiano Cores
8.   Data : 06/12/2010
9.   Site : http://sqlburger.blogspot.com
10.    
11.   Desenvolvido para demonstrar a utilização de  
12.   triggers no SQL Server. A reprodução deste  
13.   artigo só é permitida se forem mantidos os  
14.   créditos do autor.
15.    
16.   */
17.  
18.   /* A tabela inserted existe "virtualmente" durante     
19.      a execução de uma trigger. Ela possui exatamente    
20.      a mesma estrutura da tabela da trigger, e contem    
21.      todos os registros inseridos no evento INSERT,      
22.      que disparou a trigger. */
23.  
24.   declare @vcd_empresa   smallint
25.   declare @vnr_serie     char(3)
26.   declare @vnr_nota      int
27.   declare @vcd_item      char(13)
28.   declare @vqt_faturada  decimal(10,4)
29.   declare @vvl_unitario  money
30.    
31.   declare cur_NF_Item cursor
32.    local fast_forward read_only for
33.     select cd_empresa
34.          , nr_serie
35.          , nr_nota
36.          , cd_item
37.          , qt_faturada
38.          , vl_unitario
39.       from inserted
40.        
41.   open cur_NF_Item
42.    
43.   while 1 = 1
44.   begin
45.     fetch next from cur_NF_Item into @vcd_empresa
46.                                    , @vnr_serie
47.                                    , @vnr_nota
48.                                    , @vcd_item
49.                                    , @vqt_faturada
50.                                    , @vvl_unitario
51.     if @@fetch_status <> 0 break
52.  
53.  
54.     /* Para cada item que foi inserido, executo o
55.        procedimento que movimenta estoque (só para
56.        exemplificar) */
57.     exec spcMovimentaEstoque @vcd_empresa
58.                            , @vnr_serie
59.                            , @vnr_nota
60.                            , @vcd_item
61.                            , @vqt_faturada
62.                            , @vvl_unitario
63.      
64.      
65.   end
66.   close cur_NF_Item
67.   deallocate cur_NF_Item
68.    
69. end

CABEÇALHO DE UMA TRIGGER

• O primeiro parâmetro é o NOME (tgiNotaItem) da trigger, forma como o objeto será identificado no banco de dados. Quando uma trigger é criada, é gerado um registro na tabela SYSOBJECTS. Para se atribuir o nome da trigger, tenho como padrão colocar tgiTabela (trigger de insert), tgdTabela (trigger de delete), tguTabela (trigger de update), ou somente tgTabela, quando ambas as situações são contempladas no código.
  
• O segundo parâmetro é o nome da tabela que será vinculada a esta trigger (no exemplo abaixo, estamos colocando um gatilho na tabela NotaItem).
  
• O terceiro parâmetro (“for insert”) determina o tipo de trigger, que vamos entender melhor cada uma adiante.
  

Code Snippet

1. create trigger trigger_name
2. on { table_name }
3. { FOR / AFTER | INSTEAD OF }
4. { [INSERT] [,] [DELETE] [,] [UPDATE] }
5. as
6. begin
7.  
8.    sql_statement [ ...n ]
9.  
10. end

Tipos de triggers

Existem 2 tipos de triggers no SQL Server (e na maioria dos SGBD do mercado).

AFTER: A trigger somente é executada após a gravação dos dados na tabela (padrão quando cria trigger)
INSTEAD OF: A trigger será disparada ANTES do registro ser modificado na tabela.

O tipo AFTER é o mais comum de ser utilizado. O tipo INSTEAD OF tem aplicações mais específicas. Os casos mais comuns em que utilizei este tipo de trigger foi para criar bloqueios em sistema que não detenho o código fonte (exemplo: lançamentos contábeis em período fechado, não permite gravar). Mais adiante falaremos mais sobre este tipo.

Exemplos:

AFTER

Code Snippet

1. CREATE trigger tgiNotaItem on NotaItem
2. FOR { insert | delete | update }
3. as
4.  
5. OU
6.  
7. CREATE trigger tgiNotaItem on NotaItem
8. AFTER { insert | delete | update }
9. as

INSTEAD OF

Code Snippet

1. CREATE trigger tgiNotaItem on NotaItem
2. INSTEAD OF { insert | delete | update}
3. as

Eventos de disparo de uma trigger

Uma trigger é disparada quando um evento de INSERT, DELETE ou UPDATE ocorre em uma tabela. Para isso, é necessário informar ao SQL que tipo de evento deseja tratar, que pode INSERT, DELETE ou UPDATE.

Exemplos:

Code Snippet

1.  
2. -- Trigger que será executada ao inserir um item no sistema     
3. CREATE trigger tgiNotaItem on NotaItem      
4. FOR insert
5. as
6.  
7.  
8.   sql_statement [ ...n ]
9.  
10.   
11. -- Trigger que será executada ao excluir um item no sistema
12. CREATE trigger tgdNotaItem on NotaItem
13. FOR delete
14. as
15.  
16.  
17.   sql_statement [ ...n ]
18.  
19.   
20. -- Trigger que será executada em qualquer operação de manipulação     
21. -- de dados na tabela (insert, delete ou update)
22. CREATE trigger tgNotaItem on NotaItem
23. FOR insert, delete, update
24. as
25.  
26.  
27.   sql_statement [ ...n ]

TABELAS “DE AMBIENTE” EXISTENTES NA EXECUÇÃO DE UMA TRIGGER

Ao desenvolver uma trigger, você precisa ter acesso aos dados EM ATUALIZAÇÃO (no caso de insert, quais dados estão sendo inseridos, para exclusão quais estão sendo excluídos, e assim por diante). Para isso, chamo-as de “tabelas de ambiente”, que só existem no momento em que uma trigger está sendo executada.

Existe duas tabelas, chamadas INSERTED (registros incluídos ou alterados – novos valores) e DELETED (registros excluídos ou alterados – valores anteriores). Estas tabelas “virtuais” possuem exatamente a mesma estrutura da tabela em que a trigger foi criada. Vamos separar somente o trecho do código que busca dados desta tabela.

Exemplo:

Code Snippet

1. CREATE trigger tgiNotaItem on NotaItem
2. for insert
3. as
4. begin
5.  
6.   ...
7.  
8.   declare cur_NF_Item cursor
9.    local fast_forward read_only for
10.     select cd_empresa
11.          , nr_serie
12.          , nr_nota
13.          , cd_item
14.          , qt_faturada
15.          , vl_unitario
16.       from inserted
17.        
18.   open cur_NF_Item
19.    
20.   ...
21.    
22. end

Observe que estou buscando dados de uma tabela INSERTED, porém se olhar esta tabela no Object Explorer, perceberá que ela não existe. Ela somente vai existir durante a execução de uma trigger. Os updates são ainda mais interessantes. Toda vez que um UPDATE é feito em uma tabela, durante a execução da trigger nós teremos na tabela DELETED quais eram os dados antes do update, e na INSERTED quais são os novos valores dos registros alterados.

Fique atento para o próximo capítulo da saga. Na próxima parte vamos entender mais sobre a parte prática, parâmetros e o comportamentos das triggers.

=)

SQL Dinâmico – usar código para criar código

Hoje vou demostrar um pequeno exemplo de como utilizar programação dinâmica no SQL. No exemplo aqui demonstrado, utilizo o SQL dinâmico para definir a estrutura de uma tabela temporária de acordo com os dados que desejo exibir.

Eu montei rapidamente um modelo similar a um relatório que fiz recentemente. Eu precisava retornar os fornecedores como colunas, e doze meses como linhas, para montar uma grande planilha de dados. Para poder retornar o fornecedor na coluna, montei um loop nos fornecedores que deverão entrar no relatório, e utilizei o nome fantasia como nome da coluna que receberá o valor do mês/ano específico. Dentro deste mesmo loop, já busco o valor dos títulos deste fornecedor, e atualizo a tabela temporária. Depois só retorno os resultados.

Vamos então ver o código. Tentei comentar da melhor forma possível para explicar cada passo do procedimento.

create proc spcExemploSQLDinamico as       /*        Autor: Fabiano Cores    Data : 04/12/2010    Site : http://sqlburger.blogspot.com         Desenvolvido para demonstrar a utilização de querys    dinâmicas no SQL Server. A reprodução deste artigo    só é permitida se forem mantidos os créditos do autor.      */    declare @sql            varchar(8000)     declare @vcd_cadastro   int     declare @vfantasia      varchar(20)     declare @valor          money     declare @data           smalldatetime        -- Crio a temporária que receberá modificações dinâmicas     create table #tmp (       mes tinyint null     , ano smallint null )        -- Preencho a tabela com as datas. Pego o ano atual,     -- e defino como primeiro de janeiro, para pegar o ano     -- inteiro.     select @data = rtrim(year(getdate())) + '-01-01'        set @sql = ''       while 1 = 1     begin          -- Aqui estou montando uma query dinâmica. Veja que estou       -- concatenando várias querys em uma variável VARCHAR, para       -- posterior execução.       select @sql = @sql                   + 'insert into #tmp (mes, ano) '                   + 'values ('                   + rtrim(month(@data)) + ','                   + rtrim(year(@data)) + ')'                   + char(13) + char(10)                        -- Caso tenha inserido os 12 meses do ano, saio do loop       if month(@data) = 12         break          -- A cada ciclo do loop, pulo um mês       select @data = dateadd(month, 1, @data)        end        -- Aqui executo a query montada acima.     exec (@sql)     -- limitei em um TOP 25 para não trazer muitos registros     -- O distinct é para que retorne apenas uma vez cada     -- fornecedor neste select.     declare cp1 cursor local fast_forward read_only for       select distinct top 25 a.cd_cadastro            , a.fantasia         from cadastro a            , fornecedor b            , titulofor c        where a.cd_cadastro         = b.cd_cadastro          and a.situacao            = 'A'          and a.cd_cadastro         = c.cd_cadastro          and year(c.dt_lancamento) = year(getdate())          and c.situacao           in ('A', 'L')        order by a.fantasia            /*    As tabelas aqui utilizadas e parâmetros devem ser     desconsiderados. Adapte este procedimento ao seu    banco de dados.    */       open cp1         while 1 = 1     begin       fetch next from cp1 into @vcd_cadastro                              , @vfantasia       if @@fetch_status <> 0 break             -- Adiciono colunas na tabela com o nome do fornecedor       select @sql = 'alter table #tmp add ['                   + rtrim(ltrim(@vfantasia))                                         + '] money null'             exec (@sql)             select @data = rtrim(year(getdate())) + '-01-01'         while 1 = 1       begin               -- Sempre inicialize uma variável dentro de um loop.         set @valor = 0;                 select @valor = sum(vl_titulo)           from titulofor          where situacao            in ('A', 'L')            and cd_cadastro          = @vcd_cadastro            and month(dt_lancamento) = month(@data)            and year(dt_lancamento)  = year(@data)               select @valor = isnull(@valor, 0)                 -- Para montar o update, uso novamente o nome        -- fantasia do fornecedor para saber qual o        -- nome da coluna que preciso atualizar o valor.         select @sql = 'update #tmp '                     + '   set ['                     + rtrim(ltrim(@vfantasia)) + '] = '                     + rtrim(@valor)                     + ' where mes = ' + rtrim(month(@data))                     + '   and ano = ' + rtrim(year(@data))                             exec (@sql)                 if month(@data) = 12           break               select @data = dateadd(month, 1, @data)             end                 end     close cp1     deallocate cp1         select @sql = 'select * from #tmp order by ano, mes'         exec (@sql)

O resultado é um relatório estruturado dinamicamente:

Afinal de contas, maiúsculo ou minúsculo?

Este é apenas um pequeno post “desabafo”. Muitos programadores me questionam por que eu programo em SQL usando somente letras minúsculas. Vamos fazer uma pequena comparação de um código do jeito que eu faço, um no padrão que os programadores dizem ser o “correto”, e mais para o final, um terceiro procedimento, que é código interno do SQL.

Maneira como eu programo	Padrão considerado “correto”

Em primeiro lugar, minha influência em programação veio de meu pai, que sempre programou em C. Uma coisa inclusive que detesto no C++/C# é o fato de ser case sensitive. Isso é irritante! Hoje com as ferramentas atuais (visual studio por exemplo) isso “teoricamente” não é mais problema.

Bom, eu honestamente, acho uma grande besteira ficar ligando e desligando o CAPS LOCK enquanto se programa. Se o SQL tem o recurso de identificar todas as palavras do código por cor, pra você identificar comandos, strings, operadores, não vejo sentido nenhum nessa formatação. E pessoalmente, acho muito mais agradável e bonito o código sem letras maiúsculas, pra mim só poluem a tela.

Mas se ainda insistir em achar que eu estou errado, vamos dar uma olhadinha então nos códigos de programadores que desenvolveram o SQL! Sim, podemos ver esses códigos! Vamos pegar aqui, duas funções muito comuns do SQL (sp_help e sp_spaceused), e ver como é o código delas (basta usar sp_helptext para visualizar o código do procedimento):

exec sp_helptext 'sp_help'

exec sp_helptext 'sp_spaceused'

Se quiser ver no seu próprio SQL, é só executar “ sp_helptext 'sp_spaceused' ” ou “ sp_helptext 'sp_help' ” e você vai ver como foram feitos o código T-SQL dos procedimentos internos mais utilizados do SQL Server.

:D

Calculando o tamanho de cada tabela no SQL Server

Hoje vou descrever uma solução que adotei durante uma visita a um cliente que estava com problemas de tamanho e performance no banco de dados. Ele utiliza Microsoft SQL Server 2005 para armazenar os dados do seu aplicativo. O problema é que precisávamos detectar tabelas que estavam grandes demais, e estavam absorvendo muitos recursos de armazenamento (tanto no servidor como no backup) e também recursos de processamento e memória. Só não confunda espaço do banco de dados com arquivo de LOG, que abordarei em um próximo artigo.

O trabalho teve várias etapas, porém vou descrever as que trouxeram maior resultado efetivo. Eu dividi o trabalho em duas partes. Primeiro identifiquei as tabelas que ocupando mais espaço. Depois, remodelei as tabelas de forma a utilizarem o espaço do banco de dados de forma mais adequada. Muitas vezes, um sistema utilizado em diversos segmentos, pode acabar tendo suas dimensões desproporcionais. Vou dar um exemplo muito simples. Você tem um sistema ERP que foi desenhado para indústrias, porém o implanta em uma distribuidora que emite 300 notas fiscais por dia, diferente do que havia sido previsto quando o sistema foi pensado para uma fábrica de médio porte. Neste exemplo, se as tabelas de notas fiscais conter campos mal dimensionados, ou relacionamentos redundantes, podem acarretar problemas de espaço e performance a longo prazo.

Neste artigo, vou abordar exclusivamente o cálculo do tamanho das tabelas. As modificações estruturais de tabelas, e demais otimizações, não vou abordar neste momento. O primeiro código que vamos analisar, é uma rotina que criei que relaciona todas as tabelas no banco de dados, calcula individualmente o espaço utilizado por cada uma e exibe no final um relatório ordenado pelas tabelas que mais utilizam espaço do banco.

sys.sp_spaceused

Este procedimento interno do SQL Server retorna informações sobre o tamanho do banco de dados, e também especificamente de tabelas passadas como parâmetro.

Code Snippet

1.             exec sp_spaceused-- retorna informações do banco
2.  
3. OU
4.  
5. exec sp_spaceused 'cadastro' -- retorna informações da tabela

 

RESERVED: Espaço total reservado pelo banco de dados “para armazenamento de dados”. Por mais que sejam excluídos registros do banco, ele nunca reduz o seu tamanho (a não ser que seja feito o Shrink Database, que irá devolver ao disco o espaço UNUSED do banco).

DATA: Quantidade em KB de dados no banco de dados

INDEX_SIZE: Tamanho utilizado somente para a indexação de valores. Muitos índices em um banco podem elevar consideravelmente o tamanho do banco, é necessário estar atento.

UNUSED: Espaço que foi alocado ao banco de dados, porém não está sendo utilizado. Este espaço pode ser retomado ao disco através da ferramenta Shrink Database.


Quando passamos o nome de uma tabela como parâmetro, obtemos a mesma informação, porém os valores informados especificamente da tabela, com a diferença que temos uma coluna a mais, chamada ROWS, que retorna o número de linhas nesta tabela:

 

Utilizando esta ferramenta do sistema, criei o seguinte procedimento:

Code Snippet

1. create proc sp_espacotabela
2. as
3.  
4. /*
5.   Fabiano Cores 02.12.2010
6.   http://sqlburger.blogspot.com
7. */
8.  
9.   declare @vname sysname
10.   declare @tmpTamTabela table (
11.     name       sysname     null
12.   , rows       int         null
13.   , reserved   varchar(25) null
14.   , data       varchar(25) null
15.   , index_size varchar(25) null
16.   , unused     varchar(25) null )
17.  
18.   declare cp1 cursor local fast_forward read_only for
19.     select name
20.       from sysobjects
21.      where type = 'U'
22.      order by name
23.  
24.   open cp1
25.  
26.   while 1 = 1
27.   begin
28.     fetch next from cp1 into @vname
29.     if @@fetch_status <> 0 break
30.  
31.     insert into @tmpTamTabela (name, rows, reserved
32.                              , data, index_size, unused)
33.       exec sp_spaceused @vname
34.  
35.   end
36.   close cp1
37.   deallocate cp1
38.  
39.   select name as 'Nome'
40.        , rows as 'Linhas'
41.        , convert(int, replace(reserved, ' KB','')) as 'Tamanho total'
42.        , convert(int, replace(data, ' KB',''))as 'Dados'
43.        , convert(int, replace(index_size, ' KB',''))as 'Index'
44.        , convert(int, replace(unused, ' KB',''))as 'Não utilizado'
45.     from @tmpTamTabela
46.    order by convert(int, replace(reserved, ' KB','')) desc

O código acima já está preparado para ser salvo no banco de dados, através do nome sp_espacotabela. Vamos ver agora sua execução, e o resultado apresentado:

 

O resultado é um relatório que retorna todas as tabelas, e os respectivos valores de consumo de tamanho em disco, conforme explicado acima. Você pode exportar esta lista para o excel, e fazer várias visões, como analisar índices x quantidade de linhas, ordenar por espaço não utilizado, etc). Observe que pode ser modificado o último SELECT do procedimento, para ordenar por outro valor. Eu optei em ordenar pelo tamanho total da tabela (RESERVED), que é o que importa nesta análise.

Após visualizar o relatório, pegamos as 10 tabelas mais inchadas do banco (de um banco com aproximadamente 2500 tabelas). Geralmente são as tabelas de LOG, notas fiscais, cupons, etc. Observamos a presença de campos tipo TEXT desnecessários (para armazenar média de 80 a 180 caracteres). Mudamos para CHAR(250), e assim por diante. Com uma pequena modificação como esta, chegamos a otimizar 3GB de uma única tabela (as imagens utilizadas aqui são somente para exemplificar).

Outras tabelas, como notas fiscais e lançamentos financeiros, fizemos uma espécie de datawarehouse, deixando na base de dados de trabalho 3 anos de histórico, os demais anos foram separados em uma base onde os relatórios consolidam estes bancos. A transição dos dados é feita automaticamente a partir de uma JOB, sem a necessidade de intervenção do usuário. O backup do período anterior fica intacto, armazenado em mídia separada. O backup feito diariamente passou a ser dos últimos 3 anos.

Todas estas modificações trouxeram grandes benefícios, como otimização dos backups, mais velocidade em processos críticos do sistema (faturamento, log), banco de dados mais enxuto, sem alocar grandes espaços desnecessários, e assim por diante.

Resultados

No final, aplicando as técnicas aqui descritas, e outras manutenções, o banco de dados de produção, que estava com 13,5GB de tamanho, ficou com modestos 1,8GB. O backup, que é totalmente automatizado, estava levando mais de 5 horas para ser realizado, pois o banco compactado ficava com 3,0GB. Levava 1 hora para compactar, e 4 horas para transmitir. E se houvessem quedas de internet durante a transmissão, esta tinha que ser feita manualmente. Hoje, o arquivo de backup compactado está com 122mb, leva 2 minutos para ser gerado e 20 minutos para ser transmitido.