CADASTRE-SE

Trabalhando Com Stored Procedures

17 Comente abaixo Marku Vinícius da Silva

Neste Artigo veremos os principais conceitos para trabalhar com Stored Procedures, uma ferramente surpreendente e pouco conhecida.


Antes de mais nada, a primeira pergunta a ser respondida é:
- O que é um Stored Procedure?
   Stored Procedure é um conjunto de comandos, ao qual é atribuído um nome. Este conjunto fica armazenado no Banco de Dados e pode ser chamado a qualquer momento tanto pelo SGBD (sistema Gerenciador de Banco de Dados) quanto por um sistema que faz interface com o mesmo.
   A utilização de Stored Procedures é uma técnica eficiente de executarmos operações reetitivas. Ao invés de digitar os comandos cada vez que determinada operação necessite ser executada, criamos um Stored Procedure e o chamamos. 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 vantagnes com a utilização de Stored Procedures.
   Imaginemos que estamos criando uma aplicação em Delphi e que a mesma acesse dados de um Banco de Dados do SQL Server 2000. Sem a utilização de Stored Procedures, a aplicação deveria enviar o conjunto de comandos T-SQL necessários à execução de cada tarefa. Imagine ainda ter que enviar os comandos em várias partes do programa. Qualquer alteração necessária demandaria uma revisão em todas as partes do programa que enviam estes comandos.
   Uma solução para o problema acima seria isolar e fechar os comandos T-SQL em uma função e chamá-la quando for necessário. O problema é que a cada alteração o programa deverá ser recompilado e, numa empresa com centenas de computadores conectados em rede, demandaria numa reinstalação em cada máquina. A tarefa de manutenção da aplicação atualizada em cada máquina torna-se bastante complicada.
Se ao invés de uma função interna da aplicação, criarmos um Stored Procedure e fizermos com que o programa chame-o para executar os comandos necessários, teremos mais facilidades no momento de atualizar a aplicação. Pois, neste caso, bastaria atualizar o Stored Procedure e pronto, a aplicação já passaria a ter a versão atualizada, evitando uma reinstalação em centenas de estações de trabalho.
Agora que já ponderamos as vantagens da utilização dos Stored Procedures, vamos à parte boa da coluna... a prática...rsrsrs
Você pode criar uma Stored Procedure em linha de comando no Query Analizer com a seguinte sintaxe:

CREATE PROCEDURE nome_do_stored_procedure

[

    {@parametro tipo_de_dados_parametro}[=valor_default] [output] 

]

[,...n]

AS

    comando1,

    comando2,

    comando3,

    ...,

    comando2

GO

Simples não é?
Então vamos a algumas considerações:
  • Somente poderão executar o comando CREATE STORED PROCEDURE, usuários que são membros da role de servidor sysadmin ou das roles de Banco de Dados db_owner e db_ddladmin;

  • Em um Stored Procedure, podemos incluir qualquer comando T-SQL, com exceção dos seguintes: CREATE PROCEDURE, CREATE DEFAULT, CREATE RULE, CREATE TRIGGER E CREATE VIEW;

  • Em um Stored Procedure podemos referenciar tabelas, Views, outras Stored Procedures e tabelas temporárias.

Vamos a um exemplo simples.
Exemplo: Criar um Stored Procedure que retorna todos os registros da tabela order, em que o campo ShipCountry é igual a London. Gravar o Stored Procedure com o nome usp_PedidosLondon. O mesmo será criado no Banco de Dados Northwind da instância SERVIDORSRVINST01.

USE Northwind

GO

CREATE PROCEDURE usp_PedidosLondon

AS

SELECT * FROM Orders WHERE ShipCity = 'London'

O comando é executado e a seguinte mensagem é exibida:
The command(s) completed successfully.
Agora podemos executar o Stored Procedure sempre que for necessário. Para executá-lo, execute o comando:

exec usp_PedidosLondos

