Introdução às Storeded Procedure com SQL Server2000/2005 – 2º Parte

Olá pessoal,

Após algum tempo afastado do portal DEVMEDIA por estar em fase de término de semestre em meu curso, volto agora com continuação da série de estudos sobre Procedimentos Armazenados (Stored Procedures).

Para quem começou a acompanhar agora, vamos relembrar alguns conceitos que nos serão úteis daqui para frente que passaremos a focar maior funcionalidade e como conseqüência, recursos mais avançados.

Recordando... (Recordar é viver...).

PRINCIPAIS VANTAGENS DOS PROCEDIMENTOS ARMAZENADOS:

As vantagens do uso de Stored Procedures são claras:

·       Modularidade: passamos a ter o procedimento divido das outras partes do software, bastando alterarmos somente às suas operações para que se obtenha as modificações disponíveis para toda a aplicação;

·       Diminuição de I/O: uma vez que é passado parâmetros para o servidor, chamando o procedimento armazenado, as operações se desenrolam usando processamento do servidor e no final deste é retornado ou não os resultados de uma transação, sendo assim, não há um tráfego imenso e rotineiro de dados pela rede;

·      Rapidez na execução: os stored procedures, após salvos no servidor, ficam somente aguardando, já em uma posição da memória cache, serem chamados para executarem uma operação, ou seja, como estão pré-compilados, as ações também já estão pré-carregadas, dependendo somente dos valores dos parâmetros. Após a primeira execução, elas se tornam ainda mais rápidas;

·      Segurança de dados: podemos também, ocultar a complexidade do banco de dados para usuários, deixando que sejam acessados somente dados pertinentes ao tipo de permissão atribuida ao usuário ou mesmo declarando se o Stored Procedure é proprietário ou público, podendo ser também criptografada com WITH ENCRYPTION.

TIPOS DE PROCEDIMENTOS ARMAZENADOS EXISTENTES:

Existem certos tipos de Stored Procedures para o SQL Server 2000 e para o SQL Server 2005, são eles:

·     System Stored Procedures ou Procedimentos Armazenados do Sistema: nas duas versões do SGBD (Sistema de Gerenciamento de Bancos de Dados), são criados no momento da instalação e ficam armazenados no banco de dados chamado MASTER, junto com as entidades e outros procedimentos próprios do sistema. São utilizados das mais diversas formas. Alguns exemplos clássicos, que utilizo muito, é o SP_HELPINDEX, SP_WHO e outros que você pode conferir no link abaixo:

http://doc.ddart.net/mssql/sql70/sp_00.htm

·     Local Stored Procedure ou Procedimentos Armazenados locais: esses procedimentos são criados em bancos de dados de usuários individuais, ou seja, no SQL Server 2000 são proprietárias. Já no SQL Server 2005, tem o nome de Stored Procedures Temporárias Locais, que iniciam com um sinal de # e somente a conexão que a criou poderá executá-la. Ainda no SQL Server 2005, existem os Procedimentos Temporários Globais, que podem ser utilizados de forma global, ou seja, por qualquer usuário desta conexão e iniciam com ##. Ao ser encerrada esta conexão, os dois procedimentos são eliminados.

·       Extended Stored Procedure ou Procedimentos Armazenados Estendidos: são comuns às duas versões do SGBD Microsoft. Executam funções externas e do sistema operacional e iniciam com “xp_”.  Esses procedimentos são implementados como Dynamic-link Librarys (DLL), executadas fora do ambiente do SQL Server.

·     User-Defined Stored Procedure ou Procedimento Armazenado Definido pelo Usuário: estes são criados em bancos de dados pelo próprio usuário, como o nome já diz. São utilizados para realizar tarefas repetitivas, facilitando a manutenção e alteração.

Antes que entremos definitivamente nos códigos e implementações, devemos também passar por alguns conceitos, que trago nesta segunda parte do assunto: “Como é o processamento inicial de um procedimento armazenado” e “Como é o processamento subseqüente dos procedimentos armazenados”.

COMO É O PROCESSAMENTO INICIAL DE UM PROCEDIMENTO ARMAZENADO

