23 de março de 2012

Trabalhando com rotinas T-SQL no SQL Server

O SQL (Structured Query Language) é muito mas que apenas uma linguagem para manutenções num banco de dados. Ela não se restringe às quatro operações básicas (INSERT, DELETE, UPDATE e SELECT) ou àquelas funções para gerenciar tabelas ou transações. O SQL Server da Microsoft, por exemplo, implementa uma extensão do padrão SQL chamada T-SQL (Transact SQL) que permite construir funções complexas. Elas combinam queries com elementos típicos das linguagens de programação comuns, fornecendo uma sintaxe que inclui variáveis, loops e controle de fluxo.

Com essa linguagem, você pode construir Triggers, Stored Procedures, Functions ou simplesmente executar comandos pelo SQL Server Management Studio (função antigamente disponível no Query Analyser). Neste post, vou usar essa ferramenta para mostrar alguns dos recursos do T-SQL.

Para montarmos o exemplo básico, considere que vamos gerar uma lista de preços baseada nas informações contidas numa tabela chamada LISTAPR, criada de acordo com o comando a seguir:
-- Em SQL Server
create table LISTAPR
(
ID int not null,
PRODUTO varchar(25) null,
DESCRICAO varchar(255) null,
PRECO numeric(15,4) null,
DATA_VLD smalldatetime null
)

Como toda linguagem, o T-SQL possui comandos de declaração de variáveis para armazenar valores ao longo do programa. Basicamente, os tipos de dados permitidos são os mesmos que são válidos para criação de campos num tabela, com exceção dos tipos que representam BLOBs (text, ntext e image). A declaração em si é feita com a palavra chave DECLARE seguida de um arroba (@) acompanhando o nome da variável e, para finalizar, o tipo de dado que essa variável será capaz de armazenar. Exemplos:
DECLARE @produto varchar(25)
DECLARE @descricao varchar(255)
DECLARE @preco numeric(15,4)
DECLARE @linha varchar(300)
DECLARE @nro_regs int

SELECT @nro_regs = 0

A forma como os valores são recuperados do banco de dados nos obriga a declarar variáveis para os campos que pretendemos manipular. Assim, no trecho acima eu declarei uma variável para cada campo que será impresso. Como se vê também, o comando SELECT pode ser usado para atribuir manualmente valores para as variáveis.

Há um tipo especial de variável para ser usado na obtenção de um result set (lista de registros). O nome desse tipo é CURSOR e é através dele que estabelecemos quais dados serão recuperados do banco, qual o tipo de navegação será permitida nos registros (apenas avançar ou ir pra frente e pra trás) e também se será possível atualizar os dados. Para o cenário proposto neste post, precisaremos de uma coleção read-only que contenha todos os registros da tabela LISTAPR que estejam dentro da validade. A declaração abaixo atende esses requisitos:
DECLARE lista CURSOR SCROLL FOR
SELECT produto, descricao, preco
FROM LISTAPR
WHERE DATA_VLD > GETDATE() OR DATA_VLD IS NULL

O tipo da navegação é irrelevante aqui mas eu inclui a palavra SCROLL na declaração, possibilitando a navegação pelos registros em qualquer direção que queiramos. Outro detalhe é que eu estipulei quais campos eu quero recuperar. Isso é uma boa prática pois, conforme veremos adiante, deixa claro quais campos corresponderão a quais variáveis.

Agora, podemos abrir o cursor criado, navegar pela lista de registros obtidos, processar os dados conforme necessário e, finalmente, encerrar o cursor. O quadro abaixo traz o código com os passos citados:
OPEN lista
-- Primeiro registro
FETCH FIRST FROM lista INTO @produto, @descricao, @preco

WHILE @@FETCH_STATUS = 0
BEGIN
-- O processamento do registro atual será feito aqui

-- Próximo registro
FETCH NEXT FROM lista INTO @produto, @descricao, @preco
END

CLOSE lista
DEALLOCATE lista

Vamos destrinchar esse código. O comando OPEN apenas abre o cursor, submetendo a query ao banco de dados. O comando FETCH FIRST posiciona no primeiro registro e transfere para as variáveis os valores obtidos. Veja que a ordem das variáveis na cláusula INTO segue a mesma ordem estabelecida na cláusula SELECT.

A instrução WHILE é um laço tradicional, repetindo os comandos entre o BEGIN e o END enquanto o valor da expressão após o WHILE for verdadeira. Neste caso, @@FETCH_STATUS é uma variável nativa do T-SQL. Ela é afetada pela execução de comandos FETCH, assumindo valor 0 (zero) quando há um registro válido posicionado. Com isso, o comando FETCH NEXT, é executado enquanto ainda houver registros a recuperar no cursor.

Para encerrar, o cursor é fechado com o comando CLOSE e os recursos usados por ele são devolvidos ao sistema através do comando DEALLOCATE. Variáveis do tipo CURSOR são as únicas que requerem a desalocação explícita.

Ficou faltando a parte do processamento interno do laço. Como planejamos simplesmente imprimir os valores das variáveis, podemos usar o comando PRINT para isso. Ele aceita um texto como parâmetro, de modo que teremos que fazer a conversão dos tipos numéricos antes de imprimir.
SELECT @nro_regs = @nro_regs + 1, @linha = ''

SELECT @linha = @linha + @produto + REPLICATE (' ', 21 - LEN (@produto))
SELECT @linha = @linha + @descricao + REPLICATE (' ', 21 - LEN (@descricao))
SELECT @linha = @linha + STR (@preco, 10, 2)

PRINT @linha

Cada chamada ao PRINT gera uma nova linha na janela de saída do Management Studio, razão pela qual concateno os valores num único texto antes de imprimí-lo. Assim, cada produto terá sua própria linha. A ginástica com o REPLICATE e o LEN é para garantir que produto e descrição apareçam sempre com a mesma quantidade de caracteres, mantendo o resultado alinhado, mais fácil de ler. O STR converte o preço em um texto para a impressão.

É claro que, dada a simplicidade do exemplo, poderíamos ter resolvido o problema apenas executando a query. Mas o objetivo aqui era mostrar a flexibilidade da linguagem já que ela permite conter cursores aninhados, trabalhar com decisões de fluxo (instrução IF), cálculos complexos e outras interações. Aqui na ABC71, nós aproveitamos essa flexibilidade do T-SQL mesclada a queries que obtêm informações sobre tabelas do banco de dados para gerar certos tipos de codigo fonte auxiliares de nosso ERP.

13 de março de 2012

Design Patterns com Delphi: Visitor - Parte II

No último post, eu apresentei o conceito do Design Pattern Visitor usando para isso um diagrama UML com um exemplo prático da aplicabilidade do padrão. O exemplo consiste na representação de um Produto Acabado composto por uma lista de recursos (as matérias primas, máquinas e instruções) usados para fabricá-lo. O objetivo é permitir aplicar ao produto operações externas cujo resultado depende da aplicação da operação em cada parte que compõe a estrutura. No exemplo, há duas operações: uma que exporta a arquitetura do produto em formato XML e outra que faz a impressão dessa estrutura.

Neste post, mostro uma sugestão de implementação em Delphi para o exemplo do Visitor. Para facilitar a referência, publico novamente o diagrama que o retrata:
Diagrama UML para o padrão Visitor
O primeiro passo é definir as classes que representam o propósito do sistema, estabelecendo as regras de negócio que constituirão o cerne dele. No nosso caso, essas classes são o recurso de produção, suas heranças e o produto acabado. O quadro abaixo mostra as declarações delas, que são bastante simples :
{ Recursos para a produção }
TWRecursoProducao=class
public
_Nome: String;

Constructor Create (ANome: String);virtual;
procedure Accept (AOper: TWOperacoesPA);virtual;
end;

TWMateriaPrima=class(TWRecursoProducao)
public
_CProd: String;
_Qtde: Double;

Constructor Create (ANome: String);override;
procedure Accept (AOper: TWOperacoesPA);override;
end;

