Júlio Battisti Livros e Cursosa sua sala de aula na internet

Neste capítulo aprenderemos tópicos avançados de desenvolvimento de aplicações no SQL SERVER 2005. Tratarei de elementos que são criados em um Banco de Dados do SQL SERVER 2005 e podem ser acessados por aplicações desenvolvidas em Visual Basic, Delphi, ASP, ASP.NET, VB.NET, C#, etc.

Iniciaremos o capítulo falando sobre Stored Procedures. Conforme veremos, existem muitas aplicações práticas e vantagens de utilizarmos Stored Procedures. A linguagem que utilizamos para criar Stored Procedures, como não poderia deixar de ser em se tratando de SQL SERVER 2005, é a linguagem T-SQL.

Stored Procedures são semelhantes a procedimentos e subrotinas em outras linguagens. Por exemplo:

• Podem ser chamados por outro Stored Procedures ou por programas desenvolvidos em ASP, Visual Basic, Delphi, ASP.NET, C#, VB.NET, etc.
• Podem receber um ou mais parâmetros de entrada e retornar um ou mais parâmetros de saída.
• Podem conter lógica de programação, como instruções de controle e de laço.

A utilização de Stored Procedures nos fornece vantagens, como por exemplo: programação modular, execução mais rápida, redução de tráfego de rede e, ainda, podem ser utilizados como um mecanismo de segurança.

Depois, passaremos a estudar o mecanismo de Triggers.

Triggers são uma classe especial de Stored Procedures. Uma trigger pode ser programada para executar sempre que um comando UPDATE, INSERTE ou DELETE for executado em um Banco de Dados. A principal utilização de triggers é para impor “Regras de Negócio” ao Banco de Dados. Uma Regra de Negócio é uma regra do mundo real que deve ser respeitada pelas operações efetuadas sobre os dados do Banco de Dados. Vamos supor que um gerente de um determinado banco não possa aprovar mais do que R$ 50.000,00 em empréstimos por mês. Ao inserir um novo empréstimo no Banco de Dados, podemos fazer com que seja verificado o total de empréstimos já aprovados pelo gerente, no mês corrente. Se o total exceder o valor máximo para o gerente, o novo empréstimo não será aprovado. Podemos inclusive fazer com que o chefe imediato do gerente, receba um e-mail avisando desta situação.

Também mostraremos um exemplo de triggers que atualizam, automaticamente, uma página HTML com dados de uma ou mais tabelas. Veremos passo a passo como construir esta página e testaremos a atualização automática da página.

Na parte final do capítulo, tratarei de alguns comandos avançados da linguagem T-SQL. Falaremos sobre Joins avançados e os diferentes tipos de Joins existentes, sobre “Sumarização” de dados. Também trataremos das diversas funções que podem ser utilizadas em uma instrução SELECT. Também veremos criação de sub-consultas e os cuidados em relação ao desempenho, quando utilizamos sub-consultas.

Este capítulo apresenta uma série de conceitos de fundamental importância para o desenvolvimento de aplicações com o SQL Server 2005 ou baseadas no SQL Server 2005. Nos próximos capítulos, falaremos um pouco mais sobre aplicações, principalmente utilizando páginas ASP para acessar um Banco de Dados no SQL Server 2005.

Desenvolvimento em: Teoria e Desenvolvimento de Stored Procedures no SQL Server 2005

Pré-Requisitos:

• Fundamentos apresentados na Parte I.
• Saber utilizar o SQL Server Management Studio e o Query Analyzer.
• Noções sobre Integridade de Dados e os tipos de integridade existentes no SQL Server 2005.
• Noções sobre os comandos básicos da linguagem T-SQL.

Metodologia:

• Apresentação do conceito de Stored Procedures e dos principais comandos para a criação e teste de Stored Procedures.

Técnica:

• Utilização da janela de execução de comandos T-SQL e do SQL Server Management Studio para a criação e teste de Stored Procedures.

A primeira pergunta que temos que responder é a seguinte:

“O que é um Stored Procedure?”

Resposta formal: “Um Stored Procedure é uma coleção de comandos, a qual é atribuída um nome, sendo que esta coleção nomeada de comandos é salva no Banco de Dados.”

Resposta descomplicada: “Podemos gravar um conjunto de comandos, que realiza uma função específica no Banco de Dados. Ao salvarmos este conjunto de comandos damos um nome a ele. Pronto, acabamos de criar um Stored Procedure.”

Uma vez criado, o Stored Procedure pode ser executado sempre que for necessário, simplesmente “chamando” o Stored Procedure pelo nome, isto evita que tenhamos que digitar o conjunto de comandos repetidamente, cada vez que ele tiver que ser utilizado. A utilização de Stored Procedures é uma técnica eficiente quanto temos que executar o mesmo conjunto de comandos, repetidas vezes. Ao invés de digitar os comandos cada vez que a operação precisa ser executada, criamos um Stored Procedure e executamos o Stored Procedure, sempre que for necessário. Isso evita que o mesmo conjunto de comandos tenha que ser inserido em diversos locais no Banco de Dados. Se tivermos que alterar o conjunto de comandos de uma determinada operação, basta alterá-lo no Stored Procedures e todos os objetos que chamam o Stored Procedure passarão a ter acesso à nova versão. Isto facilita muito as alterações na lógica do Banco de Dados. É ou não é muito parecido com a idéia de Procedimentos e Funções de linguagens como o Pascal e o C.

Em um Stored Procedure também podemos ter estruturas de controle e decisão, típicas das linguagens de programação. Em termos de desenvolvimento de aplicações, também temos vantagens com a utilização de Stored Procedures.

Vamos imaginar que estamos criando uma aplicação em Visual Basic e que a aplicação acessa dados de um Banco de Dados do SQL Server 2005. Sem a utilização de Stored Procedures, a aplicação precisa enviar o conjunto de comandos T-SQL necessários a execução de cada tarefa. Pode ser que, em diversas partes do programa, seja necessária a execução da mesma tarefa. A pior técnica possível seria codificar estes comandos, nos diversos pontos do programa, onde os comandos são necessários. Qualquer alteração iria demandar que todo o código do programa fosse revisado e as ocorrências dos comandos alteradas. Simplesmente um pesadelo.

Poderíamos melhorar um pouco esta situação isolando o conjunto de comandos em um função e chamando a função onde os comandos são necessários. Isso faria com que apenas tivéssemos que modificar a função, sempre que houvesse alteração na lógica de processamento dos dados. Mas imagi­ne uma empresa grande, com milhares de usuários utilizando a aplicação em Visual Basic. Modificar uma função interna da aplicação, significa ter que reinstalar a aplicação em milhares de estações de trabalho. A tarefa de manutenção da aplicação atualizada torna-se bastante complicada e dispendiosa em termos de custos e tempo.

Se, ao invés de uma função interna da aplicação, criarmos um Stored Procedure e fizermos com que o programa chame o Stored Procedure, para executar os comandos necessários, teremos mais facilidades no momento de atualizar a aplicação. Pois, neste caso, bastaria alterar o Stored Procedure no servidor SQL Server 2005 e pronto, a aplicação já passaria a ter a acesso a versão modificada, evitando uma reinstalação da aplicação cliente em milhares de estações de trabalho.

Existem soluções ainda mais sofisticadas do que a utilização de Stored Procedures. São as chamadas aplicações em n camadas. Este é o modelo recomendado pela Microsoft. Falaremos sobre aplicações de n camadas no Capítulo 11.

Além de facilitar a manutenção e alteração das aplicações, a utilização de Stored Procedures nos fornece outras vantagens, dentre as quais destacamos as seguintes:

