SQL - Linguagem Estruturada de Consultas e o VB

A linguagem estruturada de consultas (Structured Query Language), é uma linguagem de alto nível para manipulação de dados dentro do modelo relacional. É de tal ordem sua importância para a indústria dos bancos de dados relacionais que ela acabou por se tornar o mecanismo mais popular de acesso aos grandes bancos de dados cliente/servidor. Quando o primeiro sistema gerenciador de banco de dados relacional foi desenvolvido, no início da década de 70, o primeiro mecanismo de acesso aos dados ali mantidos foi uma forma primitiva de SQL.

O sucesso da linguagem foi tal que obrigou o Instituto Americano de Padrões (ANSI) a padronizar as implementações do produto. Assim, a maior parte - senão todas - as implementações da linguagem seguem de perto o padrão ANSI definido. As pequenas variações existentes não afetam a padronização global e costumam ser incorporadas para complementar as capacidades da linguagem. Ainda que tais variações possam aumentar a complexidade da migração de um ambiente para outro, elas não afetam a estratégia global de portabilidade entre plataformas. Se é o caso de desenvolver aplicações portáteis, o padrão ANSI - suportado por todos os produtos, de uma forma geral - deve ser seguido rigorosamente. Ainda que se percam alguns pontos em desempenho e facilidade, a portabilidade estará garantida.

Para nós, programadores em VB, a compreensão e a utilização da SQL é decisiva por alguns bons motivos:

É poderosa e flexível. É muito mais rápida a recuperação e a manipulação de dados utilizando a SQL que os objetos disponíveis na DAO. Com uma única instrução SQL podemos substituir dezenas ou mesmo centenas de linhas de código baseadas nos objetos da biblioteca DAO.

É um padrão na indústria. Como a SQL, com pequenas variações, é suportada pelos mais importantes bancos de dados relacionais, podemos criar programas de front-end para a administração de bancos de dados relativamente independentes do back-end. Por exemplo, podemos escrever um aplicativo voltado para administração de um banco de dados Access e, sem alterações significativas, portá-lo de modo a utilizar o Microsoft SQL Server ou outro banco de dados ODBC.

Freqüentemente, a implementação da linguagem SQL incorporada ao Microsoft Jet será utilizada para desempenho máximo, em conjunto com os objetos da DAO. Uma utilização comum poderá ser selecionar um subconjunto dos dados através de uma instrução SQL, gerar um objeto Recordset e manipulá-lo com os métodos navegacionais disponibilizados pela DAO. Com isso, o programador obterá o melhor de dois mundos. Além disso, essa estratégia costuma facilitar a portabilidade do aplicativo: deve variar somente o mecanismo de conexão com a fonte de dados SQL, tudo o mais permanecendo constante.

Neste capítulo, procuraremos abordar de uma forma ampla, os fundamentos e a capacidade do dialeto SQL suportado pelo Microsoft Jet Database Engine, tal como implementado na versão 5 do VB. O objetivo básico do capítulo será, então, prover o programador de uma ferramenta poderosa e portátil, capaz de tornar a programação de aplicativos de front-end para bancos de dados uma tarefa simples.

O objetivo fundamental da SQL é fornecer ao usuário de um RDBMS uma interface de alto nível, capaz de isolá-lo completamente das complexidades de implementação do sistema. Com tal objetivo em mente, desenvolveu-se uma linguagem totalmente não-procedural. Ao usuário da SQL não cabe definir como o gerenciador do banco de dados executará uma tarefa em particular, mas somente o que deve ser feito.

Vamos imaginar um exemplo para que isso fique claro. Suponhamos que você precise atualizar uma tabela de comissões sobre a venda dos vendedores da empresa. Por padrão, a empresa paga 5% de comissão sobre o valor faturado numa venda. Como medida de estímulo, a empresa resolveu pagar comissão de 7.5% para todas das vendas maiores que R$ 1.600,00, mas somente para vendas efetuadas a partir de 15 de fevereiro de 1998. No modelo navegacional da DAO você poderia escrever, por exemplo, o seguinte código:

Figura 15.1

 

Está claro, no exemplo, o modelo procedural da operação de atualização, expresso no seguinte algoritmo:

1. Posicionamos o ponteiro de arquivo no primeiro registro lógico.

2. Verificamos se o registro corrente corresponde ao critério desejado.

3. Se a verificação é verdadeira, atualizamos o campo desejado.

4. Seguimos para o próximo registro.

5. Repetimos os passos 2 a 4 até encontrar o fim do arquivo.

A mesma atualização utilizando a SQL poderia ser feita da seguinte forma:

Figura 15.2: Usando SQL para atualizar banco de dados

Note a diferença radical dessa abordagem. Em momento nenhum nos preocupamos em determinar como a atualização deve ser feita. Apenas especificamos o que deve ser feito. Solicitamos ao Jet atualizar o campo Comissão da tabela Comissões para 0.075 sempre que o registro satisfaça ao critério especificado. Isso foi feito por um comando muito próximo da linguagem utilizada para descrever a tarefa.

Evidentemente, o sistema gerenciador é incapaz de compreender a linguagem de alto nível empregada. Antes de ser executada, ela deve ser traduzida para uma linguagem procedural ao nível da máquina. O ponto principal a ressaltar, contudo, é que essa tradução é feita pelo dispositivo gerenciador e não pelo programador. Este pode continuar a pensar em termos da tarefa a ser executada e não de como executá-la. Isso torna mais simples a programação, de modo que mais pessoas podem escrever os mesmos programas em menos tempo. Naturalmente, a complexidade não é estranha a determinadas instruções SQL. No entanto, essa complexidade será sempre inferior à sua equivalente programada.

Um outro ponto a ressaltar diz respeito ao que você pode fazer com o VB e a SQL, ainda que não utilize o Jet. Como já assinalamos, o Jet não é um dispositivo cliente\servidor. Ele reside no mesmo computador do aplicativo de front-end. Assim, utilizando um banco de dados remoto, o tráfego na rede é equivalente quando utilizada a DAO ou a SQL. No entanto, o mesmo código escrito visando o banco de dados Access pode ser, facilmente, portado, por exemplo, para a utilização do SQL Server como fonte de dados. Ambos os dispositivos compreendem o mesmo dialeto da linguagem SQL. Numa consulta de seleção de registros, por exemplo, trafegarão na rede somente o comando SQL e os registros selecionados. Essa abordagem, portanto, implica um ganho de performance impossível sem a utilização da SQL.

Sintaxe do SQL

Uma declaração SQL pode consistir dos seguintes componentes:

Uma declaração de parâmetros. Quando uma declaração requer parâmetros, isto é, valores somente disponíveis em tempo de execução a partir da interação com o usuário, é conveniente - ainda que opcional - a declaração explícita dos parâmetros utilizados. Tal declaração deve ser feita da seguinte forma: PARAMETERS nomeDoParâmetro tipoDeDado; Qualquer quantidade de parâmetros, dentre os tipos de dados suportados pelo dispositivo Jet (ou pelo sistema gerenciador ao qual o aplicativo está conectado via ODBC), é inteiramente válida.

O comando SQL. Este componente informa ao gerenciador a ação a ser executada. Por exemplo, a declaração SELECT * FROM Clientes solicita ao dispositivo gerenciador que retorne todos os campos da tabela Clientes.

Cláusulas opcionais. Informam ao dispositivo gerenciador as condições segundo as quais os registros serão selecionados, tais como ordenações, condições lógicas de filtro, agrupamentos, etc. Se o comando SQL informa ao dispositivo o que fazer, as cláusulas opcionais especificam os registros a serem incluídos no processamento e o critério específico dessa inclusão.

Comandos SQL

É costume tratar a SQL como composta de subconjuntos de comandos - linguagens - para a execução de diferentes tarefas. Tal como a DAO, a SQL suporta tanto uma linguagem de definição de dados (DDL) - para a criação e alteração de tabelas e índices quanto uma linguagem de manipulação de dados (DML) - para a seleção e atualização dos dados armazenados nas tabelas. A Figura 15.3 sumariza os diversos comandos suportados pela SQL incorporada ao VB:

Figura 15.3: Os comandos SQL suportados pelo VB.

Cláusulas SQL

As cláusulas são modificadores utilizados para definir os dados a serem selecionados para a manipulação. A Figura 15.4 sumariza as diversas cláusulas suportadas pela SQL no VB5:

Figura 15.4: As cláusulas suportadas pela SQL do Visual Basic.

Operadores

A SQL suporta dois tipos de operadores:

Os operadores lógicos, utilizados para conectar expressões, normalmente numa cláusula WHERE.

Os operadores de comparação, utilizados para comparar os valores de duas expressões.

A Figura 15.5 sumariza, por tipo, os operadores suportados pela SQL do VB:

Figura 15.5: Os operadores SQL suportados pelo Visual Basic.

Funções Agregadas

Além dos comandos vistos anteriormente, a SQL suporta ainda um conjunto de funções pré-definidas. Tais funções, utilizadas numa cláusula SELECT, referem-se a determinados grupos de registros e retomam um valor aplicável ao grupo. A Figura 15.6 sumariza as funções suportadas pelo Jet:

Figura 15.6: As funções agregados suportados pela SQL do Visual Basic.

Como já foi dito, a DDL é um subconjunto de comandos SQL capazes de criar e modificar a estrutura de uma tabela, incluindo os seus índices. De uma forma geral, a DDL pode substituir boa parte das operações efetuadas pelos objetos da DAO, tal como discutimos anteriormente. No entanto, há algumas limitações para as operações com a DDL:

