Artigos Banco de Dados Linguagem SQL para geração de relatórios - Revista SQL Magazine 97 - Parte 4
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