Olá pessoal. Na coluna desta semana vou falar sobre como empacotar dados em uma lista de valores separados por algum caractere, e também como desempacotar valores contidos em uma lista em várias linhas. Como veremos, este empacotamento de dados é muito útil para que uma determinada aplicação envie um conjunto de valores de tamanho variável como um parâmetro para uma stored procedure do banco de dados. Desempacotar os valores é igualmente útil, principalmente quando desejamos fazer algum tipo de agregação de acordo com uma coluna da tabela.

Veremos primeiro como empacotar os valores. Como sabemos, o SQL Server 2000 possui somente suporte à estrutura de tabela, e não há como utilizar diretamente estruturas como arrays, lista, pilhas etc. Pois bem, isso é um problema quando desejamos passar como parâmetro para uma stored procedure um conjunto de dados, cuja quantidade de valores não sabemos durante o tempo de desenvolvimento. Por exemplo, vamos supor que desejamos enviar como parâmetro para uma stored procedure uma lista de valores que serão utilizados na cláusula IN() de uma instrução SELECT contida em uma stored procedure. Se utilizarmos o código apresentado na Listagem 1, onde simplesmente colocamos a variável que recebe o valor do parâmetro na cláusula IN(), não conseguiremos retornar os dados desejados. A tabela ORDERS da base de dados Northwind do SQL Server 2000 foi utilizada no exemplo da Listagem 1.

Listagem 1. Stored Procedure para retornar os pedidos

Existem várias abordagens para resolver este problema. Uma delas é utilizar instruções dinâmicas para montar a instrução SELECT a ser executada com a função EXECUTE(). Porém, neste artigo veremos como fazer para desempacotar os valores contidos em uma lista separados por um caractere, e resolver esta questão sem utilizar execução dinâmica, tornando o código mais fácil de ser implementado em outros servidores de bancos de dados, além de evitar os detalhes de desempenho e segurança relacionados à execução dinâmica.

Para desempacotar os dados de modo que o SQL Server consiga tratá-los em uma query, vamos utilizar uma UDF (User Defined Function), que receberá a lista de parâmetros com o tipo de dados TEXT e um caractere separador dos dados. Esta UDF retornará um dos valores da lista em linhas, como se pertencessem a uma tabela. Utilizei uma UDF criada pelo MVP (Most Valued Professional) americano em SQL Server chamado Erland Sommarskog, que escreveu um excelente artigo sobre esse assunto chamado “Arrays and Lists in SQL Server”. Este artigo pode ser lido por meio do link www.sommarskog.se/arrays-in-sql.html

Utilizarei a função iter_charlist_to_table. Recomendo aos leitores mais interessados em saber detalhes sobre esta função, como o código-fonte e informações relacionadas ao desempenho, que dêem uma olhada no artigo citado. A Listagem 2 apresenta dois exemplos de como a função pode ser chamada para desempacotar a lista de valores junto com os respectivos resultados gerados pelas chamadas à função.

Listagem 2. Desempacotando os dados com a UDF iter_charlist_to_table.

Notem que na Listagem 2, a função retorna três colunas: a primeira, chamada listpos, traz a posição do dado na lista de valores (da esquerda para a direita), a segunda, chamada str, traz os dados convertidos para char, e a terceira, chamada nstr, traz os dados convertidos para nchar, que é tipo de dados UNICODE.

Pois bem, uma vez que conhecemos como esta função trabalha, basta aplicá-la na stored procedure ST_LISTA_PEDIDOS. Utilizaremos esta função na cláusula FROM da instrução SELECT, pois ela retorna uma tabela. Nesta instrução iremos utilizar um INNER JOIN relacionando a coluna nstr da tabela retornada pela função com a coluna ORDERID da tabela ORDERS, com o objetivo de retornar somente os pedidos cujos IDs foram passados como parâmetro. A listagem 3 apresenta o código-fonte final da stored procedure ST_LISTA_PEDIDOS.

Listagem 3. Desempacotando os dados corretamente na procedure ST_LISTA_PEDIDOS.

Até agora, vimos como desempacotar os dados. E se quisermos empacotar os dados contidos nas linhas de uma tabela para gerar a lista de dados separados por vírgula? Para isso, criaremos uma outra UDF.

Como exemplo, os dados da Tabela 1 foram obtidos através de uma instrução SELECT que utilizou joins para obter os dados de quais encomendas foram enviadas para quais estados, proveniente de um sistema de logística. Os dados deste resultado foram armazenados em uma tabela chamada TB_ENCOMENDAS.

COD_ENCOMENDA	DESC_ENCOMENDA	UF_ENCOMENDA
ENV01	Produto X	MG
ENV02	Produto Y	SP
ENV03	Produto X	SP
ENV04	Produto Z	MG
ENV05	Produto Y	RJ
ENV06	Produto Z	BA
ENV07	Produto W	RS

Tabela 1. Dados sobre envio de encomendas.

Um relatório foi solicitado pedindo que as encomendas enviadas sejam agrupadas, e que todos os estados para os quais elas foram enviados sejam colocados na frente das encomendas. Para gerar este relatório, devemos agrupar os dados por descrição da encomenda, e empacotar o nome dos estados em uma lista que manterá os valores separados por vírgula. Infelizmente, o padrão SQL, e muitos outros bancos de dados, não apresentam nenhuma função de agregação que concatena informações da maneira que necessitamos neste relatório.

Para elaborar este relatório, vamos criar uma UDF que será utilizada junto com a função de agregação MAX. Esta função vai receber como parâmetro a descrição da encomenda, e retornar uma string com os estados, separados os estados por vírgula, para onde esta encomenda foi levada.

A Listagem 4 mostra a criação da função de usuário F_AGRUPA. A Tabela 2 mostra o resultado da instrução SELECT que utiliza a função F_AGRUPA e agrega os dados.

Listagem 4. Função F_AGRUPA e instrução SELECT que agrupa os dados.

DESC_ENCOMENDA	UF_ENVIADAS
Produto W	RS
Produto X	MG,SP
Produto Y	SP,RJ
Produto Z	MG,BA

Tabela 2. Relatório de encomendas.

Infelizmente, a função F_AGRUPA não é tão genérica quanto a função iter_charlist_to_table, ou seja, para cada agrupamento dos dados esta função deve ser re-escrita com o objetivo de se adequar às colunas da tabela envolvida.

Utilizando as UDFs descritas nesta coluna, facilmente poderemos empacotar e desempacotar os dados. Este tipo de manipulação de dados é muito útil para quem encontra situações onde a quantidade de dados é dinâmica. Como o SQL Server, e muitos outros gerenciados de bancos de dados, não possuem suporte para estruturas de dados avançadas, como arrays, listas, pilhas, etc, o uso das UDFs apresentadas nesta coluna pode facilitar o desenvolvimento de certas aplicações. Para fazer o download dos códigos-fonte utilizados neste artigo clique aqui.

Por hoje é só pessoal, até a semana que vem!