Procedimento armazenado

Procedimento armazenado ou Stored Procedure é uma coleção de comandos em SQL, que podem ser executadas em um Banco de dados de uma só vez, como em uma função. Os procedimentos armazenados encapsulam tarefas repetitivas, aceitam parâmetros de entrada, são capazes de utilizar os comandos como IF e ELSE, WHILE, LOOP, REPEAT e CASE, além de poderem chamar outros procedimentos armazenados e retornam um valor de status (para indicar aceitação ou falha na execução).

Existem diversos usos para procedimentos armazenados,pois, dentro do procedimento podemos utilizar diversos tipos de comandos como INSERT, UPGRADE, DELETE, MERGE, DROP, CREATE e ALTER assim fornecendo um grande leque de utilidades para procedimentos armazenados.

Um procedimento armazenado também podem ser utilizado para validação de dados e controle de acesso.

Os procedimentos são como funções que serão guardadas no servidor, que podem ou não ser executadas através de um comando “EXEC [nome da procedure] ” (em seu caso sem a necessidade de parâmetros de entrada).

Por ser executada dentro do servidor, o trafego de dados existente na rede é drasticamente reduzido, pois, as únicas coisas que serão passadas pela rede são os valores dos parâmetros de entrada e o nome do procedimento assim otimizando o tempo de execução, diminuindo o uso da CPU e diminuindo a necessidade de memória. Além criar mecanismos de segurança entre a manipulação dos dados do Banco de Dados. Exemplo: (MS-SQL Server)

Create procedure busca
@nomedebusca varchar (50)
as
select nome1, nome2 
from nome_da_tabela
where nome = @nomedebusca

Características dos Procedimentos no MS-SQL Server

• Procedimentos do Sistema - Armazenadas no banco de dados Master, são identificadas com o prefixo sp_, executam tarefas administrativas, podem ser executadas em qualquer banco de dados.

• Procedimentos Locais - São criadas em bancos de dados do usuário.

• Procedimentos Temporários - Locais devem começar com #. Globais devem começar com ##.

• Procedimentos Remotos - Apenas por compatibilidade. No seu lugar se usa Queries distribuídas.

• Procedimentos Estendidos - São implementadas como DLL e executadas fora do ambiente do SQL Server. Identificadas com o prefixo xp_.

Procedimento armazenado utilizando Parâmetros

Podemos também, na stored procedured, utilizar parâmetros para que possamos ter mais utilidade.

Exemplo utilizando o Microsoft SQL Server:

-- Criar o procedimento
CREATE PROCEDURE Authors
AS SELECT primeiro_nome, ultimo_nome
FROM authors ORDER BY primeiro_nome ASC

-- Executar o procedimento
EXEC pAuthors
 
-- Deletar o procedimento
DROP PROCEDURE Authors

É possível criar uma stored procedured passando um ou mais parâmetros. Seguindo o exemplo acima, podemos fazer da seguinte maneira:

-- Criar o procedimento 
CREATE PROCEDURE Authors @cidade varchar(50), @estado varchar(25)
AS SELECT primeiro_nome, ultimo_nome 
FROM authors 
WHERE authors.cidade = @cidade
AND authors.estado = @estado
ORDER BY primeiro_nome ASC 
  
-- Executar o procedimento 
EXEC Authors @cidade = 'Recife', @estado = 'Pernambuco';
  
-- Deletar o procedimento 
DROP PROCEDURE Authors

Outros usos

Em alguns sistemas, os procedimentos armazenados podem ser usados para controlar o gerenciamento de transações; em outros, procedimentos armazenados são executados dentro de uma transação de forma que as transações sejam efetivamente transparentes para eles. Os procedimentos armazenados também podem ser chamados de um Gatilho ou de um tratador de condição. Por exemplo, um procedimento armazenado pode ser disparado por uma inserção em uma tabela específica, ou atualização de um campo específico em uma tabela, e o código dentro do procedimento armazenado seria executado. Escrever procedimentos armazenados como manipuladores de condição também permite que os administradores de banco de dados rastreiem erros no sistema com mais detalhes usando procedimentos armazenados para capturar os erros e registrar algumas informações de auditoria no banco de dados ou um recurso externo como um arquivo.

Vantagens e Desvantagens

Principais Vantagens de usar uma stored procedured:

• Melhoria na performance, já que terá uma menor quantidade de trânsito entre redes;
• Pode ser compartilhado entre as aplicações;
• Portabilidade;

Principal Desvantagem de usar uma stored procedured:

Se utilizarmos uma stored procedured podemos ficar bastante dependente da base de dados, com isso, se precisássemos mudar de base por algum motivo, iriamos ter que reescrever todas as storeds procedureds, o que ocasionaria em um grande custo de tempo. Existe ferramentas que consegue fazer uma migração como essa, entretanto nem sempre é funcional.

Comparando Procedimentos Armazenados com Funções

• Funções são subprogramas escritos para realizar uma determinada tarefa enquanto procedimentos armazenados são sub-rotinas utilizadas para evitar grande repetição de consultas.
• Normalmente funções retornam apenas um único resultado ou NULL enquanto procedimentos podem retornar uma grande quantidade de resultados o parâmetro OUT ou simplesmente não retornar nada.
• Funções precisam retornar valores utilizando a palavra chave RETURN enquanto nos procedimentos armazenados isso não é necessário.
• Um procedimento armazenado pode utilizar a palavra chave RETURN sem passar nenhum parâmetro de retorno.
• O procedimento armazenado guarda a consulta em tempo de compilação.
• O procedimento armazenado é um objeto do banco de dados e também pode ser utilizado como material para criação de outro objeto no banco de dados.

Referência

• Imasters
• Devmedia
• Wikipedia English

Ver também

• Regras
• Gatilho
• Default
• Visão
• Generalizadores
• Tabelas