Desvendando o SQL - Parte VII - Cursores

Hoje estamos novamente com vocês, para completar o assunto iniciado artigo passado, onde falamos de Instruções condicionais e de repetição. Na oportunidade mostramos o funcionamento do IF, CASE, quando tratamos laços condicionais, e do WHILE falando sobre laço de repetição. Mas existe outro laço de repetição que é o CURSOR, e sobre esse assunto que vamos tratar nesse artigo. Boa leitura.

Os cursores são considerados também um laço de repetição por sua característica de percorrer repetidamente o resultado de uma consulta, mas somente quando usando dentro deste um WHILE usando um @@FETCH_STATUS que caracteriza a sua repetição.

Sintaxe

DECLARE nome_cursor [ INSENSITIVE ] [ SCROLL ]  CURSOR
     FOR consulta
     [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
[;]

Transact-SQL Extended Syntax
DECLARE nome_cursor CURSOR [ LOCAL | GLOBAL ]
     [ FORWARD_ONLY | SCROLL ]
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
     [ TYPE_WARNING ]
     FOR select_statement
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]

Para melhor exemplificar o uso de cursores vamos criar mais duas tabelas em nosso banco de dados, uma de movimentos que vai registrar os movimentos diários e outras dos produtos que foram movimentados no dia. Caso tenha começado a ver o artigo agora, os scripts de criação de banco e tabela estão na parte II, bem como algumas alterações que fizemos no banco. Vamos por a mão na massa.

Primeiro a tabela de movimentos, vamos colocar um código, o tipo do movimento, o numero do documento, caso exista, o valor do movimento, a data/hora do movimento e o status. Segue o script.

--CATEGORIA DE PRODUTOS
CREATE TABLE TBMovimentoDiario
(
       CD_MOVIMENTO INT                        NOT NULL     identity,   
       TP_MOVIMENTO CHAR(1) CHECK (TP_MOVIMENTO IN ('E','S','T','D'))     NOT NULL,    --(E - ENTRADA, S - SAIDA, T - TRANSFERENCIA, D - DEVOLUCAO)
       NR_DOCUMENTO INT                        NULL,
       VR_MOVIMENTO DECIMAL(10,2) NOT NULL,
       DTH_MOVIMENTO DATETIME            NOT NULL,
       ST_MOVIMENTO CHAR(1)      CHECK (ST_MOVIMENTO IN ('A','F','C'))          NOT NULL     --(A - ABERTO, F - FECHADO, C - CANCELADO)
)


EXECUTE SP_ADDEXTENDEDPROPERTY 'MS_DESCRIPTION',
   'Tabela de Movimentos Diario',
   'USER', 'DBO', 'TABLE', 'TBMovimentoDiario'
GO

EXECUTE SP_ADDEXTENDEDPROPERTY 'MS_DESCRIPTION',
   'Tipo do Movimento. Valores Possíveis (
       E - ENTRADA, S - SAIDA, T - TRANSFERENCIA, D - DEVOLUCAO)',
   'USER', 'DBO', 'TABLE', 'TBMovimentoDiario', 'COLUMN', 'TP_MOVIMENTO'
GO

EXECUTE SP_ADDEXTENDEDPROPERTY 'MS_DESCRIPTION',
   'Status do Movimento. Valores Possíveis (
       A - ABERTO, F - FECHADO, C - CANCELADO)',
   'USER', 'DBO', 'TABLE', 'TBMovimentoDiario', 'COLUMN', 'ST_MOVIMENTO'
GO

ALTER TABLE TBMovimentoDiario
   ADD CONSTRAINT PK_MOVT PRIMARY KEY (CD_MOVIMENTO)
GO

Veja que nos campos do tipo de movimento e do status incluímos uma restrição, a opção CHECK verifica o valor do campo no momento da inserção e somente a inclusão dos valores especificados, gerando um erro em caso contrario isso ajuda em não deixa que seja incluído outros valores para o campo. E também incluímos uma descrição para os campos com restrição para facilitar o entendimento dos valores.

Agora vamos a tabela de produtos do movimento.  Nela somente teremos o código do movimento da tabela anterior, o código do produto e a quantidade de produtos. Colocamos o tipo dessa quantidade como DECIMAL(10,4) para atender aos diferentes tipos de unidades existentes.

--CATEGORIA DE PRODUTOS
CREATE TABLE TBMovimentoProduto
(
       CD_MOVPRODUTO INT                        NOT NULL     identity,   
       CD_MOVIMENTO INT                        NOT NULL,   
       CD_PRODUTO          INT                        NOT NULL,
       QTD_PRODUTO         DECIMAL(10,4) NOT NULL
)

EXECUTE SP_ADDEXTENDEDPROPERTY 'MS_DESCRIPTION',
   'Tabela de Produtos do Movimento Diario',
   'USER', 'DBO', 'TABLE', 'TBMovimentoProduto'
GO

ALTER TABLE TBMovimentoProduto
   ADD CONSTRAINT PK_MOVP PRIMARY KEY (CD_MOVPRODUTO)
GO

ALTER TABLE TBMovimentoProduto
   ADD CONSTRAINT FK_MOVT_MOVP FOREIGN KEY (CD_MOVIMENTO)
      REFERENCES TBMovimentoDiario (CD_MOVIMENTO)
GO

ALTER TABLE TBMovimentoProduto
   ADD CONSTRAINT FK_MOVT_PROD FOREIGN KEY (CD_PRODUTO)
      REFERENCES TBProduto (CD_PRODUTO)
GO

Agora vamos inserir alguns movimentos de forma aleatória, valor vai está zerado de propósito.

INSERT INTO TBMOVIMENTODIARIO (TP_MOVIMENTO, NR_DOCUMENTO, VR_MOVIMENTO, DTH_MOVIMENTO, ST_MOVIMENTO)
VALUES ('S',123,0,getdate(),'A')
INSERT INTO TBMOVIMENTODIARIO (TP_MOVIMENTO, NR_DOCUMENTO, VR_MOVIMENTO, DTH_MOVIMENTO, ST_MOVIMENTO)
VALUES ('S',124,0,getdate(),'A')
INSERT INTO TBMOVIMENTODIARIO (TP_MOVIMENTO, NR_DOCUMENTO, VR_MOVIMENTO, DTH_MOVIMENTO, ST_MOVIMENTO)
VALUES ('S',125,0,getdate(),'A')
INSERT INTO TBMOVIMENTODIARIO (TP_MOVIMENTO, NR_DOCUMENTO, VR_MOVIMENTO, DTH_MOVIMENTO, ST_MOVIMENTO)
VALUES ('E',123,0,getdate(),'F')
INSERT INTO TBMOVIMENTODIARIO (TP_MOVIMENTO, NR_DOCUMENTO, VR_MOVIMENTO, DTH_MOVIMENTO, ST_MOVIMENTO)
VALUES ('T',001,0,getdate(),'C')
INSERT INTO TBMOVIMENTODIARIO (TP_MOVIMENTO, NR_DOCUMENTO, VR_MOVIMENTO, DTH_MOVIMENTO, ST_MOVIMENTO)
VALUES ('T',002,0,getdate(),'F')
INSERT INTO TBMOVIMENTODIARIO (TP_MOVIMENTO, NR_DOCUMENTO, VR_MOVIMENTO, DTH_MOVIMENTO, ST_MOVIMENTO)
VALUES ('D',001,0,getdate(),'F')

Agora vamos começar a exemplificar a criação e execução de cursores. No começo de forma bem simples.

DECLARE
       @CD_CATEGORIA INT

DECLARE CURSOR_CATEGORIA CURSOR FOR
SELECT CD_CATEGORIA FROM TBCATEGORIA

OPEN CURSOR_CATEGORIA;
FETCH NEXT FROM CURSOR_CATEGORIA
INTO @CD_CATEGORIA

WHILE @@FETCH_STATUS = 0
BEGIN

       SELECT CD_SUB, DESC_SUB FROM TBSubCategoria WHERE CD_CATEGORIA = @CD_CATEGORIA

       FETCH NEXT FROM CURSOR_CATEGORIA
       INTO @CD_CATEGORIA
END
CLOSE CURSOR_CATEGORIA
DEALLOCATE CURSOR_CATEGORIA

O que fizemos foi percorrer todas as categorias e mostrar as subcategorias de acordo com o código da categoria informado.

Primeiro declaramos a variável DECLARE @CD_CATEGORIA INT , em seguida, declaramos o cursor, DECLARE CURSOR_CATEGORIA CURSOR FOR, e informando que consulta faria parte dele.  Em seguida abrimos o cursor vamos pra primeira posição e setamos a variável com o valor do código:

OPEN CURSOR_CATEGORIA;
FETCH NEXT FROM CURSOR_CATEGORIA
INTO @CD_CATEGORIA

Então começamos a repetição do cursor com o WHILE e por fim fechamos o cursor e desalocamos ele da memória:

WHILE @@FETCH_STATUS = 0
BEGIN

       SELECT CD_SUB, DESC_SUB FROM TBSubCategoria WHERE CD_CATEGORIA = @CD_CATEGORIA

       FETCH NEXT FROM CURSOR_CATEGORIA
       INTO @CD_CATEGORIA
END
CLOSE CURSOR_CATEGORIA
DEALLOCATE CURSOR_CATEGORIA

Este é um cursor bem simples, agora vamos complicar um pouco mais, vamos fazer o seguinte, criamos duas tabelas, mas somente carregamos uma delas e com valor igual a zero. O que vamos fazer é preencher a outra tabela de forma aleatória usando um cursor.

--DECLARANDO VARIAVEIS
DECLARE
       @CD_MOVIMENTO INT,
       @CD_PRODUTO         INT,
       @VLR_UNITARIO DECIMAL(10,2),
       @SOMAVALOR          DECIMAL(10,2)
      
SET @SOMAVALOR = 0
      
--DECLARANDO CURSOR DE MOVIMENTO
DECLARE CURSOR_MOV CURSOR FOR
SELECT CD_MOVIMENTO FROM TBMovimentoDiario

OPEN CURSOR_MOV;
FETCH NEXT FROM CURSOR_MOV
INTO @CD_MOVIMENTO

WHILE @@FETCH_STATUS = 0
BEGIN
      
       --DECLARANDO CURSOR DE PRODUTOS
       DECLARE CURSOR_PROD CURSOR FOR
       SELECT TOP 30 PERCENT P.CD_PRODUTO, E.VLR_UNITARIO
       FROM
             TBPRODUTO P INNER JOIN
             TBESTOQUE E ON E.CD_PRODUTO = P.CD_PRODUTO
       WHERE P.ST_PRODUTO = 'A'
      
       OPEN CURSOR_PROD
       FETCH NEXT FROM CURSOR_PROD
       INTO @CD_PRODUTO, @VLR_UNITARIO
      
       WHILE @@FETCH_STATUS = 0
       BEGIN

             --INSERINDO PRODUTOS DO MOVIMENTO
             INSERT INTO TBMovimentoProduto (CD_MOVIMENTO, CD_PRODUTO, QTD_PRODUTO)
             VALUES (@CD_MOVIMENTO, @CD_PRODUTO, RAND(10))
            
             SET @SOMAVALOR = @SOMAVALOR + @VLR_UNITARIO
            
             FETCH NEXT FROM CURSOR_PROD
             INTO @CD_PRODUTO, @VLR_UNITARIO
       END   
       CLOSE CURSOR_PROD
       DEALLOCATE CURSOR_PROD    
      
       --ATUALIZANDO O VALOR DO MOVIMENTO
       UPDATE TBMovimentoDiario
       SET VR_MOVIMENTO = @SOMAVALOR
       WHERE CD_MOVIMENTO = @CD_MOVIMENTO
                          
       SET @SOMAVALOR = 0
      
       FETCH NEXT FROM CURSOR_MOV
       INTO @CD_MOVIMENTO
END
CLOSE CURSOR_MOV
DEALLOCATE CURSOR_MOV

Ficou um pouco mais complicado, mas explicando da pra entender. Primeira coisa que fazemos é declarar algumas variáveis que utilizaremos dentro dos cursores, aninhamos dois cursores para fazer a inserção. Em seguida declaramos o primeiro cursor para os movimentos gerados e vamos guardar seus códigos. Dentro do laço declaramos outro cursor de produtos ativos e guardamos seu código e valor. Agora começamos o segundo laço WHILE, inserindo os produtos com o primeiro código de movimento selecionado, um dos produtos selecionado na consulta e um valor randômico entre 1 e 10 para quantidade. Acumulamos o valor do movimento em uma variável e assim é feito ate todos os produtos consultados serem lidos. Saímos do segundo laço, então atualizamos o valor em nossa tabela de movimento de acordo com o movimento e valor acumulado, zeramos a tabela e começa o processo novamente até todos os movimentos consultados serem lidos.

No final da execução as duas tabelas estarão preenchidas e devidamente atualizadas. Achou complicado? Acredito que não.

Grande abraço e até breve.

Artigos anteriores:

Related Posts Plugin for WordPress, Blogger...

Nenhum comentário: