Stored Procedures – Como criar? E quais benefícios e ganho de desempenho?

Pessoal,
Boa noite!

Como estão? Estou aqui mais uma vez para contribuir com um pouco de meu conhecimento.

Irei falar hoje sobre Stored Procedures (SP), que é um grupo de coleções Transact-SQL (T-SQL) pré compiladas em um banco de dados SQL Server. Stored Procedure é um método de encapsulamento de tarefas repetitivas. As SP’s podem ajudá-los a alcançar uma execução lógica de aplicativos.

Em uma stored procedure é permitido que seja passados parâmetro de entrada, com o objetivo de atender uma condição necessária em uma instrução T-SQL.

Sintaxe de criação de uma Stored Procedure:

– Verificar se a procedure já existe, se sim, DROPA e CRIA ela novamente
If exists (select 1 from sys.procedures where name = ‘spSelectAlunos’)

      Drop Procedure Student.spSelectAlunos

Go
– Toda procedure deve ser criada com seu Schema. No exemplo abaixo utilizamos o schema Student

create procedure Student.spSelectAlunos

(

      @CPF  char(11)

)

As

Begin

      select *

        from dbo.Alunos

       where Alunos.CPF = @CPF

End
– Controle de Segurança
GRANT ALL ON Student.pSelectAlunos TO PUBLIC

Considerações da sintaxe de criação de uma stored procedure:

a)      Deve-se verificar se a procedure já existe no banco de dados, dropa ela e cria novamente. Mas por que não utilizar ALTER PROCEDURE? Também pode ser utilizado, o exemplo deste artigo é um padrão que adotei para poder atualizar a documentação interna da procedure (citarei abaixo);

b)      Toda procedure deve possuir seu schema para que não haja problemas/conflitos de execução, pois pode-se possuir procedures com o mesmo nome em schemas diferentes;

c)       Passar os devidos parâmetros de entrada da stored procedure. Não é obrigatório, dependerá da necessidade de cada desenvolvedor T-SQL.

 Modelo de Documentação de uma Stored Procedure

Para facilitar uma futura manutenção em uma Stored Procedure, é interessante documentá-la desde a sua criação à todas as alterações realizadas. Abaixo segue um simples modelo:

/******************************************************************************/
/* TTULO : Student.pSelectAlunos                                                                                               */
/* DEFINIO : CONSULTA DE ALUNOS POR CPF                                                                            */
/* DATA DE CRIAO : 02/05/2010                                                                                                  */
/* ANALISTA : GABRIEL B AZEVEDO                                                                                               */
/* EMPRESA : BARROSO CONSULTORIA EM TI                                                                            */
/*——————————————————————————————————————-*/
/* BANCO DE DADOS: MicrosoftMsdnTechnet                                                                           */
/******************************************************************************/
/* ALTERACOES REALIZADAS NA PROCEDURE                                                                               */
/*——————————————————————————————————————-*/
/* DATA – NOME – EMPRESA – DESCRICAO                                                                                     */
/* 02/05/2010 – Gabriel Azevedo – Barroso Consultoria em TI – Criao                                 */
/*——————————————————————————————————————-*/
/******************************************************************************/

Para completar o modelo acima, pode-se colocar o ambiente onde se encontra o Banco de Dados, as tabelas envolvidas, considerações, etc.

Controle de Segurança da Stored Procedure

Dentro da própria stored procedure pode-se configurar a permissão de acesso dos usuários. Como permissão de acesso é um tema grande, não irei detalhar muito, mas abaixo segue um exemplo de sintaxe, onde é liberado o acesso FULL de usuários do grupo PUBLIC:

GRANT ALL ON Student.pSelectAlunos TO PUBLIC

Quais são os Benfícios e o Ganho de Desempenho de uma Stored Procedure?

Ø  São registradas no servidor

Ø  Podem possuir atributos de segurança (com permissões) e encadeamento de propriedade

Ø  O usuário pode ter permissão de executar a stored procedure sem ter permissão em objetos internos da SP

Ø  Aumenta a segurança da aplicação

Ø  Permitem programação modular

Ø  A stored procedure pode ser criada uma única vez e ser chamada quantas vezes houver necessidade. Isso facilita na manutenção de sua aplicação

Ø  Podem reduzir o tráfego de rede

Ø  Por serem pré compiladas o tempo de execução é muito mais ágil do que você inserir as instruções diretas dentro de uma aplicação

Segue link do artigo em PDF:

http://cid-25e3f6074be4e9f6.skydrive.live.com/self.aspx/SQL%20Server%202008/MS%20SQL%20Server%202008%20-%2000006-2010%20-%20Stored%20Procedures.pdf

Espero mais uma vez ter contribuído no conhecimento a vocês.

Grande abraço a todos e até a próxima. Sucesso sempre!!!

Att.
Gabriel B Azevedo

About these ads