Tutorial de SQL

Manual por: Versão on-line:

Criar Web, manuais e recursos para desenvolvimento web [Link]

O que é SQL

As aplicações em rede são cada dia mais numerosas e versáteis. Em muitos casos, o esquema básico de
operação é uma série de scripts que dirigem o comportamento de uma base de dados.

Devido à diversidade de linguagens e de base de dados existentes, a maneira de comunicar entre umas e
outras seria realmente complicado de providenciar, a não ser pela existência de padrões que nos permite
realizar as operações básicas de una forma universal.

É justamente disso que se trata o Structured Query Language que não é mais do que uma linguagem padrão
de comunicação com base de dados. Falamos portanto, de uma linguagem normalizada que nos permite
trabalhar com qualquer tipo de linguagem (ASP ou PHP) em combinação com qualquer tipo de base de dados
(MS Access, SQL Server, MySQL...).

O fato de ser padrão não quer dizer que seja idêntico para cada base de dados. Na prática, determinadas
bases de dados implementam funções específicas que não têm necessariamente que funcionar em outras.

À parte desta universalidade, o SQL possui outras duas características muito apreciadas. Por uma parte,
apresenta potência e versatilidade notáveis que contrasta, por outra, com sua acessibilidade de
aprendizagem.

Informe de Rubén Alvarez

Mail: ruben@[Link]

Tipos de campos

Como sabemos, um banco de dados é composto por tabelas onde armazenamos registros catalogados em função de
diferentes campos (características).

Um aspecto prévio a considerar é a natureza dos valores que introduzimos nesses campos. Visto que um banco de
dados trabalha com todo o tipo de informações, é importante especificar que tipo de valor estamos introduzindo de
maneira a, por um lado, facilitar a busca posteriormente e por outro, otimizar os recursos de memória.

Cada banco de dados introduz tipos de valores de campo que não necessariamente estão presentes em outros.
Entretanto, existe um conjunto de tipos que estão representados na totalidade destes bancos. Estes tipos comuns são
os seguintes:

Alfanuméricos Contém cifras e letras. Apresentam uma longitude limitada (255 caracteres)
Numéricos Existem de vários tipos, principalmente, inteiros (sem decimais) e reais (com decimais).
Booleanos Possuem duas formas: Verdadeiro e falso (Sim ou Não)
Armazenam datas facilitando posteriormente sua exploração. Armazenar datas desta
Datas forma possibilita ordenar os registros por datas ou calcular os dias entre uma data e
outra...
São campos alfanuméricos de longitude ilimitada. Apresentam o inconveniente de não
Memos
poder ser indexados (veremos mais adiante o que isto quer dizer).
 São campos numéricos inteiros que incrementam em uma unidade seu valor para cada
Auto-
registro incorporado. Sua utilidade é mais que evidente: Servir de identificador já que são
incrementáveis
exclusivos de um registro.

Informe de Rubén Alvarez

Mail: ruben@[Link]

Adicionar um novo registro

Os registros podem ser introduzidos a partir de sentenças que empregam a instrução Insert.

A sintaxe utilizada é a seguinte:

Insert Into nome_tabela (nome_campo1, nome_campo2,...) Values (valor_campo1, valor_campo2...)

Um exemplo simples a partir da nossa tabela modelo é a introdução de um novo cliente que seria feito com uma
instrução deste tipo:

Insert Into clientes (nome, sobrenomes, endereço, cidade, código postal, e-mail, pedidos) Values ('Pedro', 'Palotes',
'Percebe n°13', 'Londrina', '123456', 'pedro@[Link]', 33)

Como se pode ver, os campos não numéricos ou booleanos vão delimitados por apóstrofes: '. Também é
interessante ver que o código postal foi salvo como um campo não numérico. Isto é devido a que em determinados
países (Inglaterra,por exemplo) os códigos postais contém também letras.
Nota: Se desejarmos praticar com um banco de dados que está vazio, primeiro devemos criar as tabelas que vamos
preencher. As tabelas também se criam com sentenças SQL e aprendemos a fazer isso no último capítulo.

Embora, de qualquer forma, pode ser que seja mais cômodo utilizar um programa com interface gráfica, como Access, que
pode nos servir para criar as tabelas em banco de dados do próprio Access ou por ODBC a outros bancos de dados como
SQL Server ou MySQL, por dar dois exemplos.

Outra possibilidade em um banco de dados como MySQL, seria criar as tabelas utilizando um software como PhpMyAdmin.

Obviamente, não é imprescindível preencher todos os campos do registro. Porém, poder ser que determinados
campos sejam necessários. Estes campos necessários podem ser definidos quando construirmos nossa tabela
mediante o banco de dados.
Nota: Se não inserirmos um dos campos no banco de dados se iniciará com o valor padrão que tivermos definido na hora
de criar a tabela. Se não houver um valor padrão, provavelmente se inicie como NULL (vazio), no caso de que este campo
permita valores nulos. Se esse campo não permitir valores nulos (isso se define também ao criar a tabela) o mais seguro é
que a execução da sentença SQL nos dê um erro.

É muito interessante, já veremos mais adiante o porquê, o introduzir durante a criação de nossa tabela um
campo auto-incrementável que nos permita atribuir um único número a cada um dos registros. Deste modo,
nossa tabela clientes apresentaría para cada registro um número exclusivo do cliente que será muito útil
quando consultarmos várias tabelas simultaneamente.

Informe de Rubén Alvarez

Mail: ruben@[Link]

Apagar um registro

Para apagar um registro nos servimos da instrução Delete. Neste caso devemos especificar qual ou quais são os
registros que queremos apagar. Por isso, é necessário estabelecer uma seleção que se realize mediante a cláusula
Where.

A forma de selecionar será vista; detalhadamente em capítulos posteriores. Agora, nos contentaremos em mostrar
qual é o tipo de sintaxe utilizada para efetuar estas supressões:

Delete From nome_tabela Where condicoes_de_selecao

Nota: Se desejarmos praticar com um banco de dados que estiver vazio, primeiro devemos criar as tabelas que vamos
preencher. As tabelas também se criam com sentenças SQL que aprendemos a fazer no último capítulo.

Se quisermos, por exemplo, apagar todos os registros dos clientes que se chamarem Pedro, faríamos da
seguinte forma:

Delete From clientes Where nome='Pedro'

Temos que ter cuidado com esta instrução, pois se não especificarmos uma condição com Where, o que
estamos fazendo é apagar toda a tabela:

Delete From clientes

Informe de Rubén Alvarez

Mail: ruben@[Link]

Atualizar um registro

Update é a instrução que nos serve para modificar nossos registros. Como para o caso de Delete,
necessitamos especificar por meio de Where quais são os registros que queremos fazer efetivas nossas
modificações. Ademais, obviamente, teremos que especificar quais são os novos valores dos campos que
desejamos atualizar. A sintaxe é deste tipo:

Update nome_tabela Set nome_campo1 = valor_campo1, nome_campo2 = valor_campo2,... Where

condicoes_de_selecao

Um exemplo aplicado:

Update clientes Set nome='José' Where nome='Pedro'

Mediante esta sentença mudamos o nome Pedro por José em todos os registros cujo nome seja Pedro.

Aqui também há que ser cuidadoso de não esquecer de usar Where, do contrário, modificaríamos todos os
registros de nossa tabela.

Informe de Rubén Alvarez

Mail: ruben@[Link]

Seleção de tabelas I

A seleção total ou parcial de uma tabela se realiza mediante a instrução Select. Em tal seleção há que
especificar:

-Os campos que queremos selecionar

-A tabela na qual fazemos a seleção

Em nossa tabela modelo de clientes poderíamos fazer, por exemplo, uma seleção do nome e endereço dos
clientes com uma instrução deste tipo:

Select nome, endereço From clientes

Se quiséssemos selecionar todos os campos, ou seja, toda a tabela, poderíamos utilizar o asterisco * da
seguinte forma:

Select * From clientes

Também é muito útil filtrar os registros mediante condições que vêem expressas depois da cláusula Where.
Se quiséssemos mostrar os clientes de uma determinada cidade usaríamos uma expressão como esta:

Select * From clientes Where cidade Like 'Rio de janeiro'

Ademais, poderíamos ordenar os resultados em função de um ou vários de seus campos. Para este último
exemplo poderíamos ordená-los por nome assim:

Select * From clientes Where cidade Like 'Rio de janeiro' Order By nome

Tendo em conta que pode haver mais de um cliente com o esmo nome, poderíamos dar um segundo critério
que poderia ser o sobrenome:

Select * From clientes Where cidade Like 'Rio de Janeiro' Order By nome, sobrenome

Se invertêssemos a ordem " nome,sobrenome " por " sobrenome, nome ", o resultado seria diferente.
Teríamos os clientes ordenados por sobrenome e aqueles que tivessem sobrenomes idênticos se sub-
classificariam pelo nome.

É possível também classificar por ordem inversa. Se por exemplo quiséssemos ver nossos clientes por
ordem de pedidos realizados tendo aos maiores em primeiro lugar escreveríamos algo assim:

Select * From clientes Order By pedidos Decres

Uma opção interessante é a de efetuar seleções sem coincidência. Se por exemplo, buscássemos saber em
que cidades se encontram nossos clientes sem a necessidade de que para isso apareça várias vezes a mesma
cidade, usaríamos uma sentença desta classe:

Select Distinct cidade From clientes Order By cidade

Assim evitaríamos ver repetido Rio de Janeiro tantas vezes quantos clientes tivermos nessa cidade.

Informe de Rubén Alvarez

Mail: ruben@[Link]

Seleção de tabelas II

Quisemos compilar na forma de tabela certos operadores que podem ser úteis em determinados casos. Estes
operadores serão utilizados depois da cláusula Where e podem ser combinados habilmente mediante
parênteses para otimizar nossa seleção a níveis bastante altos.

Operadores matemáticos:
> Maior que
< Menor que
>= Maior ou igual que
<= Menor ou igual que
<> Diferente
= Igual

Operadores lógicos
And
 Or
Not

Outros operadores
Seleciona os registros cujo valor de campo se assemelhe, não tendo em conta maiúsculas
Like
e minúsculas.
Dá um conjunto de valores para um campo para os quais a condição de seleção é (ou não)
In e Not In
válida
Is Null e Is Not Null Seleciona aqueles registros onde o campo especificado está (ou não) vazio.
Between...And Seleciona os registros compreendidos em um intervalo
Distinct Seleciona os registros não coincidentes
Desc Classifica os registros por ordem inversa

Curingas
* Substitui a todos os campos
% Substitui a qualquer coisa ou nada dentro de uma cadeia
_ Substitui só um caractere dentro de uma cadeia

Vejamos a seguir aplicações práticas destes operadores.

Nesta sentença selecionamos todos os clientes de Salvador cujo nome não seja Jose. Como se pode ver,
empregamos Like ao invés de = simplesmente para evitar inconvenientes devido ao emprego ou não de
maiúsculas.

Select * From clientes Where cidade Like 'salvador' And Not nome Like 'Jose'

Se quiséssemos recolher em uma seleção aos clientes de nossa tabela cujo sobrenome começa por A e
cujo número de pedidos esteja compreendido entre 20 e 40:

Select * From clientes Where sobrenomes like 'A%' And pedidos Between 20 And 40

O operador In, será visto mais adiante, é muito prático para consultas em várias tabelas. Para casos em uma
única tabela é empregada da seguinte forma:

Select * From clientes Where cidade In ('Salvador','Fortaleza','Florianopolis')

Desta forma selecionamos aqueles clientes que vivem nessas três cidades.

Informe de Rubén Alvarez

Mail: ruben@[Link]

Seleção de tabelas III

Um banco de dados pode ser considerado como um conjunto de tabelas. Estas tabelas, em muitos casos, estão
relacionadas entre elas e se complementam unas com outras.

Fazendo referência ao nosso clássico exemplo de um banco de dados para uma aplicação de e-comercio, a tabela
clientes que estivemos falando pode estar perfeitamente coordenada com uma tabela onde armazenamos os pedidos
realizados por cada cliente. Esta tabela de pedidos pode por sua vez, estar conectada com uma tabela onde
armazenamos os dados correspondentes a cada artigo do inventário.

Deste modo poderíamos facilmente obter informações contidas nessas três tabelas como pode ser a designação do
artigo mais popular em uma determinada região onde a designação do artigo seria obtida na tabela de artigos, a
popularidade (quantidade de vezes que esse artigo foi vendido) viria da tabela de pedidos e a região estaria
compreendida obviamente na tabela clientes.

Este tipo de organização baseada em múltiplas tabelas conectadas nos permite trabalhar com tabelas muito mais
manejáveis e ao mesmo tempo, nos evita copiar o mesmo campo em vários lugares já que podemos acessa-lo a
partir de uma simples chamada à tabela que o contém.

Neste capítulo veremos como, usando o que foi aprendido até agora, podemos realizar facilmente seleções sobre
várias tabelas. Definiremos antes de nada as diferentes tabelas e campos que vamos utilizar em nossos exemplos:

Tabela de clientes
Nome campo Tipo campo
id_cliente Numérico inteiro
nome Texto
sobrenomes Texto
endereço Texto
cidade Texto
cep Texto
telefone Numérico inteiro
email Texto

Tabela de pedidos
Nome campo Tipo campo
id_pedido Numérico inteiro
id_cliente Numérico inteiro
id_artigo Numérico inteiro
data Data
quantidade Numérico inteiro

Tabela de artigos
Nome campo Tipo campo
id_artigo Numérico inteiro
titulo Alfanumérico
autor Alfanumérico
editorial Alfanumérico
preço Numérico real

Estas tabelas podem ser utilizadas simultaneamente para extrair informações de todo tipo. Suponhamos que
queremos enviar um mailing a todos aqueles que tiverem realizado um pedido nesse mesmo dia. Poderíamos
escrever algo assim:

Select [Link], [Link] From clientes,pedidos Where [Link] like

'25/02/00' And pedidos.id_cliente= clientes.id_cliente
Como pode ser visto desta vez, depois da cláusula From, introduzimos o nome das duas tabelas de onde
tiramos as informações. Ademais, o nome de cada campo vai precedido da tabela de proveniência separado
ambos por um ponto. Nos campos que possuem um nome que só aparece em uma das tabelas, não é
necessário especificar sua origem embora na hora de ler sua sentença possa ser mais claro tendo esta
informação mais precisa. Neste caso, o campo data poderia ter sido designado como "data" ao invés de
"[Link]".

Vejamos outro exemplo mais para consolidar estes novos conceitos. Desta vez queremos ver o título do livro
correspondente a cada um dos pedidos realizados:

Select pedidos.id_pedido, [Link] From pedidos, artigos Where

pedidos.id_artigo=artigos.id_artigo

Na verdade a filosofia continua sendo a mesma que para a consulta de uma única tabela.

Informe de Rubén Alvarez

Mail: ruben@[Link]

Seleção de tabelas IV

Além dos critérios até agora explicados para realizar as consultas em tabelas, SQL permite também aplicar um
conjunto de funções pré-definidas. Estas funções, embora sejam básicas, podem nos ajudar em alguns momentos a
expressar nossa seleção de uma maneira mais simples sem ter que recorrer a operações adicionais por parte do
script que estivermos executando.

Algumas destas funções são representadas na seguinte tabela:

Função Descrição
Soma(campo) Calcula a soma dos registros do campo especificado
Avg(Campo) Calcula a média dos registros do campo especificado
Count(*) Proporciona o valor do número de registros que foram selecionados
Max(Campo) Indica qual é o valor máximo do campo
Min(Campo) Indica qual é o valor mínimo do campo

Dado que o campo da função não existe no banco de dados, pois o estamos gerando virtualmente, isto pode
criar inconvenientes quando estivermos trabalhando com nossos scripts na hora de tratar seu valor e seu
nome de campo. É por isso que o valor da função tem que ser recuperada a partir de um apelido que
nós especificaremos na sentença SQL a partir da instrução AS. A coisa poderia ficar assim:

Select Soma(total) As soma_pedidos From pedidos

A partir desta sentença calculamos a soma dos valores de todos os pedidos realizados e armazenamos esse
valor em um campo virtual chamado soma_pedidos que poderá ser utilizado como qualquer outro campo por
nossas páginas dinâmicas.

Obviamente, tudo que foi visto até agora pode ser aplicado neste tipo de funções de modo que, por exemplo,
podemos estabelecer condições com a cláusula Where construindo sentenças como esta:

Select Soma(quantidade) as soma_artigos From pedidos Where id_artigo=6

Isto nos proporcionaria a quantidade de exemplares de um determinado livro que foram vendidos.

Outra propriedade interessante destas funções é que permitem realizar operações com vários campos
dentro de um mesmo parênteses:
Select Avg(total/quantidade) From pedidos

Esta sentença dá como resultado o preço médio que estão sendo vendidos os livros. Este resultado não
tem porquê coincidir com o do preço médio dos livros presentes no inventário, já que, pode ser que as
pessoas tenham tendência a comprar os livros caros ou os baratos:

Select Avg(precio) as preco_venda From artigos

Uma cláusula interessante no uso das funções é Group By. Esta cláusula nos permite agrupar registros aos
quais vamos aplicar a função. Podemos por exemplo calcular o dinheiro gastado por cada cliente:

Select id_cliente, Soma(total) as soma_pedidos From pedidos Group By id_cliente

Ou saber o número de pedidos que foram realizados:

Select id_cliente, Count(*) as numero_pedidos From pedidos Group By id_cliente

As possibilidades como vemos são numerosas e pode ser práticas. Agora tudo fica à disposição de nossas
ocorrências e imaginação.

Informe de Rubén Alvarez

Mail: ruben@[Link]

Aproveite seu banco de dados

Bancos de dados deixaram há muito de ser simplesmente um repositório de informações usados para, no
máximo, organizar registros de tal forma que posteriormente pudessem ser indexados e/ou recuperados de
alguma forma. Hoje eles são ferramentas poderosas nas tomadas de decisões dentro das empresas que,
aproveitando-se da grande quantidade de dados lá existentes, extraem análises de vários tipos para
executarem os próximos movimentos e ações em seus mercados.

Mas o banco de dados por sí não opera milagres. É necessária a presença de um profissional conhecedor de
suas ferramentas e entranhas para tirar dele tudo aquilo que é possível. Este profissional normalmente
conhecido por DBA (Database Administrator) é a peça fundamental neste processo.

Por outro lado, os desenvolvedores de software mesmo não sendo especialistas em bancos de dados como os
DBA's, podem (e devem) aproveitar um pouco do que é oferecido por estas ferramentas para criar aplicações
mais rápidas, mais enxutas e mais “limpas”, aproveitando funções existentes mas que na maioria das vezes
são esquecidas no momento do desenvolvimento. Sobre isso que vamos falar hoje: aproveitamento de
funções de bancos de dados.

Obs: neste artigo usarei exemplos com o banco de dados MySQL e a linguagem PHP. Entretanto com toda a
certeza eles podem ser usados também com outras bases e linguagens de forma semelheante.

Somando valores, e concatenando campos

Com uma frequência impressionante, precisamos somar valores vindos de uma base de dados. Para fazer isso
dentro do código do programa, podemos ter:

$Rst = mysql_query("SELECT valor FROM tabela",$conexao);

while($valor=mysql_fetch_row($Rst)){
$resultado = $resultado + $valor[0];
}
print $resultado;

Mas, que tal assim:

$Rst = mysql_fetch_row(mysql_query("SELECT SUM(valor) FROM tabela", $conexao));
print $Rst[0];

Hmmm... de 5 linhas para duas? Mas como?

O “segredo” está na função SUM usada na segunda instrução. Ela simplesmente soma os valores do campo e
reduz um laço dentro do código. Com isso temos mais eficiência, velocidade e código limpo.

Um outro exemplo interessante pode ser visto quando, por exemplo, precisamos concatenar (unir) dois
campos. Para criar uma tabela no código, faríamos assim:

$Rst = mysql_query("SELECT nome, sobrenome FROM tabela",$conexao);

while($valor=mysql_fetch_row($Rst)){
echo 'Nome Completo: '.$valor[0].$valor[1].'<br>';
}

Mas também pode ser feito assim:

$Rst = mysql_query("SELECT CONCAT(nome, sobrenome) FROM tabela",$conexao);

while($valor=mysql_fetch_row($Rst)){
echo 'Nome Completo: '.$valor[0].'<br>';
}

Observe que não temos redução de linhas mas de caracteres que deixam o código mais limpo e aproveitamos
o processamento do banco ao invés do servidor web. A concatenação é feita pela função CONCAT no segundo
exemplo e depois somente precisamos imprimir uma variável, ao invés de duas, evitando assim problemas de
interpretação.

Datas? Não se perca

Muitas vezes o trabalho com datas é um pesadelo para o programador. Soma de dias, meses, em que dia da
semana caiu tal data e assim por diante.

Como para strings, bancos de dados também possuem um conjunto interessante de funções para a
manipulação de datas. Vamos ver algumas.

A data atual

Já vi muito programador criar função para a inserção de datas dentro de registros em bases de dados. Acredito que
isso seja por não conhecer uma pequena mas muito útil função chamada now(). Com ela, a data e/ou hora atual são
armazenadas em campos que necessitam este valor.

mysql_query("INSERT INTO tabela (data) VALUES (now())",$conexao);

Esta função simplesmente pega a informação de data e hora do servidor e insere no campo que deseja (neste
exemplo, em data). Mas cuidado; se o servidor está com a data/hora erradas, o valor que será inserido também
estará errado. Assim é interessante manter o servidor com horário sempre sincronizado com time servers existentes
na Internet.

Além disso, em alguns momentos precisamos saber em que semana estamos do ano ou ainda quantas semanas
faltam para o fim do ano. Ao invés de complexos códigos de linguagem, podemos usar a base de dados para nos
informar este resultado rapidamente. Por exemplo, em que semana do mês estamos agora?
print mysql_result(mysql_query("SELECT WEEK(now())",$conexao),0);
// resultado: 18

Aqui foi usada a função WEEK que retorna o número da semana de uma determinada data. Neste caso, a data
informada é “hoje” pois usamos como parâmetro de data a função now() que, como já apresentado anteriormente, é
a data atual do servidor.

Outra tarefa comum com datas é a soma de dias em uma data específica. Pode parecer simples mas como o formato
de data é diferente de formatos numéricos e strings, muitas vezes o resultado está errado e não sabemos porquê.

