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:

Desvendando o SQL - Parte I - Introdução
Desvendando o SQL - Parte II - Criando um Banco de Dados e Tabelas

Desvendando o SQL - Parte III - Alterando Registros

Desvendando o SQL - Parte IV - Consultas

Desvendando o SQL - Parte V - Visões

Desvendando o SQL - Parte VI - Estruturas Condicionais e de Repetição