Linguagem SQL para geração de relatórios - Revista SQL Magazine 97 - Parte 4

Natal DevMedia: Assine hoje e ganhe 3 meses gr�tis

Demais posts desta s�rie:
Usando linguagem SQL para gera��o de relat�rios - Parte 1
Usando linguagem SQL para gera��o de relat�rios - Parte 2
Usando linguagem SQL para gera��o de relat�rios - Parte 3
Usando linguagem SQL para gera��o de relat�rios - Parte 5

Artigo no estilo: Curso

De que se trata o artigo?

Uso Common Table Expressions (CTE) para cria��o de relat�rios. S�o descritos os dois tipos de CTEs: as convencionais e as recursivas. Sendo assim, o artigo apresenta aos leitores iniciantes e avan�ados possibilidades oferecidas por CTEs, que s�o a cria��o de conjuntos de dados tempor�rios e tamb�m a defini��o de rotinas recursivas.

Em que situa��o o tema � �til?

Cria��o de relat�rios que envolvam l�gicas muito extensas e/ou que requeiram recursos de recursividade.

Resumo DevMan

Em muitos momentos durante o desenvolvimento de aplica��es que acessam o banco de dados nos deparamos com situa��es em que consultas SQL bastante complexas para a constru��o de relat�rios se fazem necess�rias. C�digos longos e que podem gerar confus�es durante a sua leitura s�o comuns.

� para essas situa��es que utilizamos o recurso conhecido como CTE � Common Table Expressions. Uma CTE � um recurso implementado no padr�o ANSI SQL e, por consequ�ncia, aceito em praticamente todos os sistemas de gerenciamento de banco de dados.

Neste artigo veremos como implementar uma CTE e tamb�m faremos uma an�lise da performance de sua utiliza��o. Veremos tamb�m como implementar CTE para consultas recursivas e tamb�m analisaremos sua performance.

Estamos de volta com o quarto artigo da s�rie sobre cria��o de relat�rios. Desta vez, vamos falar de express�es de tabela, definidas como Common Table Expressions (CTE), um recurso estabelecido pelo padr�o ANSI SQL (ver Nota DevMan 1) e que nos dias atuais est� dispon�vel em praticamente qualquer sistema gerenciador de banco de dados (SGBD).

CTEs s�o bastante �teis principalmente por simplificarem a leitura de consultas complexas, como veremos a seguir. Mas as CTEs oferecem tamb�m uma funcionalidade �mpar: permite cria��o de l�gicas recursivas dentro do SQL! Este artigo apresenta os conceitos, as sintaxes e avalia o uso destes recursos.

Nota DevMan 1: ANSI SQL

SQL � a abrevia��o para Structured Query Language, ou Linguagem Estruturada de Consultas. � uma linguagem de programa��o projetada para gerenciar dados em sistemas de gerenciamento de banco de dados relacional (SGBDR, do ingl�s RDBMS - Relational Database Management System).

Originalmente baseado em �lgebra relacional e c�lculo relacional de tuplas, o seu �mbito inclui inserir, consultar, atualizar e excluir dados, cria��o e modifica��o de esquema e controle de acesso a dados. SQL foi uma das primeiras linguagens comerciais para o modelo relacional idealizado por Edgar Frank Codd, como descrito em seu artigo datado de 1970 "Um Modelo Relacional de Dados para os grandes bancos de dados compartilhados" ("A Relational Model of Data for Large Shared Data Banks").

SQL se tornou um padr�o da American National Standards Institute (ANSI) em 1986, e da International Organization for Standards (ISO) em 1987. Desde ent�o, o padr�o tem sido refor�ado constantemente com recursos adicionais. No entanto, quest�es de portabilidade de c�digo SQL entre os principais SGBDRs ainda existem devido � falta de cumprimento integral, ou diferentes interpreta��es da norma. Entre as raz�es mencionadas s�o o enorme tamanho e especifica��o incompleta da norma.

SQL foi desenvolvido inicialmente na IBM por Donald D. Chamberlin e Raymond F. Boyce no in�cio de 1970. Esta vers�o, chamada inicialmente de SEQUEL (Structured English Query Language - Linguagem Estruturada de Consultas em Ingl�s), foi projetada para manipular e recuperar dados armazenados no sistema de gerenciamento de banco de dados "quase" relacional da IBM, o System R, que um grupo do laborat�rio de pesquisas da IBM em San Jose tinha desenvolvido durante a d�cada de 1970. A sigla SEQUEL foi posteriormente alterada para SQL porque "SEQUEL" era uma marca de uma empresa de aeronaves sediada no Reino Unido.

O primeiro Sistema de Gerenciamento de Banco de Dados Relacional foi o RDMS, desenvolvido no MIT (Massachusetts Institute of Technology - Instituto de Tecnologia de Massachusetts) na d�cada de 1970, logo seguido pelo RDBMS Ingres, desenvolvido em 1974 na UC Berkeley. O Ingres implementava uma linguagem de consulta conhecida como QUEL, que mais tarde foi substitu�da no mercado pelo SQL.

O SQL 2008 � a sexta revis�o para os padr�es ISO e ANSI para a linguagem de consulta de banco de dados SQL e foi formalmente adotado em Julho de 2008. O padr�o SQL 2008 � dividido em v�rias partes, cobrindo o Framework, Conceitos, a Camada de Interface de Chamadas, M�dulos de Persist�ncia e Armazenamento, Gerenciamento de Dados Externos, Linguagem de Liga��o de Objetos, Informa��o e Defini��o de Esquemas, Rotinas e Tipos usando Java, e v�rias "Especifica��es relacionadas."

O padr�o SQL n�o est� dispon�vel gratuitamente. Todo o padr�o pode ser adquirido a partir da ISO como ISO/IEC 9075 (1-4,9-11,13,14): 2008. O n�vel m�nimo de conformidade com o padr�o SQL 2008 para que um produto possa ser chamado de "Core SQL 2008" se limita a defini��es especificadas em duas partes da norma: Conceito e Informa��o e Defini��o de Esquemas.

Como mencionei em todos os n�meros anteriores desta s�rie, este material foi criado a partir de uma palestra que apresentei aos meus colegas de trabalho sobre o uso do DB2. Por�m, isso n�o deve causar impacto algum para o leitor que usa outro SGBD, uma vez que as CTEs s�o recursos estabelecidos pelo padr�o ANSI SQL e sua implementa��o deve ser praticamente id�ntica em qualquer SGBD relacional.

Neste artigo tratamos dos seguintes t�picos:

� Defini��o e cria��o de uma CTE;

� Exemplos de CTEs simples;

� Avaliando performance de consultas CTEs simples;

� Recursividade;

� Como implementar CTEs recursivas;

� Exemplos de CTEs recursivas;

� Avaliando performance de consultas CTEs recursivas.

Para situarmos os recursos SQL que vamos estudar, � preciso ter uma base de dados como amostra. Temos usado nestes artigos uma base com dados fict�cios de demanda de produtos farmac�uticos. A Figura 1 mostra o modelo desta base.

Figura 1: Modelo de dados da base de exemplo

Assumimos neste artigo que o leitor j� est� familiarizado com conceitos b�sicos de programa��o, al�m de j� dominar os conceitos da linguagem SQL apresentados nos artigos anteriores desta s�rie.

Defini��o e cria��o de CTEs

Uma CTE, por defini��o, � um conjunto de dados tempor�rio que recebe um nome e que � derivado de consultas simples e pode ser usado em declara��es de leitura, escrita, atualiza��o ou exclus�o de dados (SELECT/INSERT/UPDATE/DELETE).

Um detalhe fundamental � que uma CTE existir� apenas at� a execu��o da pr�xima declara��o SQL. Depois disso, este objeto tempor�rio n�o existe mais.

A declara��o de uma CTE exige o uso da palavra reservada WITH, conforme se v� na Listagem 1.

Listagem 1. Sintaxe para de uma CTE simples.

--defini��o da CTE
  WITH nome_da_cte (coluna_saida1, coluna_saida2, ... )
    AS (SELECT .....)
  -- seguida pela chamadada CTE em um declara��o qualquer
  SELECT ....
  FROM tabela1, nome_da_cte;

...

Quer ler esse conteúdo completo? Tenha acesso completo