Desvendando o SQL - Parte IX - Procedimentos Armazenados
Hoje vamos falar um pouco sobre
STORED PROCEDURES ou Procedimentos Armazenados, sua utilidade e como usá-lo,
aproveitem bem e boa leitura.
PROCEDIMENTOS ARMAZENADOS
Para os que conhecem programação PROCEDIMENTOS ARMAZENADOS funcionam semelhantes a procedimentos como em qualquer linguagem. Para aqueles que não conhecimentos nos permitem:
- Colocar parâmetros de entrada e saída;
- Conter instruções que podem executar algumas operações no banco;
- Retornar um status de execução;
Com uma STORED PROCEDURE podemos, por exemplo, retornar o valor de uma consulta de acordo com os parâmetros passados, chamar outros procedimentos, inserir valores em tabelas de acordo com determinada situação, dentro outras funcionalidades.
Diretrizes para Procedimentos Armazenados
Alguns pontos devem ser seguidos,
comecemos pela sintaxe de criação, que segue o mesmo padrão dos anteriores
objetos criados no banco, vejamos com criar uma PROCEDURE, lembre que depois de
criada, não é possível criar outra com o mesmo nome, portanto caso precisa
fazer alguma alteração é só trocar o CREATE pelo ALTER.
CREATE { PROC |
PROCEDURE }
<lista
de parâmetros>
@PARAM1 TIPO,
@PARAM2
TIPO
[
= default ] [ OUT | OUTPUT | [READONLY]
AS
BEGIN
END
GO
Nome
Lembre-se de utilizar um nome
funcional para procedure, por padrão vamos utilizar um prefixo “PR” seguido de
nome relativo à funcionalidade da Procedure.
Parâmetros
A lista de parâmetros segue
também determinadas regras a serem seguidas.
Sempre utilize no começo do
parâmetro o caractere ‘@’ seguido do nome escolhido;
Todos os tipos de dados
Transact-SQL podem ser usados como parâmetros (Desvendando
o SQL - Parte II);
Um valor Default por ser usado para definir o parâmetro, inclusive valor NULL, isso permite que mesmo que o parâmetro não seja passado à execução da procedure é possível;
OUT ou OUTPUT indica que o parâmetro é de saída, ou seja, será devolvido ao final da execução da procedure. Lembrando que parâmetros do tipo text, ntext e image não podem ser usados com OUTPUT;
Parâmetros marcados como READONLY não podem ser atualizados ou modificados no corpo do procedimento. Se o tipo de parâmetro for um tipo com valor de tabela, deverá ser especificado READONLY;
O BEGIN (Inicio) e END (Fim) limitam o corpo da procedure, como também as instruções que estiver dentro do corpo podem ser limitadas pelo BEGIN/END;
Algumas práticas são recomendadas para a PROCEDURE e para não deixar o artigo muito extenso sugerimos consultar a página do MSDN.
Limitações
Apesar da extensa gama de instruções
que podem ser utilizadas no corpo da procedure, alguns comandos são restritos e
não podem ser usados. Vejam abaixo:
CREATE AGGREGATE
|
CREATE SCHEMA
|
SET SHOWPLAN_TEXT
|
CREATE DEFAULT
|
CREATE ou ALTER TRIGGER
|
SET SHOWPLAN_XML
|
CREATE ou ALTER FUNCTION
|
CREATE ou ALTER VIEW
|
USE database_name
|
CREATE ou ALTER PROCEDURE
|
SET PARSEONLY
|
CREATE RULE
|
SET SHOWPLAN_ALL
|
Uma PROCEDURE pode fazer referencia a tabelas não criadas, porque no momento da criação somente a sintaxe é verificada, somente no momento da execução os objetos referenciados são resolvidos.
Segurança
Como estamos trabalhando com um usuário com permissões de banco não veremos a dificuldade na execução da PROCEDURE, mas com um banco com vários usuários as procedures somente serão executadas com permissão.
Execução
Para uma procedure ser executada colocamos os comandos EXECUTE ou EXEC antes do nome da PROCEDURE:
EXECUTE
GO
-- Or
EXEC
GO
EXEMPLOS
Mostraremos agora alguns exemplos
de criação e execução de procedures, a princípio sem parâmetros e em seguida
vamos incluindo alguns parâmetros e algumas outras opções de acordo com o que
já vimos nos artigos anteriores.
Criando um procedimento simples;
CREATE PROCEDURE PRProduto
AS
BEGIN
SELECT CD_REFERENCIA, DESC_PRODUTO, ST_PRODUTO, NR_SERIE FROM TBPRODUTO
END
GO
Para executar é só digitar o
comando abaixo:
EXECUTE PRProduto
Mais de um conjunto de resultados;
CREATE PROCEDURE PRProdutoCategoria
AS
BEGIN
SELECT CD_REFERENCIA, DESC_PRODUTO,
ST_PRODUTO, NR_SERIE FROM TBPRODUTO
SELECT P.DESC_PRODUTO, C.DESC_CATEGORIA, S.DESC_SUB
FROM
TBPRODUTO AS P INNER JOIN
TBSUBCATEGORIA AS S ON S.CD_SUB = P.CD_SUB INNER JOIN
TBCATEGORIA AS C ON C.CD_CATEGORIA = S.CD_CATEGORIA
WHERE
C.DESC_CATEGORIA = 'BEBIDAS'
END
GO
Relatório de produtos. Lembra quando fizemos um cursor que mostrava um relatório com os produtos dos movimentos fechados? Agora vamos criar uma procedure para executar esse relatório sempre que necessário, só que agora passando alguns parâmetros.
CREATE PROCEDURE PRRelatProdMovimento (@ST_MOV CHAR(1), @DT_INIMOV DATETIME, @DT_FINMOV DATETIME)
AS
BEGIN
--O EXEMPLO A
SEGUIR MOSTRA COMO CURSORES PODEM SER ANINHADOS PARA PRODUZIR RELATÓRIOS
COMPLEXOS. O CURSOR INTERNO É DECLARADO PARA CADA PRODUTO.
SET NOCOUNT ON;
DECLARE
@CD_MOVIMENTO INT,
@DESC_PRODUTO VARCHAR(50),
@MENSAGEM VARCHAR(200),
@TP_MOVIMENTO CHAR(1),
@NR_DOCUMENTO INT,
@QTD DECIMAL(10,4),
@UNIDADE VARCHAR(5)
PRINT '-------- RELATÓRIO DE PRODUTOS DO MOVIMENTO --------';
DECLARE
MOV_CURSOR CURSOR FOR
SELECT
CD_MOVIMENTO, TP_MOVIMENTO, NR_DOCUMENTO
FROM TBMOVIMENTODIARIO
WHERE
ST_MOVIMENTO
= @ST_MOV AND
DTH_MOVIMENTO
BETWEEN @DT_INIMOV AND
@DT_FINMOV
ORDER BY TP_MOVIMENTO;
OPEN MOV_CURSOR
FETCH NEXT FROM MOV_CURSOR
INTO @CD_MOVIMENTO,
@TP_MOVIMENTO, @NR_DOCUMENTO
WHILE @@FETCH_STATUS
= 0
BEGIN
PRINT ' '
SELECT @MENSAGEM = '----- PRODUTOS DO
MOVIMENTO: ' +
@NR_DOCUMENTO + ' - ' +
CASE
@TP_MOVIMENTO --(E - ENTRADA, S - SAIDA, T -
TRANSFERENCIA, D - DEVOLUCAO)
WHEN 'E'
THEN 'ENTRADA'
WHEN 'S' THEN 'SAÍDA'
WHEN 'T' THEN 'TRANSFERÊNCIA'
WHEN 'D' THEN 'DEVOLUÇÃO'
END
PRINT @MENSAGEM
-- DECLARE AN INNER CURSOR BASED
-- ON VENDOR_ID FROM THE OUTER CURSOR.
DECLARE PROD_CURSOR CURSOR FOR
SELECT
P.DESC_PRODUTO,
M.QTD_PRODUTO,
U.SGL_UNIDADE
FROM
TBMOVIMENTOPRODUTO
M INNER JOIN
TBPRODUTO
P ON P.CD_PRODUTO
= M.CD_PRODUTO INNER JOIN
TBUNIDADE U ON U.CD_UNIDADE = M.CD_UNIDADE
WHERE
M.CD_MOVIMENTO =
@CD_MOVIMENTO
OPEN
PROD_CURSOR
FETCH NEXT
FROM PROD_CURSOR
INTO @DESC_PRODUTO,
@QTD, @UNIDADE
IF @@FETCH_STATUS <> 0
PRINT ' <>'
WHILE @@FETCH_STATUS
= 0
BEGIN
SELECT @MENSAGEM = ' ' + @DESC_PRODUTO + ' - ' + CAST(@QTD AS VARCHAR(10)) + '-' + @UNIDADE
PRINT @MENSAGEM
FETCH NEXT FROM PROD_CURSOR
INTO @DESC_PRODUTO,
@QTD, @UNIDADE
END
CLOSE PROD_CURSOR
DEALLOCATE PROD_CURSOR
-- GET THE NEXT VENDOR.
FETCH NEXT FROM MOV_CURSOR
INTO @CD_MOVIMENTO,
@TP_MOVIMENTO, @NR_DOCUMENTO
END
CLOSE MOV_CURSOR;
DEALLOCATE MOV_CURSOR;
END
Essa é somente uma das muitas
utilidades das Procedures, é bem interessante quando temos processos pesados e
queremos tirar isso de nossa aplicação e jogá-la no servidor. Nem sempre
precisando devolver um resultado, podemos usá-la somente pra executar
procedimentos.
Tente também fazer outras
procedures com outros processamentos que deseje, e caso tenha alguma dúvida, é
só comentar que terei o prazer de responder.
No próximo artigo falaremos um
pouco sobre as FUNCTION ou Funções no SQL. Não perca.
Até a próxima.
Comentários