• Ocultar a complexidade de acesso ao Banco de Dados: Quando utilizamos a normalização de tabelas, a tendência é que tenhamos um número elevado de tabelas no Banco de Dados. Porém, do ponto de vista do usuário, as consultas por ele executadas envolvem dados de diversas tabelas. Ao invés de fazer com que a aplicação tenha que tratar desta complexidade – acessando e consolidando dados de diversas tabelas para exibi-los de uma maneira consolidada para o usuário – podemos “ocultar” esta complexidade num Stored Procedure. O Stored Procedure contém todos os comandos necessários para buscar os dados necessários e fornece os dados para o usuário. A única coisa que a nossa aplicação precisa fazer é chamar o Stored Procedure e receber os resultados. Veja que isto simplifica, enormemente, o desenvolvimento de aplicações.
• Pode receber parâmetros de entrada e retornar resultados, através de parâmetros de saída. Com isso um Stored Procedure pode ser desenvolvido, de tal forma que receba parâmetros de entrada e, com base nos parâmetros recebidos, execute diferentes comandos ou retorne diferentes conjuntos de dados.
• Redução no tráfego de rede gerado pela aplicação: Esta redução acontece, porque ao invés da aplicação enviar um grande número de comandos, é enviado para o servidor apenas o pedido de execução do Stored Procedure e os parâmetros de entrada necessários. O Stored Procedure executa e retorna somente os resultados, na forma de parâmetros de saída.
• Facilita e centraliza o gerenciamento de permissões. Podemos atribuir permissões em nível de usuário ou role para a execução de um Stored Procedure.
• Melhoria na velocidade de execução: O SQL Server 2005 compila os comandos do Stored Procedure, na primeira vez que este é executado, e mantém estes comandos na memória cache, para agilizar as próximas execuções. Isto faz com que as chamadas subseqüentes executem bem mais rápido.

Temos diversos tipos de Stored Procedures. Neste capítulo aprenderemos a criar os chamados “User-defined Stored Procedures”, ou seja, Stored Procedures criados (definidos) pelo usuário. Podemos criar User-defined Stored Procedures em qualquer Banco de Dados do usuário.

Os tipos de Stored Procedures existentes são os seguintes:

• User-defined Stored Procedures: São criados nos Bancos de Dados do usuário. São utilizados para execução de tarefas repetitivas, facilitando a manutenção e alteração destas tarefas.
• Temporary Stored Procedures: São criados e mantidos pelo próprio SQL Server 2005, no Banco de Dados tempdb. Normalmente, estão associados a tarefas de manutenção e gerenciamento de conexões de usuários com o servidor SQL Server 2005. Podemos ter dois tipos:
  • Stored Procedures temporários locais: O nome inicia com o sinal # e somente a conexão que criou um Stored Procedure temporário poderá executá-lo; ao ser encerrada a conexão, o Stored Procedure é eliminado.
  • Stored procedures temporários globais: O nome inicia com ## e podem ser executados por todas as conexões ativas com o Banco de Dados, até que a conexão que criou o Stored Procedure seja encerrada, quando então o Stored Procedure é excluído.
• System Stored Procedures: Já falamos e utilizamos bastante este tipo de Stored Procedures. São criados no momento da instalação do SQL Server 2005 e ficam gravados no Banco de Dados master. Iniciam com “sp_”. São utilizados para uma série de tarefas de manutenção e gerenciamento dos diversos objetos e configurações do servidor SQL Server 2005.
• Extended Stored Procedures: Executam funções externas ao servidor SQL Server 2005, normalmente funções típicas do Sistema Operacional. São criados no momento da instalação do SQL Server 2005 e tem o nome iniciando com “xp_”. Alguns System Stored Procedures têm sua funcionalidade baseada na chamada a Extended Stored Procedures.

Ao criarmos um Stored Procedure, os comandos do Stored Procedure são verificados em busca de erros de sintaxe. Se houver erros, uma mensagem é emitida e o Stored Procedure não poderá ser salvo enquanto os erros não forem corrigidos. Na Figura 10.1, temos um exemplo de uma mensagem de erro, na verificação da sintaxe dos comandos de um Stored Procedure.

Figura 10.1 Erro nos comandos de um Stored Procedure.

Agora que já conhecemos o que é e quais as vantagens, podemos partir para a criação de Stored Procedures.