A forma mais simples é novamente deixar o banco de dados trabalhar por você. Então para acrescentar, por
exemplo, dez dias à data atual, fazemos:

print mysql_result(mysql_query("SELECT DATE_ADD(now(),INTERVAL 10 DAY)",$conexao),0);

// resultado: 2006-05-16

Com isso temos, em uma única linha, o valor que precisamos com a certeza que se trata de uma data válida,
inclusive contando meses que possuem somente 28,29 ou 30 dias (experimente fazer a soma de 10 dias à
data de 25/02/2006 e verá o resultado).

Conclusão

Pequenas e simples funções quando bem utilizadas facilitam a vida de qualquer desenvolvedor. Assim, antes
de “quebrar a cabeça” com dezenas de linhas de código para resolver um problema ou demanda, procure
saber se as ferramentas que está utilizando não dispõem de formas mais simples para a solução deste
problema. Muitas vezes a solução mais simples está diante dos olhos e perdemos tempo tentando algo
diferente.

Estes exemplos são pequenos e simples se comparados a enorme gama de opções existentes nas bases de
dados. Funções para data, strings, funções matemáticas e outras tantas estão disponíveis na maioria das
bases de dados relacionais existentes hoje em dia. Para o MySQL, acesse
[Link] Já para o PostgreSQL, acesse
[Link]

Espero que tenha gostado e aprendido um pouco mais.

Abraços!

Obs: Os códigos deste artigo podem ser encontrados em [Link] na seção de

downloads.

Copyright 2006 Paulino Michelazzo - [Link]

Obra licenciada sob Creative Commons Developing Nations 2.0

Informe de Paulino Michelazzo

Mail: paulino@[Link]
URL: [Link]

Tabelas temporárias no Sql Server

A sintaxe para criar uma tabela temporária no Sql Server é bastante simples, basta acrescentar o # antes do
nome da mesma.

Sintaxe:

CREATE TABLE #nomeTable

(
nomecampoA varchar(80),
nomecampoB money
)

Tabelas temporárias são muito utilizadas quando precisamos reunir vários registros de várias tabelas em uma
única seleção e exibi-las em uma aplicação qualquer ([Link].: Delphi, Visual Studio, [Link], etc.). .

É fundamental para aplicações cliente/servidor onde vários usuários estão acessando aquela procedure ao
mesmo tempo.

Aqui vai um exemplo prático da utilização de tabelas temporárias.É importante saber que a tabela temporária
só existe enquanto a procedure está sendo executada, após a execução da mesma ela é automaticamente
excluída. Aproveitei para colocar um exemplo utilizando o CASE do Sql Server

CREATE PROCEDURE TesteTabelaTemporaria

as

create table #tmpTotalPage

(mes smallint null,
totalmes smallint null,
mediames decimal(9, 3) null )
Insert into #tmpTotalPage ( mes, totalmes, media)
(select DATEPART(MONTH,data) as Mes, count(*) as TotalAcessos, null
from Acessos AS AC
INNER JOIN CadastroTB AS C ON [Link] = [Link]
group by DATEPART(MONTH,data))

select mes, totalmes,

media = case
when mes = 1 then totalmes / 31
when mes = 2 then totalmes / 28
when mes = 3 then totalmes / 30
when mes = 4 then totalmes / 31
when mes = 5 then totalmes / 30
when mes = 6 then totalmes / 31
when mes = 7 then totalmes / 30
when mes = 8 then totalmes / 31
when mes = 9 then totalmes / 30
when mes = 10 then totalmes / 31
when mes = 11 then totalmes / 30
when mes = 12 then totalmes / 31
end
from #tmpTotalPage

Por enquanto é só, até a próxima pessoal!

Informe de Fabio Correa

URL: [Link]

Adquirindo informações do Usuário com a classe System

Acredito que muitas pessoas já tiveram a necessidade de saber qual o nome do sistema operacional de um
usuário, o seu login do Windows, o diretório home etc. em suas aplicações. Se você é um dos que se
enquadram nessa situação, saiba que existe uma forma de adquirir essas informações: através da função
[Link]().

Segue um exemplo:

public class Main{

public static void main( String[] args ) {

[Link]("Usuario: " + [Link]("[Link]").toUpperCase());

[Link]("Sistema Operacional: " + [Link]("[Link]").toUpperCase());
}
}

Veja a saída:

Usuário: GLAUCIO
Sistema Operacional: WINDOWS XP

Abaixo seguem alguns parâmetros para a função getProperty():

[Link] – Retorna a arquitetura do Sistema operacional

[Link] – Retorna a versão do Sistema operacional
[Link] – Nome do Sistema Operacional
[Link] – Retorna a versão da JRE
[Link] – Diretório de Instalação Java
[Link] – Retorna o Class Path
[Link] – Diretório home do usuário
[Link] – Nome da conta do usuário

A lista completa encontra-se neste endereço:

[Link]

Essa recurso também pode ser usado em JSPs:

Código da JSP:

Saída:
Esse tipo de função permite que, por exemplo, quando um usuário acessar o sistema, seu login seja
obrigatoriamente o mesmo do Sistema Operacional. Isso oferece ao administrador do sistema total controle
de quem está usando a ferramenta, garantindo que o usuário possui uma conta na Intranet.

Informe de Glaucio Guerra

URL: [Link]

Alguns truques práticos

Eliminar chamadas aos bancos de dados

Em páginas do tipo portal nas que nas laterais se encontram links que são impressos a partir de bancos de
dados (distintas seções, serviços,...) existe sempre um efeito lentalizador devido a que se trata de páginas
altamente visitadas que efetuam múltiplas chamadas a BD sistematicamente em cada uma de suas páginas.

Uma forma de agilizar a visualização destas páginas é textualizando estes links a partir de scripts internos.
Colocamos o exemplo de Criarweb:

Como se pode ver, na lateral existem seções como "Suas Páginas", "Manuais" cujos links estão armazenados
no banco de dados. Entretanto, os links que se visualizam na página não foram obtidos por chamadas a banco
de dados e sim que, cada vez que um novo elemento da seção é adicionado, isto se atualiza
automaticamente, por meio de um script, um arquivo texto no qual o novo link é incluído e o mais antigo é
eliminado. De fato, este arquivo de texto é o que é inserido no código fonte da página. Deste modo, evitamos
meia dúzia de chamadas a bancos de dados cada vez que uma página é vista, o qual permite otimizar
recursos de servidor de uma maneira significativa.

Eliminar palavras curtas e repetições

Em situações na qual nosso banco de dados tem que armazenar campos de texto extremamente longos, e
tais campos são requeridos para realizar seleções do tipo LIKE '%algo%', os recursos do BD podem se ver
sensivelmente minguados Uma forma de ajudar a gerenciar este tipo de buscas é incluindo um campo
adicional.

Este campo adicional pode ser criado automaticamente por meio de scripts e nele incluiríamos o texto
original, do qual teremos eliminado palavras triviais como artigos, preposições ou possessivos. Além disso,
nos encarregaremos de eliminar as palavras que estiverem repetidas. Desta forma poderemos diminuir
sensivelmente o tamanho do campo que vai ser realmente consultado.

Comentamos em outros capítulos que os campos de texto de mais de 255 caracteres denominados memo não
podem ser indexados. Se ainda depois desta primeira filtragem nosso campo continuar sendo longo demais
para ser indexado, o que se pode fazer é corta-lo em pedaços de 255 caracteres de maneira que o
armazenemos em diferentes campos que poderão ser indexados e portanto, consultados com maior rapidez.

Informe de Rubén Alvarez

Mail: ruben@[Link]

Criação de tabelas

Em geral, a maioria dos bancos de dados possui potentes editores de bancos que permitem a criação rápida e
simples de qualquer tipo de tabela com qualquer tipo de formato.

Entretanto, uma vez que o banco de dados esteja hospedado no servidor, pode acontecer o caso de que
queiramos introduzir uma nova tabela já seja com caráter temporário (para gerenciar um carrinho de compra,
por exemplo) ou permanente, por necessidades concretas de nossa aplicação.

Nestes casos, podemos, a partir de uma sentença SQL, criar a tabela com o formato que desejarmos, o qual
pode nos poupar mais que um quebra-cabeça.

Estes tipos de sentenças são especialmente úteis para bancos de dados como Mysql, os quais trabalham
diretamente com comandos SQL e não por meio de editores.

Para criar uma tabela devemos especificar diversos dados: O nome que queremos atribuir, os nomes dos
campos e suas características. Ademais, pode ser necessário especificar quais destes campos vão ser índices
e de que tipo serão.

A sintaxe de criação pode variar ligeiramente de um banco de dados para outro já que os tipos de campos
aceitos, não estarão completamente padronizados.

A seguir explicamos meramente a sintaxe desta sentença e lhes propomos uma série de exemplos:

Sintaxe

Create Table nome_tabela

(
nome_campo_1 tipo_1
nome_campo_2 tipo_2
nome_campo_n tipo_n
Key(campo_x,...)
)

Ponhamos agora como exemplo a criação da tabela pedidos que empregamos em capítulos anteriores:

Create Table pedidos

(
id_pedido INT(4) NOT NULL AUTO_INCREMENT,
id_cliente INT(4) NOT NULL,
id_artigo INT(4)NOT NULL,
data DATE,
quantidade INT(4),
total INT(4), KEY(id_pedido,id_cliente,id_artigo)
)

Neste caso criamos os campos id os quais são considerados de tipo inteiro de uma longitude especificada pelo
número entre parênteses. Para id_pedido requeremos que tal campo se incremente automaticamente
(AUTO_INCREMENT) de uma unidade a cada introdução a um novo registro para, desta forma, automatizar sua
criação. Por outro lado, para evitar uma mensagem de erro, é necessário requerer que os campos que vão ser
definidos como índices não possam ser nulos (NOT NULL).

O campo data é armazenado com formato de data (DATE) para permitir sua correta exploração a partir das
funções previstas a tal efeito.

Finalmente, definimos os índices numerando-os entre parênteses precedidos da palavra KEY ou INDEX.

Da mesma forma poderíamos criar a tabela de artigos com uma sentença como esta:

Create Table artigos

(
id_artigo INT(4) NOT NULL AUTO_INCREMENT,
titulo VARCHAR(50),
autor VARCHAR(25),
editorial VARCHAR(25),
preco REAL,
KEY(id_artigo)
)

Neste caso pode-se ver que os campos alfanuméricos são introduzidos da mesma forma que os numéricos.
Voltemos a recordar que em tabelas que têm campos comuns é de vital importância definir estes campos da mesma
forma para o bom funcionamento da base.

Muitas são as opções que se oferecem ao gerar tabelas. Não vamos a trata-las detalhadamente, pois sai do
estritamente prático. Mostraremos somente alguns dos tipos de campos que podem ser empregados na criação de
tabelas com suas características:

Tipo Bytes Descrição

INT ou Números inteiros. Existem outros tipos de maior ou menor longitude específicos
4
INTEGER de cada banco de dados.
DOUBLE ou Números reais (grandes e com decimais). Permitem armazenar todo tipo de
8
REAL número não inteiro.
CHAR 1/caractere Alfanuméricos de longitude fixa pré-definida
VARCHAR 1/caractere+1 Alfanuméricos de longitude variável
DATE 3 Datas, existem múltiplos formatos específicos de cada banco de dados
BLOB 1/caractere+2 Grandes textos não indexáveis
BIT ou
1 Armazenam um bit de informação (verdadeiro ou falso)
BOOLEAN

