Introdução às Stored Procedure com SQL Server 2000/2005

 

 

Olá pessoal,

 

 

Mais uma vez, vamos abordar um assunto interessante, para o qual, recebi muitos e-mails quando abordei que falaria sobre neste artigo. Agradeço as pessoas da faculdade Federal Fluminense e da Federal do Paraná que escreveram pedindo uma enfática no assunto que irei abordar.

 

Todos, desenvolvedores, analistas e DBA’s já estão bem cientes que os bancos de dados relacionais de nosso tempo e desde algum tempo atrás, já contemplam procedimentos armazenados e estes são realmente de muito proveito em várias situações do desenvolvimento. Para aqueles que trabalham com implementações em sistemas web, este é um ótimo recurso para garantir que o seu banco não terá “filhos órfãos”, já que, por exemplo, um usuário pode desistir da compra no meio dela. Já pensou nisso?

 

Neste primeiro artigo, mostrarei alguns exemplos práticos para que fiquemos bem familiarizados com os conceitos inicias. Saliento também que, a prática no desenvolvimento destas rotinas lhe trará maior segurança com o passar do tempo. Todos os procedimentos que serão apresentados terão sua abordagem tanto no SQL Server 2000 quanto no SQL Server 2005 e somente serão apontados quando se tratar de alguma diferença relevante.

 

Um procedimento armazenado (Stored Procedure), é uma coleção de instruções implementadas com linguagem T-SQL (Transact-Sql, no SQL Server 2000/2005), que, uma vez armazenadas ou salvas, ficam dentro do servidor de forma pré-compilada, aguardando que um usuário do banco de dados faça sua execução. Geralmente, assim como VIEWS fazem com relatórios e dados estatísticos escalonáveis, os SP’s encapsulam tarefas repetitivas, desde um simples INSERT, passando por inserções por lote, updates e algumas outras instruções mais complexas, como, efetuar uma efetivação de saque em uma conta de um determinado cliente em uma instituição bancária ou efetivar saídas de mercadorias seguido por baixa em estoque. Eles oferecem suporte a variáveis declaradas pelo próprio usuário, uso de expressões condicionais, de laço e muitos outros recursos, os quais veremos alguns mais à frente.

 

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

 

·         Modularidade: passamos a ter o procedimento divido das outras partes do software, bastante alterarmos somente às suas operações para que se tenha as modificações por 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 desenolam 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.

 

 Ex.: Utilizando WITH ENCRYPTION no SQL Server 2000:

05-07pic05.JPG
 

Imagem I – Acessando o conteúdo de um Procedimento Armazenado pelo * Object Browser  no SQL Server 2000.

 

Ex.: Utilizando WITH ENCRYPTION no SQL Server 2005:


05-07pic06.JPG
Imagem II - Acessando o conteúdo de um Procedimento Armazenado pelo * Object Explorer  no SQL Server 2005.

 

Repare nas imagens exibidas acima que, no SQL Server 200, recebemos uma  janela de conexão do objeto Procedure, nos mostrando que este fora criado de forma criptografada e que o conteúdo somente poderá ser editado por quem a criou e mesmo assim, este também não aparecerá para o dono. Somente, copiando à parte é que teremos acesso a este procedimento criptografado, mesmo que seja você o dono/criador dele. O comando ALTER PROCEDURE poderá ser utilizado para modificar o conteúdo do procedimento.

Observações:


05-07pic07.JPG
Imagem III – Brow ser e Explorer, usaremos para executar e visualizar o conteúdo de procedimentos armazenados.

 

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, 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 ultilizados das mais diversas formas. Um exemplo clássico, que utilizo muito, é o SP_HELPINDEX, para checar os índices de uma determinada tabela, como mostra a imagem abaixo:


05-07pic08.JPG 

Imagem IV – System Stored Procedure, exemplo da tabela locadora do artigo sobre associação de tabelas.

 

·         Local Stored Procedure ou Procedimentos Armazenados locais: esess procedimetjos 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 Extendidos: 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 ambinete do SQL Server.

 

·         User-Defined Stored Procedure ou Procedimento Armazenados Definidos pelo Usuário: estes são criados em bancos de dados pelo prórpio usuário, como o nome já diz. São utilizados para realizar tarefas repetitivas, facilitando a manutenção e alterção, já que estão em um só ponto da aplicação/Banco de Dados.

 

 

CRIANDO STORED PROCEDURES (PROCEDIMENTOS ARMAZENADOS)

 

Alguns cuidados devem ser ressaltados, antes mesmo de começarmos a criar nossos primairos Procedimentos Armazenados:

 

·         Temos a nossa disposição ou à disposição de tais procedimentos, um espaço máximo de 128 MB, o que nos deixa bem à vontade;

·         Procedimentos Armazenados, podem fazer referência à VIEWS e TRIGGERS, bem como à tableas temporárias;

·         Caso um Stored Procedure crie uma tabela temporária local, essa tabela somente existirá até o fim da execução do procedimento;

·         Um instrução CREATE PROCEDURE deve ser única durante a execução, ou seja, nehu outro T-SQL poderá ser executado no mesmo lote;

·         Somente os usuários que são membros da role de servidor SYSADMIN ou da role de Banco de Dados db_owner e db_admin têm permissão para executar o comando CREATE PROCEDURE, permissão que pode ser atribuida por mebro da role SYSADMIN.

·         Os comandos: CREATE DEFAULT, CREATE RULE, CREATE TRIGGER e CREATE VIEW não podem estar em meio a uma construção de Stored Procedure.

 

SINTAXE BÁSICA:


05-07pic09.JPG 

 

PRIMEIRO EXEMPLO

 

Criaremos um banco de dados chamado “teste” e uma tabela, dentro do banco, de nome “tbl_usuario”, que armazenará informações de usuários, como venho fazendo alguns artigos passados, e daremos carga na mesma com um Stored Procedure. Todo o source utilizado neste artigo, poderá ser baixado no final do artigo.

Criando a base de dados “teste” no SQL Server 2005:

05-07pic10.JPG
 

Imagem V – Criando o banco de dados “teste”.

 

Criando “tbl_usuario” no Management Studio (SQL Server 2005):

05-07pic11.JPG
 

Imagem VI – Criando a tabela de usuários.

 

Após criarmos a tabela de referência, a qual iremos trabalhar, vamos então criar nosso primeiro procedimento armazenado básico, para inserção de usuários, dando então as primeiras cargas na tabela executando tal procedimento que criaremos.

 

Antes mesmo de criarmos, ficamos pensando, por onde começar, como devo pensar para assim alcançar o conceito de procedimento armazenado??

 

Vamos a mais uma breve explicação! No caso que seu Procedimento Armazenado (Stored Procedure) seja um procedimento de cadastro, temos que fornecer a ele os dados, os quais queremos inserir. Funcionará como uma * FUNCTION . O procedimento recebe o valor passado em uma variável, e insere o valor da variável, essa colocada no lugar dos reais dados, dentro de uma declaração INSERT, que se encontra encapsulada no procedimento, ou seja, vamos trabalhar!

 

 

CRIANDO NOSSA PRIMEIRA STORED PROCEDURE

 

Como já citada, usamos a declaração CREATE PROCEDURE para dar início a implementação do nosso procedimento. Já argumentamos alguns cuidados que devemos ter na hora da implementação e também uma estrutura básica de formação para um procedimento.

 

Nosso primeiro Stored Procedure se chamará “sp_insereUsuario”. Receberemos, ao executarmos o procedimento, os parâmteros para que o insert possa acontecer, já que nossos campos da tabela não aceitam valores nulos. Passaremos esses parâmteros para dentro de um INSERT e receberemos uma mensagem de “Cadastro efetuado com sucesso!”.

 

Criando o Stored Proceduresp_insereUsuario”:

05-07pic12.JPG
 

Imagem VII – Criando nosso procedimento armazenado para inserção de usuários na tabela “tbl_usuario”.

 

A partir de então, após digirtamos o código da figura acima, pressionamos F5 e recebemos a mensagem de “Comando executado com sucesso”. No SQL Server 2005, no object explorer, podemos localizar o procedimento clicando no sinal de expansão de pastas, fazendo o seguinte caminho:

05-07pic13.JPG

Imagem VIII – Visualizando o “armazem” do procedimento no object browser.

 

 

Bom, o que falta agora?? Muito bem, falta o finalmente que será a carga na tabela com nosso procedimento armazenado. Ao trabalho.

Ainda no Management Studio ou no Query Analyser, vamos executar no nosso procedimento armazenado, passando a ele todos os dados do usuários que precisamos para cadastrá-los:

05-07pic14.JPG
 

Imagem IX – Executando nosso procedimento armazenado, o cadastro foi efetuado com sucesso!

 

 

Parabéns, você desenvolveu o seu primeiro procedimento armazenado, para cadastrar usuários, mas, salientando que isso é realmente muito básico. Podemos disparar uma mensagem, testar se os dados realmente foram preenchidos pelo usuário e várias outras coisas.

 

Mensagens em procedures talvez não funcionnem bem, pois esse tipo de implementação funciona melhor e com mais fidelidade em TRIGGERS, mas podemos fazer alguns exemplos para que o sistema devolva mensagens ao usuário logado, caso um novo cadastro seja processado.

 

ALTERANDO UM STORED PROCEDURE

 

Com a declaração ALTER PROCEDURE vamos alterar o código do procedimento para conferir, hipoteticamente, se os valores foram realmente passados e que assim, podemos inserí-los na tabela tbl_usuario de nosso banco de dados teste.

 

Alter Procedure – alterando o procedure sp_insereUsuario:

05-07pic15.JPG
 

Imagem X – Utilizando o comando ALTER PROCEDURE e alterando o procedimento.

 

Agora, o teste final de nosso artigo;

05-07pic16.JPG
Imagem XI – Erro que nos foi devolvido ao tentar fazer uma inserção fora dos padrões da nossa regra de negócios.

 

 

Chegamos então, ao final de mais um artigo. Espero que tenham gostado e nosso assunto sobre Stored Procedures não pára por aqui, ainda temos muito assunto pela frente.

 

No próximo artigo, iremos expplorar mais recursos dentro dos procedimento armazenados e mesclá-los com outros procedimentos como triggers e views.

 

Qualquer dúvida, use o novo sistema de comentários do do site (preferencial) ou envie um e-mail para wagnerbianchi@infodba.com . Terei muito prazer em responder e ajudar.

 

Caso queira pegar todo o source apresentado neste artigo, clique aqui.

 

Um abraço e até a próxima.