Cursores - exemplo básico de utilização

Cursores são estruturas da linguagem T-SQL que permitem o processamento das linhas retornadas por uma consulta (SELECT), através de estruturas complexas de programação, como repetições ou  comandos condicionais.

O exemplo básico de cursor consiste em uma repetição (loop) onde um mesmo conjunto de comandos é executado para todas as linhas do retorno de uma consulta.
No exemplo abaixo, utiliza-se um cursor para executar a chamada da procedure sp_helptext para cada procedure de um banco de dados. As procedures foram obtidas através de uma consulta na view ROUTINES do Schema INFORMATION SCHEMA.

DECLARE @schemaName VARCHAR(30)
    , @procName VARCHAR(30)
    , @fullName VARCHAR(60)

-- Cursor para percorrer os nomes dos objetos
DECLARE cursor_objects CURSOR FOR
    SELECT
          ROUTINE_SCHEMA
        , ROUTINE_NAME
    FROM
        INFORMATION_SCHEMA.ROUTINES
    WHERE
        ROUTINE_TYPE = 'PROCEDURE'

-- Abrindo Cursor para leitura
OPEN cursor_objects

-- Lendo a próxima linha
FETCH NEXT FROM cursor_objects INTO @schemaName, @procName

-- Percorrendo linhas do cursor (enquanto houverem)
WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT @fullName = @schemaName + '.' + @procName

    EXEC sp_helptext @fullName

    -- Lendo a próxima linha
    FETCH NEXT FROM cursor_objects INTO @schemaName, @procName
END

-- Fechando Cursor para leitura
CLOSE cursor_objects

-- Desalocando o cursor
DEALLOCATE cursor_objects 

Algumas considerações:

• Um cursor deve estar sempre associado a uma consulta, especificada ao declarar o cursor.

• O comando FETCH popula as variáveis recebidas como parâmetro com os valores da próxima linha da consulta a ser lida. O número de variáveis passadas como parâmetro deve ser igual ao número de colunas retornadas na consulta associada ao cursor.

• A variável global @@FETCH_STATUS retorna o resultado da última operação FETCH executada por um cursor na conexão.
  O status 0 significa que o comando FETCH retornou uma linha, qualquer outro resultado significa que não houve linha retornada.

• Cursores são estruturas relativamente lentas se comparadas ao desempenho de consultas do banco. O uso descuidado dessa ferramenta pode causar sérios problemas de performance.

Atualizando registros através de junções de tabelas

As aplicações corporativas inserem, atualizam e excluem milhares e até milhões de registros todos os dias atavés da execução de comandos SQL na base de dados.

A atualização de registros (UPDATE) é uma tarefa normal e muito simples na maioria dos bancos de dados atuais, principalmente porque normalmente deseja-se atualizar os registros de uma única tabela.

Existem situações onde precisamos atualizar registros seguindo critérios mais elaborados, não apenas filtrando as linhas da tabela, mas também as linhas que participam de uma determinada junção entre tabelas.

Para realizar uma operação deste tipo, pode-se utilizar a seguinte sintaxe para o comando de update:

    UPDATE
        Tabela1
    SET
        campoX =  valorX
    FROM
        Tabela1 T1
    INNER JOIN Tabela2 T2 ON
        T1.id = T2.fk_id
    WHERE 

       T1.campo1 = valor1

       and T2.campo2 = valor2

Para ilustrar, vejamos um exemplo baseado na base de dados AdventureWorks2008:

No modelo acima, temos a relação dos produtos gerenciados na base com as suas categorias e sub-categorias. As tabelas Product, ProductSubCategory e ProductCategory, todas pertencentes ao Schema Production, estabelecem esta relação.

Imagine que todos as bicicletas (produtos pertencentes à categoria "Bikes") tenham sofrido um reajuste de preço 20% no preço final ao consumidor e seja necessário realizar esta atualização diretamente na base de dados.

Observe que não é possível realizar esta operação apenas adicionando filtros à cláusula WHERE, a não ser que se tenha conhecimento de cada um dos identificadores das sub-categorias da categoria "Bikes", o que seria muito trabalhoso.

Para realizar esta tarefa, utilizaremos a estrutura citada acima, unindo as 3 tabelas em questão, atualizando apenas o campo ListPrice da tabela Product dos registros cuja subcategoria pertença à categoria "Bikes".

Veja o comando a ser executado:

    UPDATE
        Production.Product
    SET
        ListPrice = ListPrice * 1.2
    FROM
        Production.Product P
    INNER JOIN Production.ProductSubcategory S ON
        P.ProductSubcategoryID = S.ProductSubcategoryID
    INNER JOIN Production.ProductCategory C ON
        S.ProductCategoryID = C.ProductCategoryID
    WHERE C.Name = 'Bikes'

Dica: Pode-se observar que a sintaxe empregada é muito parecida com a de uma consulta utilizando junções. Para evitar maiores problemas, sugerimos sempre a construção da consulta para testes dos registros afetados antes da execução da atualização desejada.

Passando uma lista de valores como parâmetro para uma Stored Procedure

As Stored Procedures podem aumentar consideravelmente a complexidade das pesquisas realizadas por aplicações em uma base de dados. 

O uso de parâmetros é uma das vantagens fornecidas pelas Stored Procedures. Através dos parâmetros, podemos tornar o filtro de uma consulta mais ou menos abrangente.

Em alguns casos, porém, pode ser necessário passar uma lista com tamanho indefinido de valores como parâmetro de filtro para uma pesquisa. Infelizmente, no SQL Server 2008 não é possível criar Stored Procedures com número de parâmetros variável, e não existem tipos de dados semelhantes aos vetores utilizados nas linguagens de programação convencionais.

Existem diversas formas de solucionar este problema. Uma das formas mais flexíveis de contornar esta situação é o uso de parâmetros do tipo XML.

Vamos utilizar um exemplo para entender como resolver esta situação. Utilizaremos como fonte de dados a tabela temporária Pessoa que deve conter o nome da pessoa e um código identificador (ID). Segue abaixo o código para criação da tabela:

/* Criando tabela Pessoa */
CREATE TABLE Pessoa
(
    ID int identity (1,1)
    , nome varchar(100)
)

/* Inserindo valores na tabela Pessoa */
Insert into Pessoa Values ('Evandro'),('Ronaldo'),('Rita'),('Mariana'),('Augusto')

Em seguida, criaremos uma Stored Procedure para selecionar registros desta tabela utilizando uma lista de IDs como filtro na forma de um parâmetro do tipo XML. O formato de nosso parâmetro será o seguinte:

   <id> 1 </id>
   <id> 2 </id>
          ....

No trecho abaixo, podemos verificar o código da procedure já realizando o filtro na tabela Pessoa através da lista de IDs:

/* Criando Procedure para filtro de pessoas através de uma lista de IDs */
CREATE PROCEDURE PROC_selectPessoa
    @filtroXML XML
AS
    SELECT P.ID, Nome FROM Pessoa P
    INNER JOIN   
        (
              SELECT
                   ParamValues.ID.value('.','int') as ID    
             FROM
                   @filtroXML.nodes('/id') as ParamValues(ID)
        ) F
    ON P.id = F.id
GO

Depois de criar a Stored Procedure, podemos simular a chamada realizada por uma aplicação, criando uma variável do tipo XML.

/* Simulando chamada realizada pela aplicação, passando os ids 1 e 5 */
DECLARE @parametros XML
SELECT  @parametros = '<id> 1 </id> <id> 5 </id>'
EXEC PROC_selectPessoa @parametros

Ao executar o script proposto acima, teremos o seguinte resultado:

ID	Nome
1	Evandro
5	Augusto

Na figura abaixo, podemos ver nosso script em execução:

Para maiores informações sobre o tipo de dados XML, verifique outros artigos sobre o tema:

• Introdução ao tipo de dados XML - Parte 1
• Introdução ao tipo de dados XML - Parte 2

[off-topic] Setup de aplicações .Net Framework 3.5 no VS2010

Um dos problemas que se tem encotrado ao migrar do Visual Studio 2008 para o Visual Studio 2010 (VS2010) foi a questão do Projeto Setup para as aplicações antigas.

Ao gerar o pacote de instalação de uma aplicação, o VS2010 adiciona automaticamente o “.Net Framework 4.0” como um dos pré-requisitos para a execução do instalador. Na máquina do desenvolvedor, isso não é problema, uma vez que este já possui esta versão do Framework instalada (juntamente com o VS2010).

Em ambientes de produção, porém, muitas empresas ainda trabalham com a versão 3.5 do Framework e o processo de migração pode ser demorado. Sendo assim, os setups gerados com esse pré-requisito não serão executados.

Para contornar esta situação, será necessário fazer as seguintes modificações no projeto Setup:

1 - Retirar o “Framework 4.0” dos pré-requisitos para instalação

Abrir as propriedades do projeto (clicando com o botão direito no nome do projeto e depois em “Propriedades”).

A seguinte janela será aberta:

Em seguida, clicar no botão “Prerequisites...”, para selecionar os pré-requisitos de instalação da aplicação, como na janela abaixo:

Nesta janela, será necessário desmarcar as opções relacionadas ao “Framework 4.0” e selecionar o “Framework 3.5”, como demonstrado.  

2-Retirar o “Framework 4.0” das condições para execução do setup.

Clicar com o direito no nome do projeto, Selecionar a opção “View” e em seguida a opção “Launch Conditions” 

As condições de execução do setup serão exibidas. Nesta etapa, selecione o item “.Net Framework” dentro da pasta “Launch Conditions”. Em seguida, exiba suas propriedades (pressionando a tecla F4).

Na janela de propriedades, selecione a versão correta do Framework.

Após estas alterações, será possível gerar o setup através do Visual Studio 2010 e executá-lo em um ambiente onde o .Net Framework 4.0 ainda não foi instalado.