Somente os bancos de dados nativos do Jet - no formato do Microsoft Access - admitem os comandos DDL a partir do VB.

Apenas um limitado número de propriedades de tabelas, campos e índices é abarcado pelos comandos DDL.

Operações com a DDL

Com DDL, é possível:

Criar e remover uma tabela, definindo as propriedades básicas (nome, tipo e tamanho) dos campos;

Alterar a estrutura de uma tabela, incluindo e removendo campos;

Criar e remover um índice.

Trataremos agora circunstanciadamente de cada bloco de operações.

Criando e Removendo Tabelas

Para criar uma tabela com um comando DDL, apenas especificamos os nomes, tipo de dado e tamanho, se for o caso, de cada campo numa cláusula CREATE TABLE. Os nomes dos campos não precisam ser delimitados por aspas. Contudo, nomes de campos contendo espaços devem ser delimitados por colchetes.

Na criação de uma tabela com a DDL, apenas as propriedades Name, Type e Size do equivalente objeto Field são especificadas. As demais propriedades suportadas pelo objeto, como ValidationRule, ForeignName, etc., não são suportadas. Se necessárias, a tabela deve ser modificada com o auxílio da DAO.

O seguinte fragmento de código cria uma tabela Clientes no banco de dados Base32.Mdb, para a empresa Limpa Tudo Materiais de Limpeza Ltda., tal como já foi descrito:

Dim db As Database

Set db = Workspaces(0).CreateDatabase (App.Path & _

"\Base32.Mdb", dbLangGeneral, dbVersion30)

db.Execute "CREATE TABLE Clientes " & _

"(Cliente TEXT (4), CGC TEXT " & _

"(14), RazãoSocial TEXT (30), " & _

"Endereço TEXT (50), CEP TEXT (8))"

db.Close

A remoção de uma tabela em SQL é tão simples quanto utilizando a DAO. O seguinte fragmento de código remove a tabela criada no exemplo anterior:

Dim db As Database

Set db = Workspaces(0).OpenDatabase(App.Path & _

"\Base32.Mdb")

db.Execute "DROP TABLE Clientes"

db.Close

Modificando Tabelas

Do mesmo modo que na DAO, não é possível alterar as propriedades de um campo já anexado a uma tabela. Sendo assim, utilizando os comandos DDL, é possível apenas remover um campo (coluna) ou anexar um novo à tabela. Portanto, a alteração de uma das propriedades do campo, via SQL, requer que ele seja primeiro removido e depois novamente incluído com as características alteradas. Note-se uma vez mais que apenas o nome, o tipo de dado e o tamanho de um campo podem ser especificados.

O seguinte fragmento de código altera a estrutura da tabela Clientes, criada na seção anterior:

Dim db As Database

Set db = Workspaces(0).OpenDatabase (App.Path & _

"\Base32.Mdb")

db.Execute "ALTER TABLE Clientes " & _

"DROP COLUMN Cliente"

db.Execute "ALTER TABLE Clientes " & _

"ADD COLUM Cliente SHORT"

db.Close

Criando e Removendo Índices

Há três diferentes maneiras de criarmos um índice utilizando os comandos SQL:

Na criação da tabela, após a especificação do comando CREATE TABLE. É possível, neste momento, criar um índice a partir de uma única coluna ou a partir de diversas. Apenas acrescente a cláusula CONSTRAINT especificando os campos e propriedades do índice.

Utilizando o comando CREATE INDEX isoladamente. Neste caso, pode-se também especificar as diversas propriedades do índice, bem como definir regras de validação com a cláusula WITH.

Utilizando o comando ALTER TABLE. Apenas utilize a combinação de palavras-chave ADD CONSTRAINT após o comando ALTER TABLE.

Como se pode notar, utilizamos a cláusula CONSTRAINT para criar ou remover um índice junto com os comandos CREATE TABLE e ALTER TABLE. Com esta cláusula é possível também definir uma chave primária ou externa, definir relacionamentos e forçar a integridade referencial dos dados.

O seguinte fragmento de código cria um índice primário para a tabela Clientes utilizando o campo Cliente:

Dim db As Database

Set db = Workspaces(0).OpenDatabase(App.Path & _

"\Base32.Mdb")

db.Execute "ALTER TABLE Clientes " & _

"ADD CONSTRAINT Cliente " & _

" PRIMARY KEY (Cliente)"

db.Close

O fragmento de código seguinte cria uma nova tabela, chamada Telefones dos Clientes e, a seguir, estabelece um relacionamento por chave externa com a tabela Clientes, através do campo Cliente:

Dim db As Database

Set db = Workspaces(0).OpenDatabase(App.Path & _

"\Base32.Mdb")

db.Execute "CREATE TABLE [Telefones dos Clientes] " & _