Informe de Rubén Alvarez

Mail: ruben@[Link]

SQL e Programação de Banco de Dados

Quero me apresentar a você e iniciar um contato periódico para tratarmos assuntos relacionados à linguagem
SQL e à programação de banco de dados. Trabalho com banco de dados há algum tempo e sei o quanto é
importante trocar experiências com outros profissionais. Ninguém é capaz de extrair o máximo de um banco
de dados, mas, juntos, podemos realizar mais e de uma maneira cada vez melhor.

Minha experiência maior tem sido desenvolvida junto ao banco de dados Oracle, mas a padronização do
comando SQL e pelo fato de os conceitos de programação de um banco de dados serem facilmente aplicados
a qualquer outro banco de dados, faz com que possamos tratar destes assuntos quase que universalmente.

SQL

A linguagem SQL (Structured Query Language) é a base para utilização de bancos de dados relacionais. Com a
utilização dos comandos básicos (INSERT, DELETE, UPDATE e SELECT) pode-se resolver a maior parte dos
problemas relacionados a manutenção e extração de dados no banco de dados. Com o SQL é possível criar as
estruturas básicas de armazenamento, como tabelas e índices. Também há comandos específicos da
linguagem para o controle e segurança relacionado a um banco de dados. Em princípio, os comandos SQL são
divididos em:

 DDL (Data Definition Language) ou Linguagem de definição de dados

 DML (Data Manipulation Language) ou Linguagem de manipulação de dados
 DQL (Data Query Language) ou Linguagem de recuperação de dados
 DCL (Data Control Language) ou Linguagem de controle de dados

O SQL tem sido aprimorado ao longo do tempo. Duas entidades (ANSI – American National Standards Institute
e ISO – International Standards Organization) vêm, desde 1986, publicando padrões de especificação da
linguagem SQL.

Vamos discutir como abordar e tirar o máximo de proveito desta linguagem que é extremamente importante
para todos os profissionais de banco de dados. Os principais tópicos que serão de interesse envolvem:

 Fundamentos da linguagem SQL

 Extensões e particularidades de cada banco de dados
 Melhoria no desempenho de consultas
 Utilização de funções analíticas em banco de dados
 Tendências do uso da linguagem

Naturalmente a idéia é ter uma visão prática, com exemplos e com a abertura de uma discussão entre todos
os leitores.

Programação de Banco de Dados

Os comandos da linguagem SQL são muito poderosos, mas normalmente consegue-se melhorar o
desempenho das aplicações através da programação do banco de dados. Ao desenvolver módulos que sejam
executados diretamente no servidor diminui-se o tráfego de informações na rede, esconde-se boa parte das
estruturas das tabelas e agiliza-se o processamento e retorno das mensagens. Internamento o banco de
dados possui mecanismos integrados que permitem unir as estruturas tradicionais de programação com os
comandos SQL.

O banco de dados possui mecanismos próprios que podem ser utilizados em favor do desenvolvedor. Cada
banco de dados possui um conjunto específico de comandos que definem a linguagem de programação do
banco de dados. No caso do Oracle, a linguagem é o PL/SQL, o SQL Server possui o Transact-SQL, o DB2
possui sua própria linguagem de programação, o PostGreSQL possui diversas extensões que podem ser
utilizadas como linguagem de programação e o MySQL lançou sua mais recente versão com a possibilidade de
programar o servidor. Cada banco de dados é único sob este aspecto, mas todos trabalham sobre os mesmos
conceitos. É possível criar módulos programáveis, como funções, procedimentos, objetos, pacotes, gatilhos,
etc. Em todos os casos, há um engine responsável pela integração e execução dos módulos no servidor de
banco de dados.

Desta forma, os tópicos que serão trabalhados serão:

 Fundamentos de programação de banco de dados

 Aspectos avançados da programação
 Desempenho dos módulos
 Programação aliada à extração de dados gerenciais
 Tendências em programação de banco de dados

Como se pode notar, os tópicos são quase os mesmos da linguagem SQL. Também teremos artigos que
poderão ser implementados, testados e melhorados por todos os leitores.
Informe de Eber M. Duarte
URL: [Link]

Funções para buscas com datas em Access

Buscas com datas em Access Recebemos uma pergunta recentemente de um amigo que desejava realizar
buscas em Access utilizando, nas condições do Where, campos do tipo data. Depois de várias tentativas
resgatamos umas anotações que podem ser interessantes para publicar. Seguro que servem de ajuda a
outras pessoas que tenham que trabalhar com datas em consultas de Access.

A consulta era a seguinte:

Tenho uma tabela com vários campos. Dois deles são datas, que correspondem com um intervalo. Um deles é
a data de início do intervalo (datadesde) e outro a de final do intervalo (dataate)

Queria saber como se pode fazer uma consulta SQL em Access para obter os registros cujo intervalo de datas
contenha o dia de hoje.

Ou seja, que a data desde seja menor que hoje e data até seja maior que hoje.

Estivemos primeiro fazendo duas provas sem êxito, comparando as datas com operadores aritméticos. Em
alguns casos obtivemos a resposta esperada, porém nem sempre funcionavam as sentenças e tínhamos
problemas ao executa-las desde Access ou desde o servidor web, porque não devolviam os mesmos
resultados.

Função DateDiff()

Afinal, a resposta que propusemos passou por utilizar a função DateDiff, que serve para obter a diferença
entre duas datas. Por exemplo:

