Desvendando o SQL - Parte IX - Procedimentos Armazenados

Galera Anapropéguas, nossa série de artigos está de volta. Esta já é a nona parte, as anteriores mostraram um pouco de alguns pontos da linguagem SQL, vimos operadores, funções de agregação, criação de banco, tabelas e views, também vimos à alteração de estruturas de tabelas, tipos de campo, consultas, estruturas condicionais e de repetição, cursores e também conversão de valores. Procurei explanar até agora, se não tudo, o que achei mais interessante para aqueles que ainda não conheciam a linguagem SQL. Se por acaso ainda não viram os outros artigos, disponibilizo os links no final desse artigo.

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 <@Param1, @Param2,...>
GO

-- Or

EXEC <@Param1, @Param2,...>
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

Postagens mais visitadas