Assim como ocorre com todas as consultas executadas em um banco de dados, quando executamos um procedimento armazenado pela primeira vez, a engine do SGBD define um plano de execução, elaborado pelo otimizador interno de consultas e o guarda em cache, denominado cache de procedimentos. O cache de procedimentos é um conjunto de páginas que contém planos de execução para todas as instruções/consultas executadas nesta conexão. O tamanho desse cache varia de acordo com o nível de atividades e está localizado no pool de memória.

CRIAÇÃO

Logo após a criação do procedimento, sua sintaxe é verificada em sua precisão sintática. Case haja algum erro na sua implementação, um erro é devolvido ao usuário, pelo contrário, é iniciado o processo de registro do procedimento em tabelas do sistema.

Primeiramente, a tabela sysobjects recebe o nome do objeto que foi definido pelo usuário, o status atual deste, o tipo, um UID (Unique Identifier) e vários outros campos como podemos ver na imagem abaixo.

Imagem 1 – Recuperando os objetos da tabela sysobjects.  

As definições, ou seja, o seu código é inserido em outra tabela do sistema chamada syscoments, juntamente com alguns comentários do sistema outros campos, como pode ser verificado na imagem abaixo.

Imagem 2 – A tabela syscomments exibe, dentre outras informações, o conteúdo do procedimento de forma clara e criptografado.

Execução

Após a primeira vez que o procedimento é executado ou recompilado, ele é analisado em um processo chamado de resolução, que, revisará o seu plano de execução diante das alterações relevantes ao melhor desempenho ao acesso a dados.

As alterações que forçaram uma recompilação do procedimento são:

·         Alteração estrutural a qual o procedimento faça referência (ALTER VIEW, ALTER TABLE);

·         Novas estatísticas de distribuição geradas;

·         Alteração nos índices usados pelo procedimento;

·         Alterações nas chaves que são referenciadas no procedimento.

Otimização

Após ser aprovado pelo processo de resolução, o otimizador de consultas do SQL SERVER analisará o conteúdo do procedimento sintaticamente para definir então pela otimização do plano de execução que conterá o método mais rápido e otimizado de acesso a dados.

Para que a otimização seja feita, o SGBD leva em conta os seguintes fatores:

·         Volume de dados nas tabelas participantes;

·         Índices e colunas indexadas;

·         Expressões apresentadas na(s) cláusula(s) WHERE;

·         Presença de associação ou funções de grupo como ORDER BY, GROUP BY ou UNION.

Mais à frente, mostrarei como podemos verificar com o PROFILE os níveis de desempenho de um procedimento armazenado.

Compilação

Desde a análise sintática até a criação de um plano de execução otimizado para um procedimento e o seu armazenamento no cace de procedimentos, este conjunto de processos podemos definir como compilação.

Estatísticas também são criadas visando manter em cache os planos dos procedimentos mais utilizados. Tais estatísticas são criadas nas tabelas do sistema, assim como os registros na syscomments e sysobjects.

Chega de papo acadêmico e vamos para a prática!

USANDO PARÂMETROS EM PROCEDIMENTOS ARMAZENADOS

Os procedimentos armazenados podem ser implementados para retornar informações de somente um tipo ou informações dinâmicas, de acordo com parâmetros que são enviados a ele.

Vamos supor que você precise verificar em um banco de dados, quais os produtos mais vendidos até hoje por sua empresa. Basta termos um procedimento armazenado com um SELECT TOP (N) na tabela que armazena informações de vendas de produtos e quando necessitarmos executamos este procedimento, por isso, essas informações são somente de um tipo ou estáticas.

Caso outrora, precise de informações de vendas em um determinado período ou mesmo, saber como um novo produto tem se saído em relação às vendas, podemos receber o nome do produto como parâmetro em nosso procedimento armazenado e retornar os dados referentes somente a este produto. Para termos tal dinamismo, precisaremos de parâmetros que serão definidos dentro do procedimento, chamados parâmetros de entrada.

PARÂMETROS DE ENTRADA

Os parâmetros de entrada permitem passar informações para algum tipo de manipulação de informações que são encapsuladas dentro de um procedimento armazenado. Tais parâmetros são declarados junto com a instrução CREATE PROCEDURE, como no exemplo abaixo, que pode ter utilidade para você em algum momento.


Imagem 3 – Veja que agora podemos consultar especificamente o produto que queremos informações.

Dentro ou na implementação dos Stored Procedures, que aqui estou referenciando como “Procedimentos Armazenados”, podemos definir os mais variados tipos de função para manipularmos a informação de maneira a buscar um resultado satisfatório.