DateDiff("y", #06/10/2004#, Now())

Conta-nos os dias que passaram desde seis de outubro de 2004.

Nós podemos utiliza-la como condição em um where de uma sentença SQL. Por exemplo, para uma sentença
como esta:

DateDiff("y",A,B)

 Se são iguais, a função devolverá zero.

 Se A é uma data anterior a B, então a função devolverá um número de dias que será maior que zero.
 Se A é uma data posterior a B, então devolverá um número de dias que será menor que zero.

Temos que comparar o dia de hoje com as datas desde e ate. Hoje tem que ser maior que desde e menor que
ate. Fica como resultado esta sentença:

SELECT * FROM vuelos WHERE

DateDiff('y',datadesde,now())>=0
and DateDiff('y',dataate,nom())<=0

Nota: Há que ter cuidado com o idioma das datas, pois em português se escrevem de maneira diferente que em inglês.
Access tenta interpretar a data corretamente, por exemplo, se introduzimos 02/26/04 pensará que está trabalhando em
datas em inglês e se introduzimos 26/02/04 pensará que estamos escrevendo as datas em português. O problema é com
uma data como 02/02/04 que seu valor dependerá de como esteja configurado o Access, em português ou em inglês.

Função DatePart
Serve para extrair parte de uma data. Recebe dois parâmetros, o primeiro indica mediante um string a parte a
obter. O outro parâmetro é a data com a que se deseja trabalhar.

DatePart("m",data)
Neste caso está sendo indicado que se deseja obter o mês do ano. Outro valor possível para o primeiro
parágrafo é, por exemplo "yyyy", que se utiliza para obter o ano com quatro dígitos. Um exemplo de sentença
SQL que utiliza esta função pode ser a seguinte:

SELECT DatePart("yyyy",validadedesde) FROM voos

Função DateAdd

Esta última função que vamos ver no presente artigo serve para acrescentar à data, algo como dias, meses ou
anos. Para isso a função recebe três parâmetros, o primeiro corresponde com um string para indicar as
unidades do que desejamos acrescentar, por exemplo, dias, meses ou anos. O segundo parâmetro é o número
de dias, meses ou anos a adicionar e o terceiro parâmetro é a data a qual somar esses valores. Vemos um
exemplo de sua sintaxe:

DateAdd("yyyy",10,validadedesde)

Neste exemplo a função DateAdd devolveria uma data dez anos posterior a validadedesde. Outros valores
para o string do primeiro parâmetro são "d", para acrescentar dias, ou "m", para acrescentar meses.

Um exemplo do funcionamento desta função em uma sentença SQL é a seguinte:

SELECT DateAdd("yyyy",10,validadedesde) FROM voos

Informe de Miguel Angel Alvarez

Mail: miguel@[Link]

Função em SQL para o cálculo de dias de trabalho

/*Primeiramente declaramos que vamos criar uma função, neste caso se chama Dif Dias e recebe dois parâmetros, a data inicial do
período e a final*/

CREATE FUNCTION DifDias(@StartDate DATETIME,@EndDate DATETIME)

RETURNS integer
AS
Begin

//Com esta variavel calculamos quantos dias "normais" existem na classe de datas

DECLARE @DaysBetween INT

//Com esta variavel acumulamos os dias totais

DECLARE @BusinessDays INT

//esta variavel nos serve de contador para saber quando chegarmos ao ultimo dia da classe

DECLARE @Cnt INT

/*esta variavel eh a que comparamos para saber se o dia que esta calculando eh sabado ou domingo*/

DECLARE @EvalDate DATETIME

/*Estas duas variaveis servem para comparar as duas datas, se sao iguais, a funcao nos regressa um 0*/

DECLARE @ini VARCHAR(10)

DECLARE @fin VARCHAR(10)

//Iniciamos algumas variaveis

SELECT @DaysBetween = 0
SELECT @BusinessDays = 0
SELECT @Cnt=0

//Calculamos quantos dias normais existem na classe de datas

SELECT @DaysBetween = DATEDIFF(DAY,@StartDate,@EndDate) + 1

/*Ordenamos o formato das datas para que não importando como se proporcionem se comparem igual*/
SELECT @ini = (SELECT CAST((CAST(datepart(dd,@StartDate)AS
VARCHAR(2))+'/'+ CAST(datepart(mm,@StartDate)AS
VARCHAR(2))+'/'+CAST(datepart(yy,@StartDate)AS VARCHAR(4))) as
varchar(10)))
SELECT @fin = (SELECT CAST((CAST(datepart(dd,@EndDate)AS
VARCHAR(2))+'/'+ CAST(datepart(mm,@EndDate)AS VARCHAR(2))+'/'+
CAST(datepart(yy,@EndDate)AS VARCHAR(4)))as varchar(10)))

//Comparam-se as duas datas

IF @ini <>@fin
BEGIN

/*Se a diferenca de datas for igual a dois, eh porque so foi transcorrido um dia, portanto somente se valida de que nao vai marcar dias de
mais*/

IF @DaysBetween = 2
BEGIN
SELECT @BusinessDays = 1
END
ELSE
BEGIN
WHILE @Cnt < @DaysBetween
BEGIN

/*Iguala-se a data que vamos calcular para saber se eh sabado ou domingo na variavel @EvalDate somando os dias que marque o
contador, o qual nao deve ser maior que o numero total de dias que existem na classe de datas*/

SELECT @EvalDate = @StartDate + @Cnt

/*Utilizando a funcao datepart com o parametro dw que calcula que dia da semana corresponde uma data determinada, determinados
que nao seja sabado (7) ou domingo (1)*/

IF ((datepart(dw,@EvalDate) <> 1) and

(datepart(dw,@EvalDate) <> 7) )
BEGIN

/*Se nao eh sabado ou domingo, entao se soma um ao total de dias que queremos desdobrar*/

SELECT @BusinessDays = @BusinessDays + 1

END

//Soma-se um dia a mais ao contador

SELECT @Cnt = @Cnt + 1

END
END
END
ELSE
BEGIN

//Se fosse certo que as datas eram iguales se desdobraria em zero

SELECT @BusinessDays = 0
END

//Ao finalizar o ciclo, a funcao regressa o numero total de dias

return (@BusinessDays)
END

Informe de Rosendo Lopez Robles

Mail: soulus@[Link]

SQL com Oracle

Introdução:

Antes de começar, gostaria de dizer que este curso está baseado em Oracle, ou seja, os exemplos expostos e
o material foram atualizados sobre Oracle. Por outro lado dizer que acho interessante saber algo de SQL antes
de começar com MYSQL, já que, embora existam algumas mudanças insignificantes, sabendo manejar SQL
você saberá manejar MYSQL.

Algumas características:

SQL: Structured query language.

 Permite a comunicação com o sistema administrador de banco de dados.

 Em seu uso pode-se especificar o que quer o usuário.
 Permite fazer consulta de dados.

Tipos de dados:

CHAR:

 Têm uma longitude fixa.

 Armazena de 1 a 255.
 Se introduzimos uma cadeia de menos longitude que a definida se preencherá com brancos à direita
até ficar completa.
 Se introduzirmos uma cadeia de maior longitude que a fixada nos dará um erro.

VARCHAR:

 Armazena cadeias de longitude variável.

 A longitude máxima é de 2000 caracteres.
 Se introduzimos uma cadeia de menor longitude que a que está definida, se armazena com essa
longitude e não se preencherá com brancos nem com nenhum outro caractere à direita até completar
a longitude definida.
 Se introduzimos uma cadeia de maior longitude que a fixada, nos dará um erro.

NUMBER:

 Armazenam-se tanto inteiros como decimais.

 Number (precisão, escala)
 Exemplo:

X=number (7,2)
X=155'862 à Erro já que só pode tomar 2 decimais
X= 155'86 à Bem

Nota: A categoria máxima vai de 1 a 38.

LONG:

 Não armazena números de tamanho grande, e sim cadeias de caracteres de até 2 GB

DATE:

 Armazena a data. Armazena da seguinte forma:

Seculo/Ano/Mes/Dia/Hora/Minutos/Segundos

RAW:

 Armazena cadeias de Bytes (gráficos, áudio…)

LONGRAW:

 Como o anterior, mas com maior capacidade.

ROWID:

 Posição interna de cada uma das colunas das tabelas.

 Sentenças de consultas de dados

Select:
Select [ALL | Distinct] [expresao_coluna1, expresao_coluna2, …., | *]
From [nome1, nome_tabela1, …, nome_tabelan]
{[Where condicao]
[Order By expresao_coluna [Desc | Asc]…]};

Vamos explicar como ler a consulta anterior e assim seguir a pauta para todas as demais. Quando compomos []
significa que a que vai dentro deve existir, e sim, além disso, colocamos | significa que deveremos escolher um
valor dos que colocamos e não mais de um. Em troca se colocarmos {} significa que o que vai dentro das chaves
pode ir ou não, ou seja, é opcional e se colocará segundo a consulta.
Nota: No select o valor por default entre ALL e DISTINCT é ALL.
 Alias = O novo nome que se dá a uma tabela. Coloca-se entre aspas
 Order By = Ordena ascendentemente (Asc) (valor por default) ou descendentemente (Desc).
 All = Recupera todas as filas da tabela embora estejam repetidas.
 Distinct = Só recupera as filas que são distintas.
 Desc Emple; = Dá um resumo da tabela e suas colunas. Neste caso da tabela Emple.
 Not Null= Se aparecer em uma lista de uma coluna significa que a coluna não pode ter valores nulos.
 Null= Se estiver nulo.

Nota: Note que cada consulta de SQL que fazemos temos de termina-la com um ponto e vírgula";".

Vários exemplos para vê-lo mais claro:

SELECT JOGADOR_NO, SOBRENOME, POSICAO, TIME

FROM JOGADORES
WHERE TIME_NO = 'FLAMENGO'
ORDER BY SOBRENOME;

Este exemplo mostra o número do jogador (jogador_no) o sobrenome (Sobrenome), a posição na que joga
(Posição), e o time (Time) ao que pertence.
Selecionará todos os dados da tabela jogadores onde (Where) o nome do time (Time_No) for igual que a
palavra 'Flamengo' e se ordenará (order by) sobrenome. Observe também que não põe nem 'Distinct' nem
'All'. Por padrão, gerará a sentença com ALL.

SELECT *
FROM JOGADORES
WHERE POSICAO = 'ATACANTE'
ORDER BY JOGADOR_NO;

Este exemplo mostra todos os campos da tabela jogadores onde (Where) a posição for igual que 'Atacante' e o
ordena por número de jogador. Ao não colocar nada se supõe que é ascendentemente (Asc).

SELECT *
FROM JOGADORES
WHERE TIME_NAO = 'FLAMENGO' AND POSICAO = 'ATACANTE'
ORDER BY SOBRENOME DESC, JOGADOR_NAO ASC;

Neste exemplo seleciona todos os campos da tabela jogadores onde (Where) o nome do time for igual a
'Flamengo' e a posição dos jogadores for igual a 'Atacante'. Por ultimo os ordena por 'Sobrenome'
descendentemente e por número de jogador ascendentemente.
Informe de Agustin Jareño
Mail: agustin@[Link]
URL: [Link]

SQL com Oracle. Operadores

Operadores aritméticos:

+ = Soma
- = Subtração
* = Multiplicação
/ = Divisião

Operadores de comparação e lógicos:

!> = Diferente
>= = Maior ou igual que
<= = Menor ou igual que = = Igual que
Like = Utiliza para unir cadeias de caracteres. Propriedades:
% = representa qualquer cadeia de caracteres de 0 ou mais caracteres.
_= representa um único caractere qualquer.
Not = Negação
And = e
a and b
Certo se são certas a e b.
Or = o
a or b
Certo se a ou b são certas

Vejamos dois exemplos:

Obtemos os dados dos jogadores cujos sobrenomes comecem com a letra "S":

SELECT SOBRENOMES
FROM JOGADORES
WHERE SOBRENOMES LIKE 'S%';

Obtemos aqueles sobrenomes que tiverem uma "R" na segunda posição:

SELECT SOBRENOMES
FROM JOGADORES
WHERE SOBRENOMES LIKE '_R*';

Obtemos aqueles sobrenomes que começam por "A" e tem uma "o" em seu interior:

SELECT SOBRENOMES
FROM JOGADORES
WHERE SOBRENOMES LIKE 'A%O%';

Comprovação com conjuntos de valores:

 In= permite saber se uma expressão pertence ou não a um conjunto de valores.

 Between= permite saber se uma expressão está ou não entre esses valores:

Exemplo:

SELECT SOBRENOMES
FROM JOGADORES
WHERE JOGADOR_NUM IN (10, 20);

Seleciona os sobrenomes dos jogadores onde o número de jogador (Jogador_num) seja (In) ou 10 ou 20
 SELECT SOBRENOMES
FROM JOGADORES
WHERE SALARIO NOT BETWEEN 15000000 AND 20000000;

Seleciona os sobrenomes dos jogadores onde o salário destes não esteja entre (Not Between) 15000000 e
20000000.

Informe de Agustin Jareño

Mail: agustin@[Link]
URL: [Link]

Sub-consultas SQL
Sub-consultas:

Consulta que se faz sobre os dados que nos dá outra consulta. Seu formato é:

SELECT______
FROM________
WHERE CONDICAO OPERADOR (SELECT ______
FROM ___________
WHERE CONDICAO OPERADOR); Exemplo:

Obtemos os jogadores com a mesma posição que "Sanchez":

SELECT SOBRENOME
FORM EMPLE
WHERE POSICAO = (SELECT OFICIO
FROM EMPLE
WHERE SOBRENOME LIKE 'GIL');

Selecionamos em todos os campos da tabela Jogadores cuja sede está em Madrid ou Barcelona:

SELECT *
FROM JOGADORES
WHERE EQUIPE_NOM IN (SELECT EQUIPE_NOM
FROM SEDE
WHERE LOC IN ('MADRID', 'BARCELONA');
FROM SEDE
WHERE LOC IN ('MADRID', 'BARCELONA');

Informe de Agustin Jareño

Mail: agustin@[Link]
URL: [Link]

Funções SQL
Funções de valores simples:

ABS(n)= Devolve o valor absoluto de (n).

CEIL(n)=Obtém o valor inteiro imediatamente superior ou igual a "n".
FLOOT(n) = Devolve o valor inteiro imediatamente inferior ou igual a "n".
MOD (m, n)= Devolve o resto resultante de dividir "m" entre "n".
NVL (valor, expressão)= Substitui um valor nulo por outro valor.
POWER (m, exponente)= Calcula a potência de um número.
ROUND (numero [, m])= Arredonda números com o número de dígitos de precisão indicados.
SIGN (valor)= Indica o signo do "valor".
SQRT(n)= Devolve a raiz quadrada de "n".
TRUNC (numero, [m])= Trunca números para que tenham uma certa quantidade de dígitos de precisão.
VAIRANCE (valor)= Devolve a média de um conjunto de valores.

Funções de grupos de valores:

AVG(n)= Calcula o valor médio de "n" ignorando os valores nulos.
COUNT (* | Expressão)= Conta o número de vezes que a expressão avalia algum dado com valor não nulo. A opção
"*" conta todas as filas selecionadas.
MAX (expressão)= Calcula o máximo.
MIN (expressão)= Calcula o mínimo.
SUM (expressão)= Obtém a soma dos valores da expressão.
GREATEST (valor1, valor2…)= Obtém o maior valor da lista.
LEAST (valor1, valor2…)= Obtém o menor valor da lista.

Funções que devolvem valores de caracteres:

CHR(n) = Devolve o caractere cujo valor em binário é equivalente a "n".

CONCAT (cad1, cad2)= Devolve "cad1" concatenada com "cad2".
LOWER (cad)= Devolve a cadeia "cad" em minúsculas.
UPPER (cad)= Devolve a cadeia "cad" em maiúsculas.
INITCAP (cad)= Converte a cadeia "cad" a tipo título.
LPAD (cad1, n[,cad2])= Adiciona caracteres à esquerda da cadeia até que tenha uma certa longitude.
RPAD (cad1, n[,cad2])= Adiciona caracteres à direita até que tenha uma certa longitude.
LTRIM (cad [,set])= Suprime um conjunto de caracteres à esquerda da cadeia.
RTRIM (cad [,set])= Suprime um conjunto de caracteres à direita da cadeia.
REPLACE (cad, cadeia_busca [, cadeia_substitucao])= Substitui um caractere ou caracteres de uma cadeia com 0 ou
mais caracteres.
SUBSTR (cad, m [,n])= Obtém parte de uma cadeia.
TRANSLATE (cad1, cad2, cad3)= Converte caracteres de uma cadeia em caracteres diferentes, segundo um plano de
substituição marcado pelo usuário.

Funções que devolvem valores numéricos:

ASCII(cad)= Devolve o valor ASCII da primeira letra da cadeia "cad".

INSTR (cad1, cad2 [, comeco [,m]])= Permite uma busca de um conjunto de caracteres em uma cadeia, mas não
suprime nenhum caractere depois.
LENGTH (cad)= Devolve o número de caracteres de cad.

Funções para o manejo de datas:

SYSDATE= Devolve a data do sistema.

ADD_MONTHS (data, n)= Devolve a data "data" incrementada em "n" meses.
LASTDAY (data)= Devolve a data do último dia do mês que contém "data".
MONTHS_BETWEEN (data1, data2)= Devolve a diferença em meses entre as datas "data1" e "data2".
NEXT_DAY (data, cad)= Devolve a data do primeiro dia da semana indicado por "cad" depois da data indicada por
"data".

Funções de conversão:

TO_CHAR= Transforma um tipo DATE ou NUMBER em uma cadeia de caracteres.

TO_DATE= Transforma um tipo NUMBER ou CHAR em DATE.
TO_NUMBER= Transforma uma cadeia de caracteres em NUMBER.

Informe de Agustin Jareño

Mail: agustin@[Link]
URL: [Link]

Agrupamento e combinação de elementos com SQL

Agrupamento de elementos. Group by e Having:

Para saber qual é o salário médio de cada departamento da tabela Jogadores seria:

SELECT TIME_NO, AVG (SALARIO) "SALARIO MEDIO"

FROM JOGADORES
 GROUP BY DEPT_NO;

A sentença "Select" possibilita agrupar um ou mais conjuntos de filas. O agrupamento se realiza mediante a
cláusula "GROUP BY" pelas comunas especificadas e na ordem especificada. Formato:

SELECT…
FROM…
GROUP BY COLUNA1, COLUNA2, COLUNAN…
HAVING CONDICAO
GROUP BY …

Os dados selecionados na sentença "Select" que leva o "Group By" devem ser:
 Uma constante.

 Uma função de grupo (SUM, COUNT, AVG…)

 Uma coluna expressa no Group By.

A cláusula Group By serve para calcular propriedades de um ou mais conjuntos de filas. Se se seleciona mais
de um conjunto de filas, Group By controla que as filas da tabela original sejam agrupadas em um temporário.

A cláusula Having se emprega para controlar qual dos conjuntos de filas se visualiza. Avalia-se sobre a tabela
que devolve o Group By. Não pode existir sem Group By.

Having é parecido ao Where, porém trabalha com grupos de filas; pergunta por uma característica de grupo,
ou seja, pergunta pelos resultados das funções de grupo, o qual Where não pode fazer.

Combinação externa (outer joins):

Permite-nos selecionar algumas filas de uma tabela embora estas não tenham correspondência com as filas
da outra tabela com a que se combina. Formato:

SELECT TABELA1.COLUNA1, TABELA1.COLUNA2, TABELA2.COLUNA1, TABELA2.COLUNA2

FROM TABELA1, TABELA2
WHERE TABELA1.COLUNA1 = TABELA2.COLUNA1 (+);

Isto seleciona todas as filas da tabela "tabela1" embora não tenham correspondência com as filas da tabela
"tabela2", se utiliza o símbolo +.
O resto de colunas da tabela "tabela2" se preenche com NULL.

Union, intersec e minus:

Permite combinar os resultados de vários "Select" para obter um único resultado. Formato:

SELECT… FROM… WHERE…

OPERADOR_DE_CONJUNTO
SELECT…FROM…WHERE…

UNION= Combina os resultados de duas consultas. As filas duplicadas que aparecem se reduzem a uma fila
única.
UNION ALL= Como a anterior, porém aparecerão nomes duplicados.
INTERSEC= Devolve as filas que são iguais em ambas consultas. Todas as filas duplicadas serão eliminadas.
MINUS= Devolve aquelas filas que estão na primeira "Select" e não estão na segunda "Select". As filas
duplicadas do primeiro conjunto se reduzirão a uma fila única antes que comece a comparação com o outro
conjunto.

Regras para a utilização de operadores de conjunto:

 As colunas das duas consultas se relacionam em ordem, da esquerda à direita.
 Os nomes de coluna da primeira sentença "Select" não tem porque ser os mesmos que os nomes da
segunda.
 Os "Select" necessitam ter o mesmo número de colunas.
 Os tipos de dados devem coincidir, embora a longitude não tem que ser a mesma.
>

Informe de Agustin Jareño

Mail: agustin@[Link]
URL: [Link]

Manipulação de dados com SQL

Insert, Update e Delete:

Insert:

Adicionam-se filas de dados em uma tabela:

INSERT INTO NOMBTABELA [(COL [,COL]…)]

VALUES (VALOR [,VALOR]…);

Nometabela= É a tabela na que se vão inserir as filas.

Propriedades:

 Se as colunas não se especificam na cláusula Insert se consideram, por padrão, todas as colunas da
tabela.
 As colunas às quais damos valores se identificam pelo seu nome.
 A associação coluna valor é posicional.
 Os valores que se dão às colunas devem coincidir com o tipo de dado definido na coluna.
 Os valores constantes de tipo caractere tem de ir fechados entre aspas simples (' ') (os de tipo data
também).

Com Select:

Adicionam-se tantas filas como devolva a consulta:

INSERT INTO NOMBTABELA [(COL [,COL]…)]

SELECT {COLUNA [, COLUNA]… | *}
FROM NOMETABELA2 [CLAUSULAS DE SELECT];

Update:

Atualiza os valores das colunas para uma ou várias filas de uma tabela:

UPDATE NOMETABELA
SET COLUNA1= VALOR1, …, COLUNAN= VALORN
WHERE CONDICAO;

Set= Indica as colunas que vão se atualizar e seus valores.

Com Select:

Quando a subconsulta (orden select) forma parte de SET, deve selecionar o mesmo número de colunas, (com
tipos de dados adequados) que os que existem entre parênteses ao lado de SET.

UPDATE NOMETABELA
SET COLUNA= VALOR1, COLUNA2= VALOR2, …
WHERE COLUNA3= (SELECT…)

OU

UPDATE NOMETABELA
SET (COLUNA1, COLUNA2, …)= (SELECT …)
WHERE CONDICAO;

Delete:
Elimina uma ou várias filas de uma tabela:

DELETE [FROM] NOMETABELA

WHERE CONDICAO;

Informe de Agustin Jareño

Mail: agustin@[Link]
URL: [Link]

Chaves primárias com SQL com Oracle

Rollback:

Permite ir até o último COMMIT feito ou em seu padrão até o começo das ordens com o que estas não se
executam.

Commit:

Quando executamos ordens, estas não são criadas na tabela até que coloquemos esta ordem, portanto as
mudanças realizadas se perderão se ao sair do programa não realizarmos esta ação. Pode se programar para
que seja feito automaticamente.

Algumas ordens que levam COMMIT implícito:

 QUIT
 EXIT
 CONNECT
 DISCONNECT
 CREATE TABLE
 CREATE VIEW
 GRANT
 REVOQUE
 DROP TABLE
 DROP VIEW
 ALTER
 AUDIT
 NO AUDIT

Criação de uma tabela:

Seu primeiro caractere deve ser alfabético e o resto pode ser letras, números e o caractere sublinhado.

CREATE TABLE NOMETABELA

(COLUNA1 TIPO_DADO {NOT NULL},
COLUNA2 TIPO_DADO {NOT NULL},
…
) TABLESPACE ESPACO_DE_TABELA;

Características:

 As definições individuais de colunas se separam mediante vírgulas.

 Não se coloca vírgula depois da última definição de coluna.
 As maiúsculas e minúsculas são indiferentes.

Os usuários podem consultar as tabelas criadas por meio da vista USER_TABLES.

Integridade de dados:

A integridade faz referência ao fato de que os dados do banco de dados têm que se ajustar às restrições antes
de se armazenar nele. Uma restrição de integridade será:
Uma regra que restringe classe de valores para uma ou mais colunas na tabela.

Restrições em create table:

Usamos a cláusula CONSTRAINT, que pode restringir uma só coluna ou um grupo de colunas de uma mesma
tabela.
Existem dois modos de especificar restrições:
 Como parte da definição de colunas.
 Afinal, uma vez especificadas todas as colunas.

Formato:

CREATE TABLE NOME_TABELA

(COLUNA1 TIPO_DE_DADO
{CONSTRAINT NOME_RESTRICAO}
{NOT NULL}
{UNIQUE}
{PRIMARY KEY}
{DEFAULT VALOR}
{REFERENCES NOMETABELA [(COLUNA, [,COLUNA])
{ON DELETE CASCADE}}
{CHECK CONDICAO},
COLUNA2...
)
{TABLESPACE ESPACO_DE_TABELA} ;
CREATE TABLE NOME_TABELA
(COLUNA1 TIPO_DADO ,
COLUNA2 TIPO_DADO,
COLUNA3 TIPO_DADO,
...
{CONSTRAINT NOMERESTRICAO}
[{UNIQUE} | {PRIMARY KEY} (COLUNA [, COLUNA])],
{CONSTRAINT NOMERESTRICAO}
{FOREIGN KEY (COLUNA [, COLUNA])
REFERENCES NOMETABELA {(COLUNA [,
COLUNA])
{ON DELETE CASCADE}},
{CONSTRINT NOMERESTRICAO}
{CHECK (CONDICAO)}
…
)[TABLESPACE ESPACO_DE_TABELA];

Informe de Agustin Jareño

Mail: agustin@[Link]
URL: [Link]

Definição de chaves para tabelas e restrições

Chave primária: Primary key

É uma coluna ou um conjunto de colunas que identificam univocamente a cada fila. Deve ser única, não nula e
obrigatória. Como máximo, podemos definir uma chave primária por tabela.
Esta chave pode ser referenciada por uma coluna ou colunas. Quando se cria uma chave primária,
automaticamente se cria um índice que facilita o acesso à tabela.

Formato de restrição de coluna:

CREATE TABLE NOME_TABELA

(COL1 TIPO_DADO [CONSTRAINT NOME_RESTRICAO] PRIMARY KEY
COL2 TIPO_DADO
…
)[TABLESPACE ESPACO_DE_TABELA];

Formato de restrição de tabela:

CREATE TABLE NOME_TABELA

(COL1 TIPO_DADO,
COL2 TIPO_DADO,
…
[CONSTRAINT NOMERESTRICAO] PRIMARY KEY (COLUNA [,COLUNA]),
…
)[TABLESPACE ESPACO_DE_TABELA];

Chaves alheias: Foreign Key:

Está formada por uma ou várias colunas que estão associadas a uma chave primária de outra ou da mesma
tabela. Pode-se definir tantas chaves alheias quantas se necessite, e podem estar ou não na mesma tabela
que a chave primária. O valor da coluna ou colunas que são chaves alheias deve ser: NULL ou igual a um valor
da chave referenciada (regra de integridade referencial).

Formato de restrição de coluna:

CREATE TABLE NOME_TABELA

(COLUNA1 TIPO_DADO
[CONSTRAINT NOMERESTRICAO]
REFERENCES NOMETABELA [(COLUNA)] [ON DELETE CASCADE]
…
)[TABLESPACE ESPACO_DE_TABELA];

Formato de restrição de tabela:

CREATE TABLE NOME_TABELA

(COLUNA1 TIPO_DADO,
COLUNA2 TIPO_DADO,
…
[CONTRAINT NOMERESTRICAO]
FOREIGN KEY (COLUNA [,COLUNA])
REFERENCES NOMETABELA [(COLUNA [,
COLUNA])]
[ON DELETE CASCADE],
)[TABLESPACE ESPACO_DE_TABELA];

Notas:

 Na cláusula REFERENCES indicamos a tabela a qual remite a chave alheia.

 Há que criar primeiro uma tabela e depois aquela que lhe faz referência.
 Há que apagar primeiro a tabela que faz referência a outra tabela e depois a tabela que não faz
referência.
 Eliminado em cascata (ON DELETE CASCADE): Se eliminarmos uma fila de uma tabela mãe, todas as
filas da tabela detalhe cuja chave alheia seja referenciada se eliminarão automaticamente. A restrição
se declara na tabela detalhe. A mensagem "n filas eliminadas" só indica as filas eliminadas da tabela
mãe.

NOT NULL: Significa que a coluna não pode ter valores nulos.
DEFAULT: Proporcionamos a uma coluna um valor padrão quando o valor da coluna não se especifica na cláusula
INSERT. Na especificação DEFAULT é possível incluir várias expressões: constantes, funções SQL e variáveis
UID e SYSDATE.
Verificação de restrições: CHECK: Atua como uma cláusula where. Pode fazer referência a uma ou mais colunas,
porém não a valores de outras filas. Em uma cláusula CHECK não se podem incluir sub-consultas nem as pseudo-
consultas SYSDATE, UID e USER.
Nota: A restrição NOT NULL é similar a CHECK (NOME_COLUNA IS NOT NULL)

UNIQUE: Evita valores repetidos na mesma coluna. Pode conter uma ou várias colunas. É similar à restrição
PRIMARY KEY, salvo que são possíveis várias colunas UNIQUE definidas em uma tabela. Admite valores NULL.
Assim como em PRIMARY KEY, quando se define uma restrição UNIQUE se cria um índice automaticamente.

Vistas do dicionário de dados para as restrições:

Contém informação geral as seguintes:

USER_CONSTRAINTS: Definições de restrições de tabelas propriedade do usuário.

ALL_CONSTRAINTS: Definições de restrições sobre tabelas as quais pode acessar o usuário.
DBA_CONSTRAINTS: Todas as definições de restrições sobre todas as tabelas.

Criação de uma tabela com dados recuperados em uma consulta:

CREATE TABLE: permite criar uma tabela a partir da consulta de outra tabela já existente. A nova tabela
conterá os dados obtidos na consulta. Realiza-se esta ação com a cláusula AS colocada ao final da ordem
CREATE TABLE.

CREATE TABLE NOMETABELA

(COLUNA [,COLUNA]
)[TABLESPACE ESPACO_DE_TABELA]
AS CONSULTA;

Não é necessário especificar tipos nem tamanho das consultas, já que vêm determinadas pelos tipos e os
tamanhos das recuperadas na consulta.
A consulta pode ter uma sub-consulta, uma combinação de tabelas ou qualquer sentença select válida.
As restrições COM NOME não se criam em uma tabela desde a outra, só se criam aquelas restrições que
carecem de nome.

Informe de Agustin Jareño

Mail: agustin@[Link]
URL: [Link]

Supressão e modificação de tabelas com SQL

Supressão de tabelas:

DROP TABLE: suprime uma tabela do banco de dados. Cada usuário pode eliminar suas próprias tabelas,
porém só o administrador ou algum usuário com o privilégio "DROP ANY TABLE" pode eliminar as tabelas de
outro usuário. Ao suprimir uma tabela também se suprimem os índices e os privilégios associados a ela. As
vistas e os sinônimos criados a partir desta tabela deixam de funcionar, mas continuam existindo no banco de
dados, portanto deveríamos elimina-los.
Exemplo:

DROP TABLE [USUARIO].NOMETABELA [CASCADE CONSTRAINTS];

TRUNCATE: permite suprimir todas as filas de uma tabela e liberar o espaço ocupado para outros usos sem
que reapareça a definição da tabela do banco de dados. Uma ordem TRUNCATE não se pode anular, assim
como também não ativa os disparadores DELETE.

TRUNCATE TABLE [USUARIO.]NOMETABELA [{DROP | REUSE} STORAGE];

Modificação de tabelas:

Modificam-se as tabelas de duas formas: Mudando a definição de uma coluna (MODIFY) ou acrescentando
uma coluna a uma tabela existente (ADD):
Formato:

ALTER TABLE NOMETABELA

{[ADD (COLUNA [,COLUNA]…)]
[MODIFY (COLUNA [,COLUNA]…)]
[ADD CONSTRAINT RESTRICAO]
[DROP CONSTRAINT RESTRICAO]};

ADD= Adiciona uma coluna ou mais no final de uma tabela.

MODIFY= Modifica uma ou mais colunas existentes na tabela.
ADD CONSTRAINT= Adiciona uma restrição à definição da tabela.
DROP CONSTRAINT= Elimina uma restrição da tabela.

Na hora de adicionar uma coluna a uma tabela há que ter em conta:

  Se a coluna não estiver definida como NOT NULL pode-se adicionar em qualquer momento.

 Se a coluna estiver definida como NOT NULL pode-se seguir estes passos:
1. Adiciona-se uma coluna sem especificar NOT NULL.
2. Dá-se valor à coluna para cada uma das filas.
3. Modifica-se a coluna NOT NULL.

Ao modificar uma coluna de uma tabela, há que ter em conta:

 Pode-se aumentar a longitude de uma coluna em qualquer momento.

 É possível aumentar ou diminuir o número de posições decimais em uma coluna de tipo NUMBER.
 Se a coluna for NULL em todas as filas da tabela, pode-se diminuir a longitude e modificar o tipo de
dado.
 A opção MODIFY… NOT NULL só será possível quando a tabela não contiver nenhuma fila com valor
nulo na coluna que se modifica.

Adição de restrições:

Com a ordem ALTER TABLE se acrescentam restrições a uma tabela.

Formato:

ALTER TABLE NOMETABELA

ADD CONSTRAINT NOMECONSTRAINT…

Eliminação de restrições:

A ordem ALTER TABLE com a cláusula DROP CONSTRAINT; com a que se eliminam as restrições com nome e
as atribuídas pelo sistema. Formato:

ALTER TABLE NOMETABELA

DROP CONSTRAINT NOME_CONSTRAINT,
NOME_RESTRICAO:

Informe de Agustin Jareño

Mail: agustin@[Link]
URL: [Link]

Gerenciamento de vistas em SQL

Criação e uso de vistas

Não contém informação por si mesmas, e sim, estão baseadas nas que contém outras tabelas e reflete os
dados destas.
Se suprime uma tabela a vista associada se invalida. Formato:

CREATE [OR REPLACE] VIEW NOMEVISTA

[(COLUNA [,COLUNA])]
AS CONSULTA;

AS CONSULTA= Determina as colunas e as tabelas que aparecerão na vista.

[OR REPLACE]= Cria de novo a vista se já existia.

Para consultar a vista criada, USER_VIEWS:

SELECT VIEW_NAME FROM…

Nota: ao eliminar as tabelas, as vistas dessas tabelas não se eliminam e ficam inutilizadas.
Eliminação de vistas

Faz-se com DROP VIEW. Formato:

DROP VIEW NOMEVISTA;

Operações sobre vistas

Pode-se realizar as mesmas operações que se fazem sobre as tabelas. Restrições:

 Atualização. Se uma vista está baseada em uma só tabela, pode-se modificar as filas da vista.
 A modificação da vista muda a tabela sobre a qual está definida.
 Eliminação de filas através de uma vista= Para eliminar filas de uma tabela através de uma vista, esta
se deve criar:
o Com filas de uma só tabela.
o Sem utilizar a cláusula GROUP BY nem DISTINCT.
o Sem usar funções de grupo ou referências a pseudo-colunas.
 Atualização de filas através de uma vista: Para atualizar filas em uma tabela através de uma vista, esta
tem que estar definida segundo as restrições anteriores e, ademais, nenhuma das colunas que vai se
atualizar terá definido como uma expressão.
 Inserção de filas através de uma vista: Para inserir filas em uma tabela através de uma vista, há que
ter em conta todas as restrições anteriores e, ademais, todas as colunas obrigatórias da tabela
associada devem estar presentes na vista.
 Manejo de expressões e de funções em vistas: Pode-se criar vistas usando funções, expressões em colunas e
consultas avançadas, porém unicamente se emparelham consultar estas vistas. Também podemos modificar
filas sempre e quando a coluna que vai se modificar não for a coluna expressada em forma de cálculo ou
com funções.

Nota: Não é possível inserir filas se as colunas da vista contém cálculos ou funções.

Mudanças de nome

RENAME muda o nome de uma tabela, vista ou sinônimo. O novo nome não pode ser uma palavra reservada
no nome de um objeto que o usuário tiver criado. As restrições de integridade, os índices e as permissões
dadas ao objeto se transferem automaticamente ao novo objeto.

REANME NOME_ANTERIOR TO NOME_NOVO;

Com esta ordem não podemos re-nomear colunas de uma tabela, estas se re-nomeiam mediante CREATE
TABLE AS…

Informe de Agustin Jareño

Mail: agustin@[Link]
URL: [Link]

Usuários em Oracle

É um nome definido no banco de dados que se pode conecta-lo e acessar a determinados objetos segundo
certas condições que o administrador estabelece.
Os objetos do dicionário de dados aos que um usuário pode acessar, encontram-se na vista DICTIONARY, que
é propriedade do usuário SYS.

DESC DICTIONARY;

Com a ordem:

SELECT TABLENAME FROM DICTIONARY;

Visualizam-se os objetos do dicionário de dados aos que se pode acessar.

Criação de usuários:

CREATE USER NOME_USUARIO

IDENTIFIED BY SENHA_ACESSO
[DEFAULT TABLESPACE ESPACO_TABELA]
[TEMPORARY TABLESPACE ESPACO_TABELA]
[COTA {INTEIRO {K | M} | UNLIMITED } ON ESPACO_TABELA]
[PROFILE PERFIL];

DEFAULT TABLESPACE= Atribui a um usuário o tablespace padrão para armazenar os objetos que crie. Se não
se atribui nenhum, o tablespace padrão é SYSTEM.
TEMPORARY TABLESPACE= Especifica o nome do tablespace para trabalhar temporais. Se não se especifica
nenhum, o tablespace padrão é SYSTEM.
QUOTA= Atribui um espaço em megabites ou kilobites no tablespace atribuído. Se não se especifica esta
cláusula, o usuário não tem cota atribuída e não poderá criar objetos no tablespace. Para ter espaço e acesso
ilimitado a um tablespace é:

GRANT UNLIMITED TABLESPACE NOME_TABLESPACE;

PROFILE= Atribui um perfil a um usuário.

Modificação de usuários:

ALTER USER NOME_USUARIO

IDENTIFIED BY SENHA _ACESSO
[DEFAULT TABLESPACE ESPACO_TABELA]
[TEMPORARY TABLESPACE ESPACO_TABELA]
[COTA {INTEIRO {K | M } | UNLIMITED } ON ESPACO_TABELA
[PROFILE PERFIL];

Eliminação de usuarios:

DROP USER USUARIO [CASCADE];

CASCADE= Suprime todos os objetos do usuário antes de elimina-lo.

Informe de Agustin Jareño

Mail: agustin@[Link]
URL: [Link]

Gerenciamento em Oracle com SQL

Privilégios

é a capacidade de um usuário dentro do banco de dados a realizar determinadas operações ou acessar a

determinados objetos de outros usuários.

Privilégios sobre os objetos

Permite-nos acessar e realizar mudanças nos dados de outros usuários. Exemplo: O privilégio de consultar a
tabela de outro usuário é um privilégio sobre objetos.

GRANT {PRIV_OBJETO [, PRIV_OBJETO]… | ALL [PRIVILEGES]}

[(COL [,COL]…)]
ON [USUARIO] OBJETO
TO {USUARIO | ROL | PUBLIC} [,{USUARIO | ROL | PUBLIC}…]
[WITH GRANT OPTION];
ON= Especifica o objeto sobre o qual se dão os privilégios.
TO= Identifica aos usuários ou rols aos quais se concedem os privilégios.
ALL= Concede todos os privilégios sobre o objeto especificado.
WITCH GRANT OPTION= Permite que o receptor do privilégio ou rol se atribua a outros usuários ou rols.
PUBLIC= Atribui os privilégios a todos os usuários atuais e futuros: O propósito principal do grupo PUBLIC é
garantir o acesso a determinados objetos a todos os usuários do banco de dados.

Privilégios de sistema

Dão direito a executar um tipo de comando SQL ou a realçar alguma ação sobre objetos de um tipo
especificado. Por exemplo, o privilégio para criar TABLESPACES é um privilégio de sistema. Formato:

GRANT {PRIVILEGIO | ROL} [, {PRIVILEGIO | ROL}, …]

TO {USUARIO | ROL | PUBLIC} [,{USUARIO | ROL | PUBLIC}]
[WITH ADMIN OPTION];

WITH ADMIN OPTION= Permite que o receptor do privilégio ou rol possa conceder esses mesmos privilégios a
outros usuários ou rols.

Retirada de privilégios de objetos aos usuários

REVOKE {PRIV_OBJETO [,PRIV_OBJETO]… | ALL [PRIVILEGES]}

ON [USUARIO.]OBJETO
FROM {USUARIO | ROL | PUBLIC} [, {USUARIO | ROL | PUBLIC}]…;

Retirada de privilégios de sistema ou rols aos usuários

REVOKE {PRIV_SISTEMA | ROL} [,{PRIV_SISTEMA | ROL}]…

FROM {USUARIO | ROL | PUBLIC} [,{USUARIO | ROL | PUBLIC}]…;

Roles

Conjunto de privilégios agrupados. Formato:

CREATE ROLE NOMEROL [IDENTIFIED BY SENHA];

Nota: Um rol pode decidir o acesso de um usuário a um objeto, mas não pode permitir a criação de objetos.

Supressão de privilégios nos roles

REVOKE NOMEPRIVILEGIO ON NOMETABELA FROM NOMEROL;

REVOKE NOMEPRIVILEGIO FROM NOMEROL;

Supressão de um rol

DROP ROLE NOMEROL;

Estabelecer um rol padrão

ALTER USER NOMEUSUARIO

DEFAULT {[ROLE NOME_ROL] | [NONE]};

NONE= Faz com que o usuário não tenha rol padrão.

Perfis:

Conjunto de limites aos recursos do banco de dados:

CREATE PROFILE NOMEPERFIL LIMIT

{NOME DOS LIMITES}
{INTEIRO [K | M] | UNLIMITED | DEFAULT };

UNLIMITED= Não há limites sobre um recurso em particular.

DEFAULT= Pega o limite do perfil default.

Eliminação de um perfil:

DROP FILE NOMEPERFIL [CASCADE];

Gerenciamento de tablespaces

Um tablespace é uma unidade lógica de armazenamento de dados representada fisicamente por um ou mais
arquivos de dados. Recomenda-se não misturar dados de diferentes aplicações em um mesmo tablespace.

Para criar um tablespace

CREATE TABLESPACE NOMETABLESPACE

DATAFILE 'NOMEARQUIVO' [SIZE INTEIRO [K | M] [REUSE]
[DEFAULT STORAGE
(INITIAL TAMANHO
MINEXTENTS TAMANHO
MAXEXTENTS TAMANHO
PCTINCREASE VALOR
)]
[ONLINE | OFFLINE];

REUSE= Reutiliza o arquivo se já existe ou o cria se não existe.

DEFAULT STORAGE= Define o armazenamento por omissão para todos os objetos que se criam neste espaço
da tabela. Observe a quantidade de espaço se não se especifica na sentença CREATE TABLE.

Modificação de tablespaces

ALTER TABLESPACE NOMETABLESPACE

{[ADD DATAFILE 'NOMEARQUIVO' [SIZE INTEIRO [K | M] [REUSE]
[AUTOEXTEND ON… | OFF]
]
[REANME DATAFILE 'ARQUIVO' [, 'ARQUIVO']…
TO 'ARQUIVO' [, 'ARQUIVO']]
[DEFAULT STORAGE CLAUSULAS_ARMAZENAMENTO]
[ONLINE | OFFLINE]
};

ADD_DATAFILE= Adiciona ao tablespace um ou vários arquivos.

AUTOEXTEND= Ativa ou desativa o crescimento automático dos arquivos de dados do tablespace. Quando um
tablespace se enche podemos usar esta opção para que o tamanho do arquivo ou arquivos de dados
associados cresça automaticamente.
Autoextend off: desativa o crescimento automático.
RENAME_DATAFILE= Muda o nome de um arquivoe existente do tablespace. Esta mudança tem que ser feita
através do sistema operacional e, depois, executar a ordem SQL.

Eliminação de tablespaces

DROP TABLESPACE NOMETABLESPACE

[INCLUDING CONTENTS];

INCLUDING CONTENTS= Permite eliminar um tablespace que tenha dados. Sem esta opção só se pode
suprimir um tablespace vazio.
Recomenda-se colocar o talespace offline antes de elimina-lo para certificarmos de que não haja sentenças
SQL que estejam acessando dados do tablespace, em cujo caso não seria possível elimina-lo.
Quando se elimina um tablespace os arquivos associados não se apagam do sistema operacional, portanto
teremos que elimina-los de forma manual.

Informe de Agustin Jareño

Mail: agustin@[Link]
URL: [Link]

Otimizar consultas SQL

A linguagem SQL é não procedimental, ou seja, nas sentenças se indica o que queremos conseguir e não
como tem que fazer o intérprete para consegui-lo. Isto é pura teoria, pois na prática todos os gerenciadores
de SQL têm que especificar seus próprios truques para otimizar o rendimento.

Portanto, muitas vezes não basta com especificar uma sentença SQL correta, e sim que além disso, há que
indicar como tem que fazer se quisermos que o tempo de resposta seja o mínimo. Nesta seção, veremos como
melhorar o tempo de resposta de nosso intérprete ante umas determinadas situações:

Design de tabelas

 Normalize as tabelas, pelo menos até a terceira forma normal, para garantir que não haja duplicidade
de dados e aproveitar o máximo de armazenamento nas tabelas. Se tiver que desnormalizar alguma
tabela pense na ocupação e no rendimento antes de proceder.
 Os primeiros campos de cada tabela devem ser aqueles campos requeridos e dentro dos requeridos
primeiro se definem os de longitude fixa e depois os de longitude variável.
 Ajuste ao máximo o tamanho dos campos para não desperdiçar espaço.
 É normal deixar um campo de texto para observações nas tabelas. Se este campo for utilizado com
pouca freqüência ou se for definido com grande tamanho, por via das dúvidas, é melhor criar uma
nova tabela que contenha a chave primária da primeira e o campo para observações.

Gerenciamento e escolha dos índices

Os índices são campos escolhidos arbitrariamente pelo construtor do banco de dados que permitem a busca a
partir de tal campo a uma velocidade notavelmente superior. Entretanto, esta vantagem se vê contra-
arrestada pelo fato de ocupar muito mais memória (o dobro mais ou menos) e de requerer para sua inserção e
atualização um tempo de processo superior.

Evidentemente, não podemos indexar todos os campos de uma tabela extensa já que dobramos o tamanho do
banco de dados. Igualmente, tampouco serve muito indexar todos os campos em uma tabela pequena já que
as seleções podem se efetuar rapidamente de qualquer forma.

Um caso em que os índices podem ser muito úteis é quando realizamos petições simultâneas sobre várias
tabelas. Neste caso, o processo de seleção pode se acelerar sensivelmente se indexamos os campos que
servem de nexo entre as duas tabelas.

Os índices podem ser contraproducentes se os introduzimos sobre campos triviais a partir dos quais não se
realiza nenhum tipo de petição já que, além do problema de memória já mencionado, estamos lentificando
outras tarefas do banco de dados como são a edição, inserção e eliminação. É por isso que vale a pena pensar
duas vezes antes de indexar um campo que não serve de critério para buscas ou que é usado com muita
freqüência por razões de manutenção.

Campos a Selecionar

 Na medida do possível há que evitar que as sentenças SQL estejam embebidas dentro do código da
aplicação. É muito mais eficaz usar vistas ou procedimentos armazenados por que o gerenciador os
salva compilados. Se se trata de uma sentença embebida o gerenciador deve compila-la antes de
executa-la.
 Selecionar exclusivamente aqueles que se necessitem
 Não utilizar nunca SELECT * porque o gerenciador deve ler primeiro a estrutura da tabela antes de
executar a sentença
 Se utilizar várias tabelas na consulta, especifique sempre a que tabela pertence cada campo, isso
economizará tempo ao gerenciador de localizar a que tabela pertence o campo. Ao invés de SELECT
Nome, Fatura FROM Clientes, Faturamento WHERE IdCliente = IdClienteFaturado, use: SELECT
[Link], [Link] WHERE [Link] = [Link].

Campos de Filtro

 Procuraremos escolher na cláusula WHERE aqueles campos que fazem parte da chave do arquivo pelo
qual interrogamos. Ademais se especificarão na mesma ordem na qual estiverem definidas na chave.
 Interrogar sempre por campos que sejam chave.
 Se desejarmos interrogar por campos pertencentes a índices compostos é melhor utilizar todos os
campos de todos os índices. Suponhamos que temos um índice formado pelo campo NOME e o campo
 SOBRENOME e outro índice formado pelo campo IDADE. A sentença WHERE NOME='Jose' AND
SOBRENOME Like '%' AND IDADE = 20 seria melhor que WHERE NOME = 'Jose' AND IDADE = 20 porque
o gerenciador, neste segundo caso, não pode usar o primeiro índice e ambas sentenças são
equivalentes porque a condição SOBRENOME Like '%' devolveria todos os registros.

Ordem das Tabelas

Quando se utilizam várias tabelas dentro da consulta há que ter cuidado com a ordem empregada na cláusula
FROM. Se desejarmos saber quantos alunos se matricularam no ano 1996 e escrevermos: FROM Alunos,
Matriculas WHERE [Link] = [Link] AND [Link] = 1996 o gerenciador percorrerá
todos os alunos para buscar suas matrículas e devolver as correspondentes. Se escrevermos FROM Matriculas,
Alunos WHERE [Link] = 1996 AND [Link] = [Link], o gerenciador filtra as
matrículas e depois seleciona os alunos, desta forma tem que percorrer menos registros.

Informe de Claudio
Mail: claudio@[Link]
URL: [Link]

Todos los direitos de reprodução e difusão reservados