TWMaquina=class(TWRecursoProducao)
public
_Cod: String;
_Tempo: Double;
_TempoSetup: Double;

Constructor Create (ANome: String);override;
procedure Accept (AOper: TWOperacoesPA);override;
end;

TWRoteiro=class(TWRecursoProducao)
public
_Cod: String;
_Texto: String;

Constructor Create (ANome: String);override;
procedure Accept (AOper: TWOperacoesPA);override;
end;

{ Produto Acabado }
TWProdutoAcabado=class
protected
_MatPrimas : TObjectList;
_Maquinas : TObjectList;
_Roteiro : TWRoteiro;
procedure ClearListaRec (ALista: TObjectList);

public
_Nome: String;

Constructor Create (ANome: String);
Destructor Destroy; override;

procedure ClearRecursos;
procedure AddMatPrima (ARecurso: TWRecursoProducao);
procedure AddMaquina (ARecurso: TWRecursoProducao);
procedure SetRoteiro (ARoteiro: TWRoteiro);

procedure DoOperacao (AOper: TWOperacoesPA);
end;
Veja que a classe que representa o produto acabado (TWProdutoAcabado) possui membros do tipo TObjectList e também um roteiro separado. Esses membros armazenarão os recursos necessários para fabricar o produto acabado. Neste exemplo, as classes que compõem a agregação são heranças de uma mesma classe base - a TWRecursoProducao - mas o padrão Visitor não faz essa exigência. Assim, ele ainda é aplicável mesmo quando a agregação é composta por classes que não guardam relação entre si. Então, usei variáveis separadas pra cada tipo de recurso apenas para deixar clara essa possibilidade.

Repare ainda na função Accept introduzida na classe base de recursos. É ela quem define a família de classes como Visitable, determinando que os recursos de produção que elas representam poderão ser visitados por qualquer operação externa herdada de TWOperacoesPA. Na verdade, Accept simplesmente solicitará à operação que "visite" o recurso em questão, o que fará com que a operação seja aplicada ao recurso :
procedure TWMateriaPrima.Accept (AOper: TWOperacoesPA);
begin;
inherited;
AOper.Visit (Self);
end;

procedure TWMaquina.Accept (AOper: TWOperacoesPA);
begin;
inherited;
AOper.Visit (Self);
end;

procedure TWRoteiro.Accept (AOper: TWOperacoesPA);
begin;
inherited;
AOper.Visit (Self);
end;
A seguir, podemos definir a hierarquia de classes que implementarão as operações externas nos recursos de produção. Tais classes exercerão o papel de Visitors:
TWOperacoesPA=class
public
procedure InitOper(AInfo: TObject);virtual;
procedure TermOper;virtual;

procedure Visit (pRecurso: TWMateriaPrima);overload;virtual;
procedure Visit (pRecurso: TWMaquina);overload;virtual;
procedure Visit (pRecurso: TWRoteiro);overload;virtual;
end;

TWRecursoSaveToXml=class(TWOperacoesPA)
protected
_Xml : String;
public
procedure InitOper(AInfo: TObject);override;
procedure TermOper;override;

procedure Visit (pRecurso: TWMateriaPrima);overload;override;
procedure Visit (pRecurso: TWMaquina);overload;override;
procedure Visit (pRecurso: TWRoteiro);overload;override;

function GetXML : String;
end;
A operação para imprimir o produto acabado é bastante similar à de salva para XML e, por isso, eu a omiti do quadro.

Veja que a classe TWOperacoesPA, que é base para todas as operações, possui 3 métodos Visit sobrecarregados (overload). Cada um deles trata um tipo diferente de recurso, o que, na prática, nos permite adequar o comportamento da operação. Isso quer dizer que a operação será realizada de um modo coerente com o tipo do recurso.

Um outro detalhe nessa classe é a função InitOper. Ela é desenhada para realizar procedimentos iniciais da operação, aceitando um parâmetro genérico do tipo TObject com informações que façam sentido para a operação. Por exemplo, pode representar uma impressora para a operação de Imprimir ou um objeto DOM para a exportação em formato XML.

O trecho de código abaixo mostra as funções de inicialização e encerramento da operação de exportação para XML. Também retrata a versão da função Visit sobrecarregada para os tipos de recurso "Matéria Prima" e "Máquina":
procedure TWRecursoSaveToXml.InitOper(AInfo: TObject);
var lProd: TWProdutoAcabado;
begin
inherited;

{Abre a tag raiz para o XML }
lProd := AInfo As TWProdutoAcabado;
_XML := '<?xml version="1.0" encoding="ISO-8859-1"?>' + #13#10;
_XML := _XML + '<produtoAcabado name="' + lProd._Nome + '">' + #13#10;
end;

procedure TWRecursoSaveToXml.TermOper;
begin
{ Fecha a tag XML raiz }
_XML := _XML + '</produtoAcabado>';
inherited;
end;

procedure TWRecursoSaveToXml.Visit (pRecurso: TWMateriaPrima);
begin
inherited;

{ Versão específica dessa operação p/ o recurso "Matéria Prima" }
_XML := _XML + ' <materiaPrima>' + #13#10;
_XML := _XML + ' <codigo>' + pRecurso._CProd + '</codigo>' + #13#10;
_XML := _XML + ' <nome>' + pRecurso._Nome + '</nome>' + #13#10;
_XML := _XML + ' <qtde>' + FormatFloat ('#,##0.00', pRecurso._Qtde) + '</qtde>' + #13#10;
_XML := _XML + ' </materiaPrima>' + #13#10;
end;

procedure TWRecursoSaveToXml.Visit (pRecurso: TWMaquina);
begin
inherited;
{ Versão específica dessa operação p/ o recurso "Máquina" }
_XML := _XML + ' <maquina>' + #13#10;
_XML := _XML + ' <codigo>' + pRecurso._Cod + '</codigo>' + #13#10;
_XML := _XML + ' <nome>' + pRecurso._Nome + '</nome>' + #13#10;
_XML := _XML + ' <setup>' + FormatFloat ('#,##0.00', pRecurso._TempoSetup) + '</setup>' + #13#10;
_XML := _XML + ' <tempo>' + FormatFloat ('#,##0.00', pRecurso._Tempo) + '</tempo>' + #13#10;
_XML := _XML + ' </maquina>' + #13#10;
end;
Por fim, podemos implementar a função que aplica a operação ao produto acabado. Por definição, isso implica aplicar a mesma operação sobre os recursos que compõe esse produto acabado. Por isso, teremos que passar por todos os recursos, chamando a função Accept para determinar como a operação deve ser executada em cada um deles:
procedure TWProdutoAcabado.DoOperacao (AOper: TWOperacoesPA);
var i : integer;
lMatPrima: TWMateriaPrima;
lMaquina : TWMaquina;
begin
AOper.InitOper (Self);

{ Aplica a operação a cada matéria prima da lista }
for i := 0 to _MatPrimas.Count - 1 do
begin
lMatPrima := _MatPrimas.Items[i] As TWMateriaPrima;
lMatPrima.Accept (AOper);
end;

{ Aplica a operação a cada máquina da lista }
for i := 0 to _Maquinas.Count - 1 do
begin
lMaquina := _Maquinas.Items[i] As TWMaquina;
lMaquina.Accept (AOper);
end;

{ Aplica a operação no roteiro, se houver um }
if (_Roteiro <> Nil) then
_Roteiro.Accept (AOper);

AOper.TermOper;
end;

{ ... }

var lOperacao : TWRecursoSaveToXml;
lProd : TWProdutoAcabado;
lXml : String;
begin
lProd := TWProdutoAcabado.Create ('Computador');

lOperacao := TWRecursoSaveToXml.Create;

lProd.DoOperacao(lOperacao);

{ Publica o resultado da operação }
lXml := lOperacao.GetXML ();
{ ... }
end;

A parte inferior do quadro demonstra como se dá a aplicação de uma operação.

O projeto com esse exemplo pode ser salvo a partir desse link. Ele foi criado em Delphi 2005 mas deve ser possível compilá-lo em outras versões do IDE sem problemas.