Um exemplo bem usual é implementar um procedimento para trazer as ultimas vendas com quantidade, preço unitário e somatório dos produtos vendidos.

Vou usar o banco de dados padrão, Northwind, para ilustrar um procedimento que pode facilmente ser entendido e manipulado, posteriormente, de maneira a se encaixar a lógica de qualquer banco de dados que tenha o mesmo caráter, abordando neste exemplo, os parâmetros de entrada que receberão os valores que passarmos afim de que algo aconteça.

Exemplo:


Imagem 3 – Usamos os parâmetros de entrada para uma possível delimitação dos valores

que queremos retornar com o uso da condição de pesquisa BETWEEN.

Neste momento, temos que salientar que, para executarmos nosso procedimento, após armazenarmos, pressionando F5 ou , temos duas (2) maneiras distintas dentro do SQL

Server 2000, as quais conceituo e demonstro exemplos à seguir.

EXECUTANDO UM PROCEDIMENTO ARMAZENADO

Podemos executar os procedimentos armazenados de duas formas, com passagem de valor

com nomes de parâmetros ou passando valor por posição.

Passando valor com nomes de parâmetros

Esta é uma forma bem convencional de executarmos procedimentos armazenados, via

comando no Query Anlyser. A seguir, executamos nosso procedimento com a instrução EXECUTE ou EXEC, seguido pelo nome do procedimento, “mySp_getInfoProduto”, passando

os valores ao procedimento explicitando os parâmetros e seus respectivos valores.

OBS.: Aqui, a ordem em que os parâmetros são declarados não importa.

Imagem 4 – Passamos os valores dos parâmetros explicitando os mesmo e atribuindo seus valores para disparar a consulta encapsulada dentro do procedimento.

Passando valor por posição

A passagem de parâmetro aos procedimentos, utilizando o conceito de passagem de valor por posição deve ser bem trabalhada, pois, a posição em que o parâmetro se encontra dentro do procedimento deve ser respeitada. Como declaramos @valor1 e @valor2, essa ordem deverá

ser respeitada quando executarmos o procedimento.

Imagem 5 – Executando o procedimento passando parâmetros por posição.

PARÂMETRO DE SÁIDA

Os parâmetros de entrada trabalham informações dentro do procedimento armazenado, como vimos na definição anterior e também com os exemplos que apresentei.

Já os parâmetros de saída, são parâmetros que nos retornam informações de processamentos ou informações de sucesso ou insucesso e devem ser apresentados com OUTPUT após o seu nome.

Vamos a um exemplo clássico:

Imagem 6 – Perceba que após o parâmetro de saída temos OUTPUT.

OUTPUT diz para o SGBD que, ao executarmos o procedimento, estamos esperando uma resposta do processamento, ou seja, a saída de um parâmetro.

Executando:

Imagem 7 – Resultado da execução do procedimento passando valores por posição e

trazendo o resultado com um parâmetro de saída.

Especialmente para este artigo, preparei uma Stored Procedure de login de usuário para um

 bom entendimento do que foi abordado. Poderemos executar o login de alguma forma em relação àquelas anteriormente apresentadas, passando valor com nomes de parâmetros ou passando o valor por posição, como já mencionado anteriormente.

Note que, se você deseja receber em sua aplicação algo que indique que o usuário poderá ou não ser logado com os dados que ele digitou, após executar o procedimento e passar os parâmetros a ele, ele devolverá para a aplicação uma variável-álias de nome ‘logger’, que conterá o que você desejar.

Baixe o procedimento clicando aqui.

Procedimento Armazenado para Login de usuário (modelo básico e ilustrativo):


 

Bom, espero que todos tenham gostado de nosso overview sobre Procedimentos Armazenados ou Stored Procedures e que este artigo se já de grande proveito!

Estarei apresentando no próximo artigo, algumas funções que não fazem parte do padrão ANSI SQL e que é de grande valia para profissionais que desejam enriquecer técnicas de formatação, agregação e associação de objetos para relatórios de dados consolidados com o SQL Server 2000/2005.

Um abraço a todos e até a próxima!

Wagner Bianchi – INFODBA/SQL MAGAZINE

Leia a primeira parte deste artigo em:
//www.devmedia.com.br/articles/visualizacomponente2.asp?comp=2213