O Stored Procedure é executado, os comandos que o compõem são executados e os resultados, retornados (somente os pedidos para London).
Criando Stored Procedures com parâmetros de Entrada
A utilização de parâmetros de entrada permite a criação de Stored Procedures mais flexíveis. Você pode inclusive criar Stored Procedures com comandos de Inserção (INSERT), Alteração (UPDATE) e Deleção (DELETE).
Vamos a um exemplo:
EXEMPLO: Se no case anterior, utilizasse-mos um parâmetro para determinar o País ao invés de fechar-mos a opção em um só.

USE Northwind

GO

CREATE PROCEDURE us_Pedidos_Pais

  @pais   nvarchar(15)

AS 

SELECT * FROM Orders WHERE ShipCity = @ pais

Para executar o Stored Procedure usp_Pedidos_Pais, passando Brazil como Parâmetro, utilizamos o seguinte comando:

Execute usp_Pedidos_pais "Brazil"

Percebam que nosso Stored Procedure se tornou bem mais flexível, agora podemos pesquisar por qualquer país dentro da tabela Orders, de acordo com o parâmetro passado.
Utilizando Estruturas de Decisão em um Stored Procedure
A linguagem que utilizamos para os comandos de um Stored Procedure é a linguagem T-SQL. A seguir vou apresentar as principais estruturas de controle disponíveis.
  • A Estrutura IF...ELSE

Este é o velho conhecido comando IF existente em qualquer linguagem:

IF teste_booleano

    Comandos_Se_Verdadeiro

Else

    Comandos_Se_Falso

Um Exemplo:

USE Northwind

GO

CREATE PROCEDURE usp_ExIF

  @pais1 nVarchar(15),

  @pais2 nVarchar(15),

AS

 /* CRIA AS VARIAVEIS LOCAIS */

DECLARE @TotPed1 int, @TotPed2 int

DECLARE @mensagem1 Char(100), @mensagem2 Char(100)

DECLARE @mensagem2 CHAR(100)

 /* DEFINE O VALOR DE CADA VARIÁVEL */

SET @TotPed1 = (SELECT Count(OrderID) FROM Orders WHERE ShipCountry=@pais1)

SET @TotPed2 = (SELECT Count(OrderID) FROM Orders WHERE ShipCountry=@pais2)

 /* EXECUTO O TESTE, UTILIZANDO IF...ELSE */

IF (@TotPed1) > (@TotPed2)

BEGIN

   SET @ mensagem1 = 'O Número de pedidos do primeiro país é maior'

   PRINT(@mensagem1)

END

ELSE

  IF (@TotPed1) < (@TotPed2)

  BEGIN

    SET @mensagem2 = 'O número de pedidos do segundo país é maior'

    PRINT(@mensagem2)

  END

  ELSE

  BEGIN

    SET @mensagem3 = 'O número de pedidos dos dois países é igual'

    PRINT(@mensagem3)

  END

A Estrutura WHILE...CONTINUE
Esta esturura faz com que um conjunto de comandos continue sendo executado, enquanto uma determinada condição for verdadeira. A sintaxe para este comando é a seguinte:

WHILE Teste

BEGIN

Comando1

Comando2

Comando3

...

Comando4

END

Vamos a um exemplo:

USE Northwind

GO

CREATE PROCEDURE usp_CalculaSoma

@numero int

AS

/*CRIA AS VARIÁVEIS PARA UM CONTROLE DE LAÇO

INICIALIZA A VARIÁVEL COM O VALOR 1 */

DECLARE @contador int

SET @soma=0

WHILE (@contador<=numero)

BEGIN

  SET @soma=@soma+@contador

  INSERT INTO SomaNaturais VALUES (@contador,@soma)

  SET @contador = @contador+1

END

Para executar esta Stored Procedure entre com o seguinte comando:

EXEC usp_CalculaSoma 10


É isso aí pessoal.
Qualquer dúvida mande um e-mail:markuvinicius@uol.com.br
Ao usuários da dica, por favor comentem para que eu possa continuar escrevendo com a melhor qualidade possível.

Boa Sorte
Marku Vinícius

 
Comentários:
Compartilhe com seus amigos:


Cadastre-se em nossa newsletter:

As melhores histórias em seu e-mail:

Novidades
» Veja todo o nosso arquivo
Compartilhe com seus amigos:





TOPO