"(Cliente SHORT, Telefone TEXT (10), " & _

"Contato TEXT (30)) "

db.Execute "ALTER TABLE [Telefones dos Clientes] " & _

"ADD CONSTRAINT TelefonesCliente " & _

"FOREIGN KEY (Cliente) " & _

"REFERENCES Clientes (Cliente)"

db.Close

O fragmento de código seguinte ilustra a utilização do comando CREATE INDEX para indexar a tabela anterior utilizando o campo Telefone:

Dim db As Database

Set db = Workspaces(0).OpenDatabase (App.Path & _

"\Base32.Mdb")

db.Execute "CREATE INDEX Telefones " & _

"ON [Telefones dos Clientes] (Telefone)"

db.Close

Para remover um índice existente, apenas utilize o comando DROP com referência ao índice desejado. Por exemplo, para remover o índice Telefones da tabela Telefones dos Clientes, utilize o seguinte comando:

db.Execute "DROP INDEX Telefones " & _

"ON [Telefones dos Clientes]"

Método Execute

Executa um comando SQL no banco de dados especificado. Note que o método só é válido para consultas de ação, onde não são retomados registros.

Sintaxe

ObDatabase.Execute comandoSQL [, opções ] ObQueryDef.Execute [ [ opções ]

Extração de Dados das Tabelas

De longe, a utilização mais comum da linguagem SQL - mais especificamente do subconjunto conhecido como DML, a linguagem de manipulação de dados - é na recuperação de subconjuntos dos dados para posterior tratamento. É o que se convencionou chamar Consultas. Aliás, o maior poder da SQL não se revela no subconjunto DDL, mas principalmente na sua capacidade de selecionar um conjunto complexo de registros segundo um conjunto, também complexo, de condições e tratá-lo num único passo.

O Comando SELECT

Na SQL, é o comando SELECT o utilizado na recuperação de dados. A forma básica do comando SELECT é a seguinte:

SELECT nomesDosCampos FROM nomeDaTabela [cláusulas]

Assim, a forma mais simples de uma consulta poderia ser a que segue, aplicada a uma das tabelas definidas para a Limpa Tudo:

SELECT * FROM Fornecedores

A instrução anterior seleciona todos os campos da tabela Fornecedores e retorna um subconjunto dos dados, que pode ser visualizado no Microsoft Access, conforme a Figura 15.7:

Figura 15.7: Uma consulta de seleção.

Naturalmente, não é obrigatória a seleção de todos os campos da tabela. Pode-se selecionar qualquer quantidade desejada de campos. O comando a seguir recupera apenas dois campos da tabela Estoque do mesmo banco de dados:

SELECT Produto, [Quantidade Estocada] FROM Estoque

A Cláusula WHERE

É possível também determinar que a seleção dos registros seja feita com base num determinado critério. Aliás, essa é a utilização mais comum. Para isso, nós adicionamos determinadas cláusulas ao comando SQL. A cláusula básica para a especificação de critérios de seleção é a cláusula WHERE.

A cláusula determina as condições de extração dos registros. Assim, na sua forma básica, ela utiliza os operadores da linguagem para determinar o mecanismo de seleção utilizado pelo comando. Um exemplo da utilização da cláusula pode ser o que segue:

SELECT Produto, [Nome do produto] FROM Produtos WHERE Categoria = 1

A cláusula anterior, aplicada ao banco de dados definido para a empresa Limpa Tudo, retorna os registros exibidos no exemplo da Figura 15.8:

Figura 15.8: Uma consulta de seleção com cláusula condicional

Costuma ser necessário que uma consulta satisfaça a múltiplos critérios de seleção. Por exemplo, você pode desejar visualizar os produtos da categoria 2 - Detergentes. Assim, você escreve o seguinte comando:

SELECT Produto, [Nome do Produto], [Preço de Venda] FROM Produtos WHERE Categoria = 2

Você deseja discutir com um cliente da Limpa Tudo uma grande venda de um desses produtos. Sabe, porém, que o cliente não deseja pagar mais que setenta centavos por unidade. Assim, você solicita a seguinte consulta:

SELECT Produto, [Nome do Produto], [Preço de Venda] FROM Produtos WHERE [Preço de Venda] <= 0.70

Como você pode notar, o que você efetivamente deseja é ainda um subconjunto dos dados produzidos pelas duas queries. Na verdade, apenas aquele subconjunto de registros comuns a ambas as consultas. Para obter tal resultado, você deve, evidentemente, compor a cláusula condicional de tal modo que apenas esse subconjunto específico seja retornado pelo dispositivo gerenciador. Assim, o comando deve ser escrito da seguinte forma, que recupera tão somente os produtos desejados:

SELECT Produto, [Nome do Produto], [Preço de Venda] FROM Produtos WHERE Categoria = 2 AND [Preço de Venda] <= 0.70

Lembre-se de que a estratégia fundamental, quando se trata da utilização da linguagem SQL no contexto de aplicações cliente-servidor, é reduzir o universo de registros selecionados ao mínimo possível, de modo a melhorar a eficiência do tráfego dos dados. Assim, boa parte do esforço do programador na composição de consultas de seleção estará voltado para a definição de cláusulas WHERE tais que reduzam ao máximo possível o resultado da query.

Não suponha que qualquer artifício de programação utilizando a DAO possa substituir a eficiência da linguagem SQL. Os modernos gerenciadores de banco de dados relacionais possuem mecanismos de otimização eficientes e são capazes de criar estratégias de recuperação as mais velozes no contexto do comando executado.

Especificando um Intervalo para os Registros

Uma outra necessidade comum é a especificação de um intervalo de valores de um determinado campo para a seleção dos registros. Imaginemos que seja necessário visualizar todas as vendas efetuadas na segunda quinzena do mês de janeiro de 1998. Para isso precisamos utilizar a cláusula BETWEEN na condição lógica de seleção. Veja o exemplo a seguir:

SELECT Vendas.Data, Produtos.[Nome do Produto],

Faturas.[Quantidade Vendida] FROM

(Vendas INNER JOIN Faturas ON

Vendas.Fatura = Faturas.Fatura)

INNER JOIN Produtos

ON Faturas.Produto = Produtos.Produto

WHERE (Vendas.Data BETWEEN CVDate('16/01/98')

AND CVDate('31/01/98'))

Especificando uma Lista de Condições

Suponhamos agora que você deseje todos os produtos categorizados como 1- Saponáceo, 2 - Detergente e 5 -Água Sanitária. Isso pode ser obtido pela seguinte cláusula:

SELECT Produto, [Nome do Produto] FROM Produtos

WHERE Categoria = 1 OR Categoria = 2 OR Categoria = 5

Na verdade, o que se deseja aqui é um conjunto de registros que satisfaçam a uma lista de condições alternativas. Uma forma mais compacta de obter o mesmo resultado é o comando:

SELECT Produto, [Nome do Produto] FROM produtos

WHERE Categoria IN (1, 2, 5)

Verificando a Ocorrência de Valores NULL

Na definição da base de dados da Limpa Tudo, feita anteriormente, especificamos uma tabela-filha Telefones dos Fornecedores, onde armazenávamos também o nome dos empregados responsáveis pelo fornecimento. Você se lembra de que o campo criado para representar a informação - Contato - não era obrigatório, podendo assumir valor NULL. Além disso, especificamos que diversos telefones e contatos poderiam ser consignados a um mesmo fornecedor. Suponhamos agora que seja necessário visualizar os telefones e os contatos de algumas empresas. O seguinte comando SQL poderia ser usado:

SELECT Fornecedor, Telefone, Contato

FROM [Telefones dos Fornecedores]

WHERE Fornecedor IN (23, 41, 168)

No entanto, você não deseja telefones que não estejam associados a nenhum contato. Assim, alguns registros da consulta resultante não são necessários e podem ser eliminados. A seguinte declaração não inclui tais registros:

SELECT Fornecedor, Telefone, Contato

FROM [Telefones dos Fornecedores]

WHERE Fornecedor IN (23, 41,168)

AND Contato <> ""

O comando, utilizado com o Microsoft Jet, elimina os registros com valor NULL para o campo Contato. No entanto, tais tipos de campos podem variar de sistema para sistema, o que tornaria a consulta menos portátil. Desse modo, a linguagem SQL implementa um mecanismo genérico para testarmos a ocorrência de um valor NULL. Aquela consulta pode, então, numa forma mais genérica, ser escrita da seguinte forma, obtendo o mesmo resultado:

SELECT Fornecedor, Telefone, Contato

FROM [Telefones dos Fornecedores]

WHERE Fornecedor IN (23, 41, 168)

AND Contato IS NOT NULL

Ordenando as Consultas

Não faz sentido, no modelo relacional, questionarmos a ordenação física dos registros (linhas) numa tabela. O problema só passa a ter significado no momento da recuperação dos dados, uma vez que a ordenação só faz sentido para o usuário e não para o próprio modelo lógico dos dados.

A linguagem SQL fornece a cláusula ORDER BY visando a especificação da ordem particular na qual os registros serão retornados do dispositivo gerenciador de bancos de dados. O seguinte comando retorna uma lista em ordem alfabética de todos os clientes da Limpa Tudo:

SELECT [Razão Social] FROM Clientes

ORDER BY [Razão Social]

Extraindo Dados de Múltiplas Tabelas

Quando tratamos da modelagem de dados, insistimos no fato de que nenhuma informação redundante deveria ser mantida numa tabela. Nestes casos, o tópico deveria ser dividido em duas ou mais tabelas, estabelecendo-se um relacionamento entre elas através de um campo comum a ambas. Isso significa que, via de regra, os dados necessários para a eficiente manipulação de uma base de dados estão contidos em mais de uma tabela.

Não há nenhuma complicação adicional em se extrair dados de diversas tabelas armazenadas num mesmo banco de dados. Simplesmente informe após a cláusula FROM o nome das diversas tabelas necessárias. A partir daí, passa a ser relevante declarar explicitamente de qual tabela o campo deve ser extraído na cláusula SELECT. Isso é feito precedendo o nome do campo pelo nome da tabela e separando-os com o operador ponto.

No entanto, algumas considerações devem ser feitas. Quando separamos dados afins em múltiplas tabelas, geralmente as relacionamos através de um campo-chave (a chave externa). Quando isso acontece, não é possível simplesmente declarar as diversas tabelas de onde os dados devem ser extraídos sem determinar o tipo de relacionamento desejado entre os campos-chave. Sendo assim, deve haver um mecanismo para declarar o relacionamento entre as tabelas. Um exemplo simples nos ajudará a ilustrar o problema. Suponhamos que a Limpa Tudo precise mandar uma mala direta para todos os seus clientes, divulgando os novos e fantásticos descontos dados para seus produtos. Assim, é necessário retornar um endereço completo na forma exigida pela empresa de correios. Como você se recorda, os campos necessários para a mala direta - Razão Social, Endereço, CEP, Cidade e UF - estão armazenados em duas tabelas - Clientes e Localidades.

Você se recorda também que estabelecemos uma relação um-para-vários entre ambas através do campo CEP, onde Clientes é a tabela dependente. Assim, nem todos os registros da tabela Localidades nos interessam, apenas aqueles com uma correspondência direta na tabela Clientes. Esta correspondência é definida na expressão Localidades.CEP = Clientes.CEP.

Vamos supor agora que nem todos os clientes da Limpa Tudo devem ser incluídos na mala direta, apenas aqueles que adquiriram produtos no ano de 1998. Assim, precisamos incluir na consulta também a tabela Vendas. Os campos dessa tabela não serão utilizados na mala direta; servem apenas para filtrar os registros da tabela Clientes.

Na modelagem do banco de dados, definimos que para cada registro na tabela Clientes, poderiam existir vários registros na tabela Vendas, relacionados pela chave Clientes. A condição de relacionamento da consulta é, portanto, expressa pela relação Clientes.Cliente = Vendas.Cliente. O relacionamento global entre as três tabelas componentes da mala direta pode ser graficamente expresso pela Figura 15.9:

Figura 15.9: Gerando uma mala direta.

Como dissemos, a tabela Vendas será utilizada somente como um filtro para os registros da tabela Clientes. Queremos extrair somente aqueles registros que satisfaçam a duas condições:

Estejam, simultaneamente, em ambas as tabelas. Essa, como se sabe, é a condição relacionando as tabelas.

Estejam armazenados na tabela Vendas com uma data qualquer do ano de 1998. A condição WHERE de filtro seria, portanto, Vendas.Data > CVDate('01/01/98')

Essa complexa rede de relacionamentos deve ser expressa na cláusula FROM da nossa consulta. O mecanismo básico para a expressão desse critério de relacionamento é a cláusula SQL INNER JOIN... ON. Nós declaramos que a tabela A deve ser combinada à tabela B (INNER JOIN) sempre que os campos-chave corresponderem a um determinado valor de comparação (ON). A complexidade do relacionamento das tabelas do exemplo pode ser expressa pelo aninhamento das cláusulas INNER JOIN.

A cláusula INNER JOIN combina registros de duas tabelas sempre que campos comuns satisfaçam a um determinado valor de comparação. Note que a tentativa de utilizar campos Memo ou OLE Object na comparação gera um erro. Campos numéricos podem ser comparados mesmo que possuam diferentes tipos de dados.

A extração dos registros desejados do banco de dados da Limpa Tudo pode ser feita utilizando a declaração SQL listada a seguir, onde deve ser notado o aninhamento de ligações:

SELECT Clientes.[Razão Social], Clientes.CGC,

Clientes.Endereço, Clientes.CEP, Localidades.Cidade,

Localidades.UF FROM (Localidades

INNER JOIN Clientes ON Localidades.CEP = Clientes.CEP)

INNER JOIN Vendas ON Clientes.Cliente = Vendas.Cliente

WHERE (Vendas.Data>CVDate('01/01/98'))

Em alguns casos, contudo, podemos querer selecionar todos os registros de uma das tabelas, tenham ou não correspondência com os registros de outra. Nestes casos, utilizamos as cláusulas LEFTJOIN e RIGHTJOIN.

A cláusula LEFT JOIN seleciona todos os registros da primeira tabela (à esquerda da declaração), mesmo que não tenham correspondência com os registros da segunda tabela. A cláusula RIGHT JOIN faz a mesma coisa em sentido inverso, referenciando a segunda tabela (à direita da declaração).

Acessando Tabelas Externas

Algumas vezes, é necessária a extração de dados de tabelas armazenadas em fontes de dados externas. Tais fontes representam bancos de dados não nativos, mas acessíveis pelo Microsoft Jet, tais como dBase, Paradox, FoxPro, etc. Nestes casos, especificamos o banco de dados e o tipo da conexão através da cláusula IN. Por exemplo, podemos selecionar todos os campos de arquivo dBase III das seguintes maneiras:

SELECT * FROM Arquivo IN "Drive:\Diretório" "dBase III;";

SELECT * FROM Arquivo IN "" [dBase III; DATABASE=Drive:\Diretório;];

Condições de filtragem e ordenações podem ser especificadas normalmente. Deve-se atentar, contudo, para as diferenças entre os diversos tipos de bancos de dados e a maneira do Microsoft Jet conectá-los.

Filtrando Resultados de uma Consulta

Há diferentes maneiras de filtrar e agrupar os dados numa consulta. Para isso, a linguagem SQL incorpora um bom número de cláusulas e predicados com essa finalidade. Trataremos apenas das mais utilizadas, a saber:

O predicado DISTINCT. Omite os registros contendo dados duplicados dentro de uma seleção. Imagine que você deseja uma visão geográfica dos clientes de uma empresa. Uma das possibilidades pode ser, por exemplo, uma consulta selecionando somente o campo Cidade da base de dados. Neste caso, faz sentido selecionar apenas um registro para cada valor distinto obtido, eliminando-se a redundância. Um exemplo poderia ser a declaração SELECT DISTINCT Cidade FROM Localidades.

O predicado TOP. Retorna somente uma determinada quantidade de registros compondo o início ou o fim de um intervalo especificado. Por exemplo, suponha que você deseje localizar os dez maiores clientes da sua empresa. Isso poderia ser feito por uma declaração como SELECT TOP 1O Cliente FROM Clientes ORDER BY Faturamento DESC.

A cláusula GROUP BY. Combina, num único registro, registros com um mesmo valor para um campo especificado. Caso se utilize as funções SUM ou COUNT, um valor agregado é criado para cada registro que satisfaça a condição especificada. Por exemplo, para obter uma consulta ordenada de itens em estoque, é possível utilizar a declaração SELECT Produtos.[Nome do Produto], Estoque.[Quantidade Estocada] FROM Estoque INNER JOIN Produtos ON (Produtos.Produto = Estoque. Produto) AND (Estoque.Produto = Produtos. Produto) GROUP BY Produtos.[Nome do Produto], Estoque.[Quantidade Estocada].

A cláusula HAVING. Especifica quais registros agrupados (por uma cláusula GROUP BY) serão retornados. Tais registros deverão satisfazer a uma condição especificada. Por exemplo, para obter apenas os itens em estoque cuja quantidade é inferior a 50, pode-se utilizar a declaração SELECT Produtos.[Nome do Produto], Estoque. [Quantidade Estocada] FROM Estoque INNER JOIN Produtos ON (Produtos.Produto = Estoque.Produto) AND (Estoque.Produto = Produtos.Produto) GROUP BY Produtos.[Nome do Produto], Estoque.[Quantidade Estocada] HAVING (Estoque.[Quantidade Estocada]<50).

Subconsultas e Consultas de Referência Cruzada

Não há, praticamente, limite para a complexidade de uma query. Nós vimos um exemplo dessa complexidade quando enfocamos a cláusula INNER JOIN. Há outros tópicos a abordar.

Subconsultas

Uma subconsulta é simplesmente uma declaração SELECT dentro de uma outra declaração SELECT ou declarações de ação, vistas mais adiante. É possível utilizar uma subconsulta nas mais diversas circunstâncias:

No lugar de uma expressão na lista de campos de uma declaração SELECT;

Dentro de uma cláusula WHERE;

Dentro de uma cláusula HAVING.

 

Para selecionar os registros da consulta principal que satisfaçam a comparação com quaisquer dos registros da consulta secundária, utilizamos os predicados ANY ou SOME. Para selecionar somente os registros da consulta principal que satisfaçam a comparação com todos os registros da consulta secundária, utilizamos o predicado ALL. Para selecionar somente os registros da consulta principal para os quais existam valores idênticos na consulta secundária, utilizamos o predicado IN.

Tentemos um exemplo simples. Queremos uma lista de todos os fornecedores da Limpa Tudo cujos produtos, sejam eles quais forem, tenham preço inferior a R$ 1,00. Podemos utilizar a seguinte consulta para a seleção dos registros apropriados: SELECT * FROM Fornecedores "WHERE Fornecedor = ANY (SELECT * FROM [Produtos Por Fornecedor] WHERE [Preço de Compra] < 1@).

Consultas de Referência Cruzada

A consulta de referência cruzada é utilizada quando desejamos resumir dados em um formato de linhas e colunas. Com tais consultas, usamos valores de um campo ou expressão particular como cabeçalhos de coluna e cabeçalhos de linha, de modo a visualizar dados num formato mais compacto do que com uma consulta seleção.

Para a criação de uma consulta de referência cruzada, utilizamos a instrução TRANSFORM antes da declaração SELECT usada para recuperar os registros e especificamos a função de agregação que opera sobre o dado selecionado.

Consultas de Ação

Um conjunto amplo de operações é possível com a SQL. Ela é particularmente eficiente no processamento de grandes massas de registros. Vejamos alguns exemplos.

Consultas de Criação de Tabelas

É possível a criação de uma tabela a partir dos resultados de uma consulta em particular. Neste caso, em vez de utilizamos o objeto Recordset criado pelo comando SQL, fazemos com que ele crie uma tabela onde os registros serão armazenados. Isto é feito acrescentando-se a cláusula INTO à instrução SELECT, especificando o nome da tabela a ser criada.

As consultas de criação de tabela podem ser utilizadas para arquivar registros antigos, fazer cópias de reserva ou de exportação para outro banco de dados ou, ainda, como base para relatórios que exibem dados de uma época específica.

Quando uma tabela é criada a partir de uma consulta, os campos dessa nova tabela herdam os tipos de dados e o tamanho do campo de cada campo das tabelas de origem da consulta, mas nenhuma outra propriedade de campo ou de tabela é transferida. É possível, contudo, definir um campo de chave primária para a nova tabela.

Para criar uma tabela temporária contendo os dez melhores clientes da empresa, utilize o seguinte comando SQL: SELECT TOP 10 Cliente INTO [Melhores Clientes] FROM Clientes ORDER BY Faturamento DESC.

Consultas de Anexação

É possível acrescentar registros de uma tabela ou consulta ao final de uma outra tabela, no mesmo banco de dados ou em um diferente. São as chamadas consultas de anexação, criadas utilizando-se o comando INSERT INTO antes da declaração SELECT que gera a consulta. Neste caso, especificamos a tabela onde os registros serão anexados. Caso a tabela de destino se localize num outro banco de dados, este deverá ser especificado numa cláusula IN.

Se a tabela à qual se está anexando registros incluir um campo de chave primária, os registros anexados precisam ter o mesmo campo, ou um equivalente, do mesmo tipo de dados. O Microsoft Jet anexará os registros se tanto os valores repetidos quanto os vazios resultarem no campo de chave primária.

A seguinte declaração cria uma cópia de backup de uma das tabelas da Limpa Tudo: INSERT INTO Clientes IN 'BACKUP.MDB' SELECT * FROM Clientes.

Consultas de Atualização de Tabelas

Uma consulta de atualização altera um conjunto de registros de acordo com o critério especificado. É utilizada para alterar um grupo de registros especificados com critérios definidos. É possível a utilização de expressões numa consulta de atualização para realizar essas alterações. Utilizamos a cláusula UPDATE para especificar a tabela a ser atualizada e a cláusula SET para determinar os campos que deverão ser alterados e os respectivos valores a serem considerados. A consulta utilizada como exemplo logo no início deste capítulo é uma consulta de atualização de tabela.

Remoção de Registros

Uma consulta de exclusão remove um conjunto de registros de acordo com o critério especificado. É utilizada para excluir um grupo de registros especificados com critérios definidos. É possível utilizar uma consulta de exclusão para excluir registros de uma tabela simples ou de tabelas múltiplas, desde que o relacionamento entre elas seja do tipo um para um. Para excluir registros de tabelas múltiplas em um relacionamento um para vários é necessário executar duas consultas.

Em alguns casos, executar consultas de exclusão pode excluir registros de tabelas relacionadas, mesmo que elas não estejam incluídas na consulta. Isto pode acontecer quando a consulta contém uma tabela que está do lado um de um relacionamento um para vários e a integridade referencial dos dados força a exclusão em cascata para este relacionamento. Quando os registros desta tabela são excluídos, estão sendo excluídos também os registros relacionados da tabela "vários".

A seguinte consulta remove todos os registros da tabela Clientes que não adquiriram produtos da Limpa Tudo no ano de 1997: DELETE * FROM Clientes WHERE Cliente = ANY ( SELECT * FROM Clientes INNER JOIN Vendas ON Clientes.Cliente = Vendas.Cliente WHERE Vendas.Data < CVDate(‘01/01/97’)) .

 

Torne seu código mais legível indentando-o com RabJump