Índice
Introdução...1
Visão geral da Oracle...1
O que é o BD Oracle 10g XE...1
1ª Etapa...2
Instalação do Oracle 10g XE...2
Apresentação das ferramentas do Banco...3
Ferramentas SQL*Plus...3
Criação e exclusão de usuários...4
Edição de senhas dos usuários...7
Privilégios...8
2ª Etapa...10
Tipos de dados...10
Criação e manipulação de tabelas...12
Relacionamento de tabelas...17
Tipos de Constraints. ...17
Manipulação de dados com SQL...26
Commit e Rollback...32
“Rotulando” campos das tabelas...33
Concatenando colunas...36
3ª Etapa...37
Criação e execução de scripts SQL...37
Operadores lógicos em SQL...41
Funções de ordenação...54
SELECT com mais de uma tabela...59
Funções do Oracle 10g XE...67
4ª Etapa...83 Conceito de PL/SQL...83 Function...83 Procedure...85 Sequence...88 Trigger...89 View...92 Index...94 5ª Etapa...97
Oracle Application Express...97
Serviços do Oracle 10g...108
Backup lógico ...111
Backup físico...115
Índice de Figuras Figura 1.1: Assistente de Instalação...2
Figura 1.2: Menu Iniciar...3
Figura 1.3: SQL*Plus...4
Figura 1.4: Show User...5
Figura 1.5: Create User Fatec...6
Figura 1.6: Drop User Fatec...7
Figura 1.7: Alter User Fatec...8
Figura 2.4: Alter Table Teste...16
Figura 2.5: Alter Table Teste Drop Column...17
Figura 2.6: Alter Table Teste Add Constraint...19
Figura 2.7: Create Table Aluno...20
Figura 2.8: Create Table Nota_Aluno...22
Figura 2.9: Alter Table Aluno Add Constraint...24
Figura 2.10: Alter Table Aluno Drop Constraint...25
Figura 2.11: Insert Into Cidades...27
Figura 2.12: Select Cidades 1...27
Figura 2.13: Column Format...28
Figura 2.14: Select Cidades 2...29
Figura 2.15: Update Cidades...30
Figura 2.16: Select Cidades 3...30
Figura 2.17: Delete Cidades...31
Figura 2.18: Select Cidades 4...31
Figura 2.19: Select Cidades após Delete...32
Figura 2.20: Rollback...33
Figura 2.21: Select Cidades 5...34
Figura 2.22: Select com Rótulo...35
Figura 2.23: Select com Rótulo 2...35
Figura 2.24: Concatenando Campos...36
Figura 2.25: Concatenando Campos 2...37
Figura 3.1: Iniciando Script...38
Figura 3.2: Executando Script...39
Figura 3.3: Select Cidades 6...40
Figura 3.4: Diretório do Script...40
Figura 3.5: Select Cidades Codigo 7...44
Figura 3.6: Operador Lógico “Diferente” ...45
Figura 3.7: Oprador Lógico “Maior”...45
Figura 3.8: Operador Lógico “Menor”...46
Figura 3.9: Operador Lógico “Maior ou Igual”...47
Figura 3.10: Operador Lógico “Menor ou Igual”...47
Figura 3.11: Operador Lógico “And”...48
Figura 3.12: Operador Lógico “Or”...48
Figura 3.13: Operador Lógico “Between”...49
Figura 3.14: Operador Lógico “In”...50
Figura 3.15: Operador Lógico “%Like”...51
Figura 3.16: Operador Lógico “Like%”...51
Figura 3.17: Operador Lógico “%Like%”...52
Figura 3.18: Operador Lógico “Not In”...53
Figura 3.19: Operador Lógico “Not Like”...53
Figura 3.20: Função “Order By”...54
Figura 3.21: Select com Order By...55
Figura 3.22: Select com Order By Desc...56
Figura 3.23: Select com Order By Desc 2...57
Figura 3.24: Select com Order By Desc 3...58
Figura 3.25: Select com Order By Desc 4...58
Figura 3.26: Select com Order By Desc 5...59
Figura 3.27: Select Vendas...63
Figura 3.31: Select com Join 2...66
Figura 3.32: Select com Outer Join...67
Figura 3.33: Função SQRT...68
Figura 3.34: Função ROUND...69
Figura 3.35: Função TRUNC...69
Figura 3.36: Função TRUNC 2...70
Figura 3.37: Função POWER...71
Figura 3.38: Função UPPER...71
Figura 3.39: Função LOWER...72
Figura 3.40: Função REPLACE...73
Figura 3.41: Função NVL...74
Figura 3.42: Função TO_CHAR...75
Figura 3.43: Função SYSDATE...75
Figura 3.44: Função LAST_DAY...76
Figura 3.45: Função ADD_MONTHS...77
Figura 3.46: Função DECODE...77
Figura 3.47: Função DECODE 2...78
Figura 3.48: Função AVG...79
Figura 3.49: Função MAX...79
Figura 3.50: Função MIN...80
Figura 3.51: Função SUM...80
Figura 3.52: Função COUNT...81
Figura 3.53: Função COD_VENDEDOR...82
Figura 3.54: Função COUNT DISTINCT...82
Figura 4.1: Create Function...84
Figura 4.2: Executando Function...85
Figura 4.3: Create Procedure...86
Figura 4.4: Executando Procedure...87
Figura 4.5: Verificando resultado da Procedure...87
Figura 4.6: Criando Sequence...89
Figura 4.7: Criando Trigger...90
Figura 4.8: Inserindo Registro...91
Figura 4.9: Verificando Vendas...92
Figura 4.10: Criando View...93
Figura 4.11: Visualizando View...94
Figura 4.12: Campo Nome da Tabela Vendedores...95
Figura 4.13: Criando Index...96
Figura 4.14: Funcionalidade do Index...96
Figura 5.1: Menu Ir para a Home Page do Banco de Dados...97
Figura 5.2: Opções do Application Express...98
Figura 5.3: Informações de armazenamento do Application Express...98
Figura 5.4: Usuários do Banco de Dados...99
Figura 5.5: Tela Criar Usuário do Banco de Dados...99
Figura 5.6: Tela Alterar Minha Senha...100
Figura 5.7: Tela Browser de Objetos...101
Figura 5.8: Tela Criar Tabela...102
Figura 5.9: Tela Chave Primária...102
Figura 5.10: Tela Restrições...103
Figura 5.11: Tela Confirmar...103
Figura 5.15: Visualização dos Dados na Tabela Oracle...106
Figura 5.16: Tela Gerar DLL...107
Figura 5.17: Scripts SQL...107
Figura 5.18: Serviços...108
Figura 5.19: Interrompendo Banco de Dados...109
Figura 5.20: Erro gerado...110
Figura 5.21: Erro gerado 2...110
Figura 5.22: Iniciando Banco de Dados...111
Figura 5.23: Exportação Lógica...112
Figura 5.24: Diretório dos Arquivos Lógicos Exportados...113
Figura 5.25: Exclusão de usuário...114
Figura 5.26: Importação Lógica...115
Figura 5.27: Select Após Importação...115
Figura 5.28: Exportação Física...116
Figura 5.29: Diretório dos Arquivos Físicos Exportados...117
Figura 5.30: Diretório dos Arquivos Físicos Exportados 2...118
Figura 5.31: Importação Física...118
Índice de Tabelas Tabela 1.1: Permissões...9
Tabela 2.1: Tipos de Dados...12
Tabela 2.2: Tabela Cidades...26
Tabela 2.3: Cidade 2...29
Tabela 3.1: Cidade 3...38
Tabela 3.2: Operadores Lógicos...42
Tabela 3.3: Cidade 4...42
Tabela 3.4: Cidade 5...42
Tabela 3.5: Cidade 6...42
Tabela 3.6: Cidade 7...43
Tabela 3.7: Cidade 8...43
Tabela 3.8: Tabela Vendas...59
Tabela 3.9: Tabela Vendedores...60
Tabela 3.10: Venda 1...60 Tabela 3.11: Venda 2...60 Tabela 3.12: Venda 3...60 Tabela 3.13: Venda 4...61 Tabela 3.14: Venda 5...61 Tabela 3.15: Vendedor 1...61 Tabela 3.16: Vendedor 2...61 Tabela 3.17: Vendedor 3...62 Tabela 3.18: Vendedor 4...62 Tabela 3.19: Vendedor 5...62 Tabela 3.20: Vendedor 6...73 Tabela 4.1: Venda 6...91
Introdução • Visão geral da Oracle.
Há quase trinta anos, Larry Ellison vislumbrou uma oportunidade que outras companhias não haviam percebido, quando encontrou uma descrição de um protótipo funcional de um banco de dados relacional e descobriu que nenhuma empresa tinha se empenhado em comercializar essa tecnologia. Ellison e os co-fundadores da Oracle, Bob Miner e Ed Oates, perceberam que havia um tremendo potencial de negócios no modelo de banco de dados relacional, mas não se deram conta de que mudariam a face da computação empresarial para sempre. Hoje a Oracle (Nasdaq: ORCL) continua à frente de seu tempo. A tecnologia Oracle pode ser encontrada em quase todos os setores do mundo inteiro e nos escritórios de 98 das empresas citadas na lista “Fortune 100”. A Oracle é a primeira empresa de software a desenvolver e empregar software empresarial totalmente habilitado para Internet em toda a sua linha de produtos: banco de dados, aplicativos empresariais e ferramentas para desenvolvimento de aplicativos e suporte a decisões. A Oracle é o principal fornecedor de software para gerenciamento de informações e a segunda maior empresa de software independente do mundo. A Oracle é uma empresa inovadora. Foi uma das primeiras a tornar seus aplicativos empresariais disponíveis através da Internet -- atualmente, esse conceito é predominante. Agora, a Oracle está comprometida em garantir que todo o seu software seja projetado para trabalhar em conjunto – a abordagem de suíte – e outras empresas, analistas e a imprensa vem reconhecendo que ela está certa. O que estamos preparando para o futuro? Continuaremos a inovar e liderar o mercado – nos preocupando sempre em resolver os problemas dos nossos clientes.
www. oracle .com/global/br/corporate/story.html
• O que é o BD Oracle 10g XE.
O BD Oracle 10g Express Edition é a versão gratuita do BD da Oracle. Esse é um BD relacional, ou seja, é baseado no conceito de tabelas para armazenamento de dados. Foi desenvolvido visando a comunidade acadêmica, já que os estudantes da área de tecnologia não tinham acesso às versões licenciadas desse banco, pois as mesmas são muito caras para as pessoas em comum e até mesmo para pequenas empresas. Frente a esse fato, os profissionais que sabiam trabalhar com esse banco estavam cada vez mais escassos. Por esse motivo a Oracle lançou sua versão gratuita do seu BD, para que mais pessoas tivessem acesso à ele e conhecessem essa magnífica ferramenta.
O BD possui suporte ao PL/SQL, versão otimizada da linguagem SQL, que é utilizada para a comunicação com o BD, desenvolvida pela Oracle e que também será apresentada no decorrer do curso.
O Oracle 10g Express Edition conta ainda com o mesmo suporte oferecido pela sua versão comercial, para quem desenvolve softwares, à integração com as linguagens
1ª Etapa
• Instalação do Oracle 10g XE.Nesse curso utilizaremos a versão do Oracle XE para Windows. A Oracle também possui a versão para Linux desse BD para aqueles que preferem esse SO.
Para quem desejar baixar o Oracle XE, basta acessar o endereço abaixo: http://www.oracle.com/technology/software/products/database/xe/index.html Caso você ainda não tenha se cadastrado no site da Oracle, o cadastro será requerido e você pode fazê-lo pois é totalmente grátis.
Com o software devidamente baixado em seu microcomputador, basta dar um duplo clique sobre ele para iniciar a instalação.
Como é padrão no Windows, depois que a janela do instalador estiver aberta, basta clicar em “Próximo” até chegar na seguinte tela:
Figura 1.1: Assistente de Instalação
Nessa etapa deverá ser informada a senha do usuário System, que é o usuário padrão do Oracle XE.
Dica: escolha seu primeiro nome (opcional).
Feito isso, clique em “próximo” e na tela que seguinte clique em “instalar”.
Após instalado será exibida a janela de confirmação da instalação. Desmarque a opção “Acionar a homepage do Banco de Dados” e clique em “Finalizar”.
• Apresentação das ferramentas do Banco.
Após a conclusão da instalação, acesse o menu Iniciar, exiba todos os programas e procure a pasta Oracle Database 10g Express Edition.
Figura 1.2: Menu Iniciar Pode-se visualizar 8 opções:
1. Diretório “Obter Ajuda”: nesse diretório temos mais 4 opções de ajuda online, que não serão aqui discutidas.
2. Executar linha de comandos SQL: essa opção abre o SQL*Plus, que será muito utilizada nesse curso.
3. Fazer Backup do Banco de Dados: opção para backup do banco, que será discutida mais adiante aqui no curso.
4. Iniciar Banco de Dados: caso o banco não esteja iniciado essa opção dá um “start” no banco.
5. Interromper Banco de Dados: essa opção encerra todos os serviços do banco de dados que estiverem sendo executados no computador.
6. Ir para a Home Page de Banco de Dados: essa opção abre uma tela para gerenciamento do BD no navegador padrão do computador.
7. Obter ajuda: outra opção de ajuda online, que aqui não será discutida. 8. Restaurar Banco de Dados: caso haja um arquivo de backup essa opção é utilizada para restaurar esse arquivo.
• Ferramentas SQL*Plus.
Para iniciar, iniciaremos a opção Executar linha de comandos SQL. Deverá aparecer a seguinte tela:
Figura 1.3: SQL*Plus
Essa é a tela inicial do SQL*Plus. Será esse ambiente que usaremos no decorrer de grande parte do tutorial.
Nessa tela digite conn; e pressione a tecla “Enter”.
O comando conn; serve para o usuário conectar-se ao BD. • Criação e exclusão de usuários.
Como estamos acessando o BD pela primeira vez após a instalação, o único usuário existente é o “system”. Portanto digite “system” assim que o SQL*Plus solicitar e pressione “Enter”.
Após isso o SQL*Plus solicitará a senha. Informe a senha que você cadastrou no momento da instalação do BD.
Podemos ainda visualizar qual usuário está conectado ao banco. Para isso utilizamos o seguinte comando:
show user;
Figura 1.4: Show User
Não é aconselhável usar o usuário padrão do Oracle, pois o mesmo pode realizar toda e qualquer ação dentro do banco de dados, e isso é muito perigoso, pois somente o administrador do BD deve possuir tais privilégios.
Portanto aprenderemos a seguir como criar usuários no BD. Para criarmos usuários no Oracle utilizamos o seguinte comando: create user [login] identified by [senha];
Onde [login] é o nome do usuário e [senha] será o código de acesso dele ao banco. Como em geral, recomenda-se não usar caracteres especiais para login e senha.
Vamos aos testes!
A partir desse ponto exercitaremos a criação de usuários.
Suponha que você é o administrados do banco de dados de uma organização e, portanto, você é o responsável pela criação de usuários. Cadastre alguns usuários (5 ao menos) no Oracle, mas atenção aos logins e senhas, pois utilizaremos esses usuários para exercícios posteriores (é recomendável que tais informações sejam anotadas em algum lugar de sua preferência).
Sempre que um usuário é criado corretamente deverá ser exibida a mensagem de confirmação, como no exemplo a seguir:
Figura 1.5: Create User Fatec
Suponha que você está cadastrando usuários que foram recentemente contratados pela empresa onde você trabalha. Como são funcionários novos na empresa, consequentemente eles não possuíam usuário e senha de acesso ao sistema, por isso você ficou encarregado de realizar essa tarefa.
Mas vamos supor uma outra situação: a empresa onde você trabalha demitiu alguns funcionários. Lógicamente você precisará excluir esse usuários do banco de dados. Desse modo, veremos a seguir o comando que nos possibilita a exclusão de usuários do BD.
Para excluirmos usuários no Oracle utilizamos o seguinte comando: drop user [login] cascade;
Onde o [login] é o nome do usuário que deseja-se deletar. A palavra CASCADE no fim do comando significa "em cascata" e é opcional. Quando criamos objetos dentro do banco de dados (tabelas, funções, etc.), estes objetos ficam na área do usuário que os criou e se este for deletado seus objetos serão deletados também. Para que essa exclusão "em cascata" ocorra, o comando CASCADE deve estar presente ao final do comando DROP USER. Caso o comando CASCADE não esteja no fim da linha de comando será exibida na tela uma mensagem de erro da Oracle. Esse comando só será dispensável caso o usuário em questão não tenha criado nenhum objeto no BD.
Após a exclusão de um usuário deverá aparecer na tela uma mensagem tal qual a ilustração a seguir:
Figura 1.6: Drop User Fatec • Edição de senhas dos usuários.
Outra função muita usada por um administrador de banco de dados é a alteração de senhas dos usuários. Normalmente nas empresas em geral o usuário é cadastrado com uma senha padrão, mas que pode posteriormente ser alterada, segundo a solicitação do mesmo.
Veremos agora como fazer para alterar a senha de um usuário.
Para alterarmos as senhas dos usuários no Oracle utilizamos o seguinte comando:
alter user [login] identified by [nova senha];
Onde [login] é o nome do usuário que deseja-se alterar e [nova senha] é o novo código de acesso do usuário ao banco. Vale lembrar que caracteres especiais são dispensáveis! Assim que um usuário é alterado, a tela deverá estar parecida com a que segue:
Figura 1.7: Alter User Fatec
“É importante ressaltar que toda alteração de senha deve ser anotada em um local aonde o administrador do banco de dados poderá consultar posteriormente, pois
casos em que o usuário esquece a senha não são raros.”
• Privilégios.
Como já foi comentado anteriormente, o usuário padrão (System) do Oracle pode executar qualquer ação dentro do banco. Por isso demos início aos tópicos de criação, exclusão e alteração de usuários dentro do banco de dados.
Mas isso de nada adiantaria se esses usuários tivessem as mesmas permissões do usuário System. Por isso neste tópico, aprenderemos como adicionar ou remover permissões de um usuário.
Mas antes disso é interessante conhecer os tipos de permissões e suas características.
Privilégio Descrição
CONNECT Permite que o usuário conecte-se ao banco de dados e execute ações básicas nele. Conceda este privilégio ao usuário ou aplicação que necessite acessar o banco de dados. CONNECT inclui o privilégio CREATE SESSION, que permite somente acesso ao banco.
DBA Permite que um usuário administre todos os objetos do banco de dados, inclusive os demais usuários, mas não inclui os privilégios necessários para iniciar ou parar o banco de dados, start up e shut down, respectivamente. DBA inclui o privilégio de sistema com WITH ADMIN OPTION, que significa: com opção de administração. RESOURCE Permite que o usuário crie objetos (tabelas, funções, etc). Conceda
este privilégio apenas aos usuários que desenvolvem no banco de dados. RESOURCE inclui os seguintes privilégios do sistema: CREATE CLUSTER, CREATE INDEXTYPE, CREATE
OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, e CREATE TYPE .
Tabela 1.1: Permissões
Agora que já conhecemos as permissões, vamos fazer alguns testes. Conecte-se ao banco com o usuário SYSTEM, caso não esteja.
Crie um usuário com o seu nome, a senha ficará por sua conta.
Quando desejamos adicionar/alterar a permissão de usuários, utilizamos o comando GRANT (conceder). A sintaxe desse comando é muito simples, como pode-se notar a seguir:
grant [privilégio] to [usuário];
É possível conceder mais de um privilégio a um usuário numa mesma linha de comando. Para isso é necessário separar os privilégios com uma vírgula.
Ex:
grant [privilégio1, privilégio2] to [usuário];
Há também a possibilidade de conceder um ou mais privilégios a um ou mais usuários, bastando somente separar o nome dos usuários com uma vírgula, tal qual demonstramos no exemplo anterior.
Ex:
grant [privilégio1, privilégio2] to [usuário1, usuário2];
Um usuário que não possui o privilégio de DBA pode conceder permissões a outros usuários, porém ele só pode conceder as permissões que ele possui. E ele só poderá fazer isso se quando esse usuário recebeu a permissão foi especificado no comando grant a opção WITH ADMIN OPTION.
Para melhor entendimento, segue a sintaxe do comando: grant [resource] to [usuário1] with admin option;
No exemplo acima o usuário poderá conceder o privilégio resource a qualquer outro usuário, pois está especificado na última parte do comando a opção with admin option.
Agora que sabemos como conceder privilégios à usuários, vamos conhecer o comando para tirar privilégios.
O comando que nos possibilita esse ação é o REVOKE (revogar). A sintaxe desse comando não possui segredos, como pode ser observado a seguir:
revoke [privilégio] from [usuário];
Assim como no comando grant podemos revogar mais de um privilégio de um ou mais usuários na mesma linha de comando.
Ex:
revoke [privilégio1, privilégio2] from [usuário]; ou
revoke [privilégio1] from [usuário1, usuário2];
Ou ainda podemos revogar mais de uma permissão de mais de um usuário no mesmo comando, fazendo uma junção dos dois comandos demonstrados anteriormente:
revoke [privilégio1, privilégio2] from [usuário1, usuário2];
Um usuário que não tem o privilégio DBA pode tirar privilégio de outro usuário, mas somente se o primeiro estiver habilitado para conceder esse privilégio, o que o obriga a possuir tal privilégio.
Para acabar com qualquer confusão, vamos aos testes!
Faremos vários testes com diferentes usuários e permissões para ver na prática o que cada privilégio concede ao usuário. Logo após faremos vários testes para tirar as permissões dos usuários e ver quem pode o quê.
2ª Etapa
• Tipos de dados.Para todo administrador do banco de dados ou desenvolvedor de sistemas em geral é extremamente importante saber quais os tipos de dados que serão armazenados. Isso porque quanto melhor específicas forem essas informações melhor será o rendimento do banco de dados.
Vamos analisar juntos: se eu necessito de um campo para armazenar o CPF de um cliente, não faz sentido algum eu definir esse campo para que o mesmo receba letras.
Esse foi um exemplo banal, mas que é perfeitamente adequado à nossa realidade. Quando criamos uma tabela no banco e definimos as colunas e as propriedades das colunas consumimos recursos tanto do banco de dados quanto do sistema, por isso espaços desperdiçados, campos inúteis, etc, são extremamente prejudiciais ao desempenho do sistema.
Frente a esses fatos é importante saber os tipos de dados que podemos armazenar na tabelas.
A seguir observamos uma tabela com os tipos de dados suportados pelo Oracle XE:
Tipo de dados Descrição
NUMBER Armazena números reais com sinal
(negativos e positivos), tem precisão de 1 a 38 no que se refere à casas decimais. É aconselhável utilizá-lo quando os valores numéricos possam ser utilizados para cálculos.
Ex: NUMBER(12,2) onde o valor 12 refere-se a quantidade máxima de números antes das vírgula e o valor 2 refere-se a quantidade de casas decimais após a vírgula.
CHAR Para campos de texto, cujo comprimento é
fixo, determinado por “n”, cujo tamanho máximo é de 2000 caracteres.
Ex: CHAR(5)
VARCHAR2 Para campos de texto de comprimento
variável com tamanho máximo dimensionado por N, cujo maior valor possível é 4000 caracteres. Recomenda-se o uso deste campo sobre o char.
Ex: VARCHAR2(50)
NVARCHAR2 Assim como os campos do tipo varchar2,
os campos do tipo nvarchar2 também armazenam textos de comprimentos variáveis, porém há casos onde na presença de caracteres especiais o varchar2 poderá aparecer desconfigurado. Em casos com esse se o nvarchar2 for utilizado esse erro não acontecerá.
Ex: a palavra pendência pode ser exibida como “pend?encia” se for do tipo varchar2, o que não ocorre se ela for do tipo nvarchar2.
DATE Utilizado em campos do tipo data e hora.
TIMESTAMP Utilizado em campos do tipo data e hora,
incluindo valores de milissegundos .
BLOB (binary large object) Para compos que armazenarão dados binários (sons, imagens, vídeos, etc.) de tamanho variável até um limite máximo de 4GB
CLOB (character large object) Para campos de textos de tamanho variável com um tamanho máximo de 4GB, muito útil para textos longos.
BINARY_FLOAT Número de ponto flutuante, com cumprimento ao IEEE-754, de precisão simples. Necessita de menos memória e utiliza aritmética de máquina nativa - o que proporciona alto desempenho para sistemas de computação intensiva. Pode ser comparado ao infinitivo ou ao NaN (Not a Number). Apresenta o armazenamento da precisão binária, que pode gerar erros ao realizar arredondamento.
BINARY_DOUBLE Semelhante ao BINARY_FLOAT, porém
com precisão dupla. Ainda assim é armazenado com precisão binária – não deve ser utilizado em sistemas com necessidades financeiras de arredondamento. Alto desempenho pois utiliza a aritmética de máquina nativa.
Tabela 2.1: Tipos de Dados
Agora que já conhecemos os tipo de dados suportados pelo Oracle 10g Express Edition, vamos partir para o próximo passo.
• Criação e manipulação de tabelas.
Quando falamos de banco de dados relacional automaticamente estamos nos remetendo ao conceito de tabelas para armazenagem da massa de dados.
Não detalharemos aqui o conceito de tabelas, tuplas, etc, pois não é esse o objetivo do curso. Nosso foco a partir daqui será a criação, manipulação e manutenção de tabelas.
A sintaxe para criação de tabelas é a seguinte: create table [nome da tabela] (
[nome do campo 1] [tipo] [valor padrão] [nulo?], [nome do campo 2] [tipo] [valor padrão] [nulo?], .
. .
[nome do campo n] [tipo] [valor padrão] [nulo?] );
[Nome da tabela] é onde deve ser informado o nome de identificação da tabela, [nome do campo 1], [nome do campo 2], etc, são os nomes que serão atribuídos às colunas; [tipo] é o tipo de dados que cada coluna armazenará (os tipos possíveis foram mostrados na última tabela deste documento); [nulo?] é onde devemos informar se a tabela poderá conter valores nulos (vazios) ou não.
Observemos a seguir o bloco de comandos da criação da nossa primeira tabela:
create table teste (
codigo varchar2(10) not null, nome varchar2(50) not null, sexo char(1) default 'M' not null, dt_nasc date null, idade number null );
Após a digitação desse bloco de código devemos pressionar a tecla “Enter” para confirmar a criação da nossa tabela 'TESTE'.
Se não houver nenhum erro no código, sua tela deverá estar semelhante à tela a seguir:
Figura 2.1: Create Table Teste
Após criarmos uma tabela, podemos visualizar sua estrutura utilizando o comando DESCRIBE, ou DESC, tanto faz.
A sintaxe desse comando é a seguinte: desc [nome da tabela];
Vamos fazer um teste: digite no SQL*Plus o comando: desc teste;
Figura 2.2: Desc Teste 1
Por se tratar de um comando extremamente simples, dispensaremos explicações sobre ele.
Após a criação de uma tabela, é comum alterarmos as propriedades de seus campos, embora o ideal é que não seja necessário a alteração da estrutura da tabela. Mas por vezes somos obrigados a isso, seja por alterações na legislação do país, alteração na regra de negócio da empresa, ou por qualquer outro motivo.
Perante essa situação, vamos conhecer os comando para que seja possível adicionar, remover e alterar as propriedades de um campo.
Primeiramente veremos como alterar um campo em uma tabela. Utilizaremos a tabela criada anteriormente para esses exercícios.
Segue a sintaxe do comando para alteração das propriedades do campo: alter table [nome da tabela] modify [nome do campo] [tipo];
Onde [nome da tabela] é o nome de identificação da tabela no banco, [nome do campo] é o campo que será alterado e [tipo] é a alteração que faremos nele. Podemos, além de modificar o tipo do campo, modificar também seu valor padrão e se poderá ser nulo ou não.
Vamos supor que nossa intenção seja alterar o campo código. Atualmente ele está com o tipo varchar2(10) e nós iremos alterá-lo para number. Para realizarmos essa alteração utilizamos o código que segue:
alter table teste modify codigo number;
Após a digitação do código anterior, pressionamos a tecla “Enter” e nos deverá ser exibida uma tela semelhante à essa:
Figura 2.3: Desc Teste 2
No exemplo anterior alteramos apenas o tipo de dado que o campo armazenará, mas é perfeitamente possível alterar o valor padrão desse campo assim como se ele poderá ou não ser nulo.
Faça teste nos demais campos antes de prosseguir.
Dando sequencia aos nossos testes, veremos agora como adicionar um campo em uma tabela.
A sintaxe para adição de campos em uma tabela é a seguinte:
alter table [nome da tabela] add ([nome do campo] [tipo] [nulo?]);
Onde [nome da tabela] é o nome de identificação da tabela, [nome do campo] é o campo que será adicionado, [tipo] é o tipo de dados que o campo armazenará e [nulo?] é a propriedade que indica se o campo poderá ser nulo ou não. Vale lembrar que podemos ainda definir o valor padrão do campo.
Vamos supor que na tabela testes devemos criar um campo chamado rg, para armazenar o número desse documento dos indivíduos que serão cadastrados.
Para realizamos essa alteração utilizamos o comando a seguir: alter table teste add (rg varchar2(12) null);
Ao digitar o comando acima, pressione “Enter” para confirmar a alteração. Agora utilize o comando desc para ver como ficou a estrutura da sua tabela. Ela deve estar parecida com a tabela ilustrada na sequencia:
Figura 2.4: Alter Table Teste
Agora que já sabemos como alterar as propriedades de um campo e como adicionar campos em uma tabela, vamos aprender a apagar campos de uma tabela.
A sintaxe desse comando é a seguinte:
alter table [nome da tabela] drop column [nome do campo];
Onde [nome da tabela] é o nome de identificação da tabela e [nome do campo] é o campo que será excluído.
Vamos apagar a coluna que acabamos de criar: rg. Digite o código: alter table teste drop column rg;
Após a digitação do código anterior, sua tela do SQL*Plus deverá estar parecida com a tela seguinte:
Figura 2.5: Alter Table Teste Drop Column
“MUITO CUIDADO AO EXCLUIR UM CAMPO, POIS TODOS OS REGISTROS QUE ESTIVEREM GRAVADOS NELE SERÃO AUTOMATICAMENTE EXCLUÍDOS SEM
AVISO PRÉVIO!!!”
Agora que já sabemos como alterar, incluir e excluir campos de uma tabela, vamos exercitar os conhecimentos até aqui adquiridos.
Faça testes e veja pense em diferentes situações que possam ser aplicados os conceitos até aqui discutidos.
• Relacionamento de tabelas.
Por mais tabelas que um banco de dados tenha, elas nunca poderão ser independentes umas das outras.
Em banco de dados, vemos que as tabelas relacionam-se entre si, por meio de campos que nós definimos. A esses campos chamamos de chaves primárias e chaves estrangeiras. Não explicaremos aqui o conceito de chave primária e estrangeira pois não é o objetivo do curso. Partiremos do princípio que todos sabem, ao menos na teoria, o que é uma chave primária e o que é uma chave estrangeira.
A seguir começaremos a discutir como tratar esses campos dentro do Oracle 10g Express Edition.
• Tipos de Constraints.
A partir daqui trataremos de um assunto de extrema importância. Constraints são restrições que colocamos para determinados campos das tabelas do BD. Antes de definirmos os campos que receberão essas restrições, precisamos primeiro analisar cuidadosamente a regra de negócio envolvida, pois uma restrição errada pode complicar toda programação futura.
Vamos estudar três tipos de constraints: primary ker, foreign key e check. Começaremos pela primary key.
Uma contraint do tipo primary key (chave primária), deve ser atribuída ao(s) campo(s) que deverá possuir valores únicos dentro da tabela.
Para conhecermos a sintaxe desse comando utilizaremos a tabela que temos trabalhado até aqui, ou seja, a tabela teste.
Observe que na tabela teste temos cinco campos: codigo, nome, sexo, dt_nasc e idade.
O campo código foi colocado de propósito para que viesse a ser nossa chave primária. O campos CPF também poderia ser definido como chave primária, já que não existe CPF’s com números iguais, mas para facilitar utilizaremos o campo código.
A sintaxe desse comando é a seguinte:
alter table [tabela] add constraint [nome da constraint] primary key ([campo]);
Onde [tabela] é o nome da tabela que será alterada, [nome da contraint] é o nome que daremos à nossa restrição e [campo] é o nome do campo que possuirá essa restrição.
Agora que já conhecemos a sintaxe, podemos fazer o teste na tabela teste. Então digite no SQL*Plus o seguinte código:
alter table teste add constraint cp_teste primary key (codigo);
“Aqui demos o nome de cp_teste à constraint. Demos esse nome pelo seguinte motivo: cp são as iniciais de Chave Primária. É sempre bom estabelecer um padrão para criação de objetos dentro do banco de dados. Por isso utilizamos cp para
sabermos que é uma chave primária, e logo em seguida, complementado o nome da nossa contraint, utilizamos o nome da tabela a qual pertence a constraint. E para melhor
visualização separamos esse nome composto com o underline.”
Confirme o comando anteriormente digitado pressionando a tecla “Enter”. Sua tela do SQL*Plus deverá estar semelhante a tela seguinte:
Figura 2.6: Alter Table Teste Add Constraint
Podemos ainda criar uma chave primária composta por mais de um campo. No nosso caso isso não será necessário, mas como isso é muito comum acontecer em um sistema, vamos aprender a sintaxe para criação de uma chave primária composta que é muito parecida com a sintaxe que vimos anteriormente. Para que não fique nenhuma dúvida, mostraremos a seguir como fazer uma chave primária composta:
alter table teste add constraint [nome da constraint] primary key ([campo1, campo2]);
Como pode ser observado, apenas a última parte do comando sofre alteração. Quando criamos uma chave primária composta devemos apenas separar os campos que farão parte dela com uma vírgula.
Até aqui vimos como criar uma chave primária em uma tabela que já existia no BD. Vamos agora ver como criar uma chave primária no momento de criação de uma tabela.
A sintaxe para criação de uma chave primária no momento da criação da tabela segue:
create table [tabela] (
[campo1] [tipo] [default] [nulo?], [campo2] [tipo] [default] [nulo?], .
. .
[campoN] [tipo] [nulo?],
constraint [nome da constraint] PRIMARY KEY ([Campo]) );
Na verdade a única coisa que devemos fazer para criar uma restrição no momento da criação da tabela é fazer a junção dos dois comandos.
Embora não haja segredos, vamos testar. Para isso vamos criar a tabela aluno. Abaixo segue o código para criação da tabela aluno:
create table aluno (
codigo number(5) not null, nome varchar2(50) not null, curso number(5) not null, sexo char(1) not null,
constraint cp_aluno primary key (codigo) );
Confirme o comando pressionado “Enter”. No exemplo que vimos criamos uma chave primária simples, mas se desejássemos criar uma composta deveríamos apenas informar os campos que a receberiam, separando-os com vírgula.
Sua tela do SQL*Plus deverá estar parecida com a seguinte:
Figura 2.7: Create Table Aluno
Agora que sabemos como criar constraint do tipo chave primária, vamos aprender a criar constraint do tipo chave estrangeira.
Uma chave estrangeira é um campo que possuo em uma tabela e esse mesmo campo é chave primária em outra tabela.
Vamos inverter a ordem que seguimos no aprendizado de chave primária. Veremos primeiramente como criar uma chave estrangeira no momento da criação de uma tabela.
Segue a sintaxe:
create table [tabela] ( [campo1] [tipo] [nulo?], [campo1] [tipo] [nulo?], .
. .
[campoN] [tipo] [nulo?], [campoE] [tipo] [nulo?],
constraint [nome da constraint] foreign key ([campoE]) references [tabelaE]([campoE])
);
Onde [nome da constraint] é o nome que daremos à nossa chave estrangeira, [campoE] é o campo que é chave primária na [tabelaE] e que nessa tabela que criamos será chave estrangeira. A diferença principal do código de criação de chave estrangeira para chave primária é que na chave estrangeira temos que especificar o nome da tabela em que esse campo é chave primária e o nome desse campo na outra tabela, por meio do comando references.
Vamos testar!
Vamos agora criar a tabela nota_modulo. Essa tabela registrará a nota final dos alunos por módulo. Como é apenas uma tabela que usaremos para estudo ela será o mais simples possível. Possuirá apenas três campos: cod_modulo, cod_aluno e nota_final.
create table nota_modulo ( cod_modulo number(5) not null, cod_aluno number(5) not null, nota_final number(2,2) not null,
constraint ce_nota_modulo-aluno foreign key (cod_aluno) references aluno (codigo)
);
Confirme o comando pressionando “Enter”. Sua tela do SQL*Plus deverá estar parecida com a seguinte:
Figura 2.8: Create Table Nota_Aluno
Na tabela que acabamos de criar o indicamos que o campo cod_aluno é chave estrangeira do campo codigo que está na tabela aluno. Note que chave primária e estrangeira não necessitam ter o mesmo nome, porém é necessário que elas armazenem o mesmo tipo de dados. Definimos o nome da constraint chave estrangeira seguindo o mesmo padrão que usamos na definição da constraint chave primária. Usamos o prefixo 'ce' como sigla de Chave Estrangeira e separamos com underline esse prefixo do nome da tabela em que se encontra essa constraint. Mesmo que a princípio pareça complicado, futuramente isso nos auxiliará muito, basta apenas nos acostumarmos com determinados padrões. Lembrando que seguir padrões é um bom hábito de programação, pois quando o sistema começa a ganhar volume esses padrões facilitam a vida de qualquer desenvolvedor.
No nosso caso criamos uma tabela com apenas uma chave estrangeira; mas e se quiséssemos criar uma tabela com várias constraints do tipo chave estrangeira? Muito simples, basta separar as constraints com vírgulas. Na sequencia é exibida a sintaxe para esse caso:
create table [tabela] ( [campo1] [tipo] [nulo?], [campo1] [tipo] [nulo?], .
. .
[campoN] [tipo] [nulo?], [campoE1] [tipo] [nulo?], [campoE2] [tipo] [nulo?],
constraint [nome da constraint 1] foreign key ([campoE1]) references [tabelaE1] ([campoE1]),
constraint [nome da constraint 2] foreign key ([campoE2]) references [tabelaE2] ([campoE2])
);
Desse modo podemos criar quantas chaves estrangeiras forem necessárias no momento da criação das tabelas.
Até o momento vimos como criar chaves estrangeiras no momento da criação das tabelas. Vamos ver agora como criar chaves estrangeiras para tabelas que já existem.
Bem, na verdade não há segredo nenhum, a única coisa que temos que fazer é usar o comando de criação de chave estrangeira dentro do comando alter table. Segue a sintaxe:
alter table [tabelaX] add (constraint [nome da constraint] foreign key ([campoY]) references [tabelaY] ([campoY]));
Usando esses comandos podemos criar chaves estrangeiras sem maiores problemas.
Para finalizar essa parte de nossos estudos sobre contraints falta-nos conhecer o último tipo de constraint que estudaremos aqui: constraint check.
Mas, afinal de contas, para que serve a restrição check?
Essa constraint serve para limitar as informações que o usuário pode inserir em determinado campo. Para entendermos melhor, nada como vermos a aplicação na prática.
Usaremos a tabela aluno para entendermos melhor esse conceito. Nessa tabela temos o campo sexo. Esse campo está definido como tipo char(1), ou seja, só armazenará um caracter. Definimos esse campo dessa maneira para que nele seja informado 'M' ou 'F'. Mas e se o usuário errar na hora da digitação, e em vez de 'M' ou 'F' colocar uma outra letra qualquer, ou até mesmo um número? Se esse campo não possuir nenhuma restrição ele aceitará o que o usuário inserir. Como é um erro perfeitamente possível acontecer (e acredite, mais frequente do que se imagina) é necessário colocarmos uma restrição no campo sexo, para que o mesmo aceite apenas as letras 'M' ou 'F'. Vamos conhecer e entender a sintaxe do comando que adiciona check's:
alter table [tabela] add (constraint [nome da constraint] check ([campo] [condição]));
Onde [tabela] é a tabela que possui o campo que receberá a constraint, [nome da constraint] é o nome que usaremos para identificar a constraint no banco de dados, [campo] é o campo que receberá a restrição e [condição] é o que pode ou não ser inserido no campo.
Vamos adicionar uma restrição do tipo check no campo sexo da tabela aluno. A seguir o comando:
alter table aluno add (constraint ck_sexo check (sexo in ('M', 'F')));
Confirme o comando pressionando “Enter”. Sua tela do SQL*Plus deverá estar parecida com a seguinte:
Figura 2.9: Alter Table Aluno Add Constraint
Vimos como com o comando anterior como adicionar uma constraint do tipo check em uma tabela já existente, mas como nos outros tipo de constraints é possível inserir a restrição no momento da criação da tabela. E, assim como nos outros tipos de constraints, é extremamente simples fazer isso. Segue a sintaxe
create table [tabela] ( [campo1] [tipo] [nulo?], [campo1] [tipo] [nulo?], .
. .
[campoN] [tipo] [nulo?],
constraint [nome da constraint] check ([campo] [condição]) );
Seguindo a sintaxe do comando anterior podemos, sem maiores dificuldades, criar uma restrição do tipo check no momento da criação da tabela.
Veremos agora como apagar essas restrições.
Caso a restrição seja criada de maneira errada, ou mesmo que muda alguma situação e haja a necessidade de apagar uma restrição usamos um comando muito simples, o qual a sintaxe é a que segue:
alter table [tabela] drop constraint [nome da constraint];
Onde [tabela] é o nome da tabela que possui a constraint e [nome da constraint] é a restrição em si.
Vamos testar!
Apagaremos a restrição do tipo check que criamos anteriormente na tabela aluno. A seguir o comando:
alter table aluno drop constraint ck_sexo_aluno;
Confirme o comando pressionando “Enter”. Sua tela do SQL*Plus deverá estar parecida com a seguinte:
Figura 2.10: Alter Table Aluno Drop Constraint
Finalizamos aqui nosso aprendizado sobre contraints. Sempre fique atento à essas restrições, pois são fundamentais para qualquer profissional que deseja trabalhar com o banco de dados Oracle, independente da versão do mesmo.
• Manipulação de dados com SQL
Até o momento vimos como formatar e organizar tabelas e campos. Mas até o momento não trabalhamos com dados, ou seja, com as informações que precisaremos gerenciar.
Pra início de nossos estudos veremos como inserir dados em uma tabela. Para essa tarefa criaremos a tabela cidades com as seguintes especificações:
Tabela: cidades
CAMPO TIPO TAMANHO NULO
codigo number 5 Não
nome varchar2 30 Não
estado char 2 Não
pais_regiao varchar2 20 Sim
Tabela 2.2: Tabela Cidades Após a criação dessa tabela, vamos inserir os dados. A sintaxe para inserção de dados na tabela é a seguinte:
insert into [tabela] ([campo1, campo2,..., campoN]) values ([valor1, valor2,..., valorN]);
Onde [tabela] é o nome da tabela que receberá os dados [campo1], [campo2], etc, são os nomes das colunas que receberão valores e [valor1], [valor2], etc, são as informações que inseriremos. Sempre que formos inserir números reais, no lugar da vírgula devemos colocar o ponto, pois a configuração de números reais do Oracle 10g XE segue o padrão dos Estados Unidos. E quando formos inserir dados do tipo de texto ou data, devemos colocar os valores entre aspas simples.
Vamos agora inserir valores na tabela que acabamos de criar. Digite a seguinte linha de comando:
insert into cidades (codigo, nome, estado, pais_regiao) values (1, ‘Ourinhos’, ‘SP’, ‘Sudeste’);
Após digitar o comando acima confirme pressionando a tecla “Enter”. Note que os dados que preencherão os campos do tipo char e varchar2 estão entre aspas simples. Sua tela do SQL*Plus deverá estar como a seguinte:
Figura 2.11: Insert Into Cidades
Para visualizarmos os dados inseridos utilizaremos o comando mais simples em SQL.
Digite no SQL*Plus o comando a seguir: select * from cidades;
Após digitar esse comando confirme pressionando a tecla “Enter”. Sua tela deverá estar semelhante a seguinte:
Figura 2.12: Select Cidades 1
Note uma peculiaridade: foi reservado um espaço muito pequeno para o campo estado, fazendo com que o nome do mesmo não apareça inteiro.
É interessante que visualizemos o nome dos campos completos, assim como os dados contidos nos mesmos.
Para isso vamos aprender um comando muito simples, mas que é muito interessante e útil nesses casos.
Por se tratar de um comando simples, não vamos analisar a sua sintaxe, mas veremos como ele funciona diretamente na prática.
Digite no SQL*Plus o seguinte comando: column estado format a8;
O número ‘8’ é o espaço que será reservado para a exibição da coluna estado. Confirme o comando pressionando a tecla “Enter”.
Neste momento não é percebida nenhuma alteração; então digite novamente o comando SQL que digitamos para visualizar os dados da tabela.
Figura 2.13: Column Format Sua tela do SQL*Plus deverá estar como a seguinte:
Agora que já sabemos inserir dados na tabela e como formatar o tamanho das colunas para melhor visualizá-las, vamos aprender a atualizar dados que já estão inseridos.
Para isso vamos inserir mais uma linha de dados na tabela cidades. Insira os dados exibidos a seguir:
Tabela: cidades
CAMPO VALOR
codigo 2
estado SP pais_regiao Sudeste
Tabela 2.3: Cidade 2
Após a inserção desses dados, digite um comando SQL de forma que todos os dados da tabela cidades sejam visualizados.
Sua tela do SQL*Plus deverá estar semelhante a seguinte:
Figura 2.14: Select Cidades 2
Vamos agora alterar a última linha inserida. Vamos supor que a cidade Assis não era para ser cadastrada com o código número 2, em vez disso deveria ser cadastrada com o código número 5.
Em vez de apagar toda a linha, alteraremos apenas o campo necessário. Para isso vamos usar o comando update.
Então digite no SQL*Plus o comando a seguir: update cidades set codigo = 5 where codigo = 2;
Após a digitação desse comando pressione a tecla “Enter” para confirmar. Se a operação for realizada com sucesso, sua tela do SQL*Plus deverá estar parecida com a seguinte:
Figura 2.15: Update Cidades
Agora digite um comando SQL que possibilite visualizar todos os registros da tabela cidades.
Sua tela do SQL*Plus deverá estar semelhante a seguinte:
Figura 2.16: Select Cidades 3
Note que apenas a coluna código teve seu valor alterado.
Se desejássemos alterar mais de um campo num único comando update, apenas teríamos que informar os campos a serem atualizados, separando-os com uma vírgula. A seguir a sintaxe para podermos entender melhor:
update [tabela] set [campo1] = [valor1], [campo2] = [valor2], ... , [campoN] = [valorN] where [campoX] = [condiçãoX];
Para isso utilizamos o comando delete. Vamos então apagar a linha da cidade Assis da nossa tabela cidades.
Para tal, utilizaremos o seguinte comando: delete from cidades where codigo = 5;
Após a digitação do código anterior, pressione a tecla “Enter”. Sua tela do SQL*Plus deverá estar como a seguinte:
Figura 2.17: Delete Cidades
Digite um comando SQL que possibilite visualizar todos os registros da tabela cidades.
Figura 2.18: Select Cidades 4 Sua tela do SQL*Plus deverá estar assim:
Finalizamos assim nosso aprendizado sobre inserção, atualização e exclusão de dados no banco de dados.
• Commit e Rollback.
Em toda inserção, alteração ou exclusão de dados, é necessário confirmar a operação realizada. Para tal, utilizamos o comando commit. O comando commit confirma todas as transações realizadas no banco de dados.
Assim como temos o comando commit confirmar todas as modificações no banco de dados, temos também o comando rollback, que cancela todas as modificações realizadas na sessão atual do banco de dados. Ou seja, se iniciarmos uma sessão e nessa sessão fizermos várias alterações nos dados e sairmos da sessão sem dar um commit tudo o que realizamos será perdido. Já o comando rollback quando informado por nós, cancela todas as alterações realizadas após o último commit.
Vamos testar!
Exclua o registro da cidade Ourinhos na tabela cidades.
Digite um comando select que lhe permita ver todos os dados da tabela cidades.
Sua tela do SQL*Plus deverá estar assim:
Figura 2.19: Select Cidades após Delete Após a confirmação da exclusão digite no seu SQL*Plus: rollback;
Digite um comando select que lhe permita ver todos os dados da tabela cidades.
Figura 2.20: Rollback
Note que o rollback desfez a modificação que havia sido realizada anteriormente.
Sempre que qualquer alteração for realizada no banco de dados, deve-se confirmar com o comando commit, para garantir que essa alteração foi salva. Para utilizar o comando commit basta digitá-lo como aparece a seguir sempre que se realizar alguma alteração no banco:
commit;
Embora sejam muito simples, commit e rollback são indispensáveis, e usá-los é um ótimo hábito para todo profissional que trabalha com banco de dados.
• “Rotulando” campos das tabelas.
A partir de agora, veremos como “rotular” campos. Rotular campos nada mais é que mudar o nome dos campos para melhor visualizá-los. Mas, na verdade, o campo não tem seu nome alterado, apenas é exibido um nome diferente quando executamos um comando select, ou seja, a estrutura da tabela dentro do banco de dados permanece a mesma.
Vamos testar!
No nosso teste continuaremos utilizando a tabela cidades. Note que quando efetuamos um comando select, a coluna pais_regiao aparece exatamente o nome dela na íntegra. Vamos mudar esse nome (somente para efeito de visualização). Para rotularmos campos é muito simples. Antes de vermos isso, veremos como fazer o comando select que vinhamos fazendo até o momento de forma diferente. Digite na tela do SQL*Plus o seguinte comando:
select codigo, nome, estado, pais_regiao from cidades;
Sua tela do SQL*Plus deverá estar semelhante a seguinte:
Figura 2.21: Select Cidades 5
Veja que o resultado que obtivemos é o mesmo resultado do comando quando usamos o asterisco. Isso porque em vez de usar o asterisco, que indica que queremos trazer o resultado de todos os campos, escolhemos digitar o nome dos campos.
Já que fizemos isso, digitaremos novamente o comando anterior, mas dessa vez com uma diferença que está destacada:
select codigo, nome, estado, pais_regiao as regiao from cidades;
Observe a diferença! No topo da coluna temos o nome regiao no lugar de pais_regiao:
Figura 2.22: Select com Rótulo
Se desejarmos colocar um rótulo com duas ou mais palavras, temos que utilizar as palavras entre aspas duplas.
Vamos à um exemplo. Vamos colocar um rótulo no campo nome. O rótulo será NOME DA CIDADE.
Então digite o seguinte comando:
select codigo, nome as “NOME DA CIDADE”, estado, pais_regiao from cidades;
Sua tela do SQL*Plus deverá estar parecida com a seguinte:
Finalizamos assim nosso estudo sobre rótulos de campos das tabelas do banco de dados.
• Concatenando colunas.
A partir desse ponto, conheceremos um outro recurso que facilita a visualização de dados no SQL*Plus: a concatenação de colunas. Concatenar nada mais é que unir duas colunas, para que os dados sejam melhor visualizados, seja por capricho do desenvolvedor, seja por necessidade.
Utilizaremos para essa fase de nosso aprendizado a tabela cidades.
Nessa tabela vamos concatenar os campos nome, estado e pais_regiao. Além de concatenarmos esses campos, vamos colocar um rótulo que servirá para todos eles.
Quando desejamos realizar uma concatenação devemos usar o símbolo pipe duas vezes consecutivas (||).
Vejamos na prática!
Digite no SQL*Plus o código a seguir:
select codigo, nome || estado || pais_regiao as local from cidades;
Sua tela deverá estar como a seguinte:
Figura 2.24: Concatenando Campos
Como mencionamos anteriormente, a concatenação é utilizada para melhor visualizar as informações. Mas no último exemplo tudo o que conseguimos foi fazer uma tremenda confusão entre os campos. Ainda é possível colocar entre os campos textos que o desenvolvedor achar necessário, e esses textos devem estar entre aspas simples. Para que tudo isso não fique muito abstrato, vamos sair da teoria e vamos à prática!
Observe com atenção a parte do código que está em destaque. Digite no SQL*Plus o seguinte comando SQL:
select codigo, nome ||', '|| estado ||' - Regiao: '|| pais_regiao as local from cidades;
O resultado na sua tela do SQL*Plus deverá estar semelhante à seguinte:
Figura 2.25: Concatenando Campos 2 Assim fica bem melhor!
Com relação à concatenação não há mais nada a estudar, pois esse assunto é muito simples. Mesmo sendo simples, é útil em determinadas situações, portanto não menospreze esse tópico, pois ele é pode te auxiliar posteriormente.
3ª Etapa
• Criação e execução de scripts SQL.A partir daqui iniciaremos nossos estudos sobre scripts SQL's.
Analisemos a seguinte situação: imaginemos que estamos inserindo dados por meio do SQL*Plus, utilizando o comando já conhecido insert.
E vamos pensar grande! E se estivéssemos cadastrando uma carga de dados de 20 clientes e no 16º cadastro acabasse a energia ou acontecesse algum incidente que forçasse o desligamento de nosso computador. Seria uma tragédia, pois não teríamos tempo suficiente para executar o comando commit e perderíamos todo o nosso trabalho. Para que isso não ocorra podemos criar scripts SQL's e salvá-los em um diretório qualquer de nosso computador.
Vamos entender melhor. No nosso exemplo utilizaremos o comando insert. Continuaremos usando a tabela cidades. Portanto vamos inserir uma nova cidade na nossa tabela.
Segue a tabela dos dados que vamos inserir: Tabela: cidades
codigo 3
nome Florianoplois
estado SC
pais_regiao Sul
Tabela 3.1: Cidade 3
Por enquanto não insira as informações, pois realizaremos essa tarefa de forma diferente, utilizando o comando dentro de um script SQL.
Para criarmos o script digite no seu SQL*Plus o seguinte código: edit C:\insert.sql;
Vamos entender: a palavra edit indica que iniciaremos um novo script; C:\ é o diretório escolhido onde o script será salvo; insert.sql é o nome que demos ao nosso script. Vale ressaltar que todo script que criarmos deverá ter a extesão .sql.
Então, após digitar o código anterior confirme pressionando o “Enter”. O resultado deverá ser o seguinte:
Figura 3.1: Iniciando Script
Assim que pressionarmos “Enter” será aberta uma janela do Bloco de Notas. Confirme clicando na opção Sim.
Agora na janela do Bloco de Notas digite o comando para inserção dos dados que estão na tabela anteriormente informada, tal como seria feito no SQL*Plus. Após digitar o comando insert salve o seu arquivo .sql.
Feito isso feche a janela do Bloco de Notas. Automaticamente voltamos à janela do SQL*Plus. Até o momento não inserimos nada na tabela cidades, apenas criamos o script.
Agora faremos o seguinte: carregaremos o script criado. Digite a seguinte linha de comando no SQL*Plus:
start C:\insert.sql;
Veja que é exibida a mensagem de confirmação de inclusão do registro:
Figura 3.2: Executando Script
Digite o comando SQL que possibilite a visualização de todos os registros da tabela cidades.
Figura 3.3: Select Cidades 6
Qualquer comando SQL pode ser gravado num script, tudo vai depender da necessidade do desenvolvedor.
Vamos agora visualizar o diretório onde salvamos nosso script. Vá até o diretório que você escolheu e encontre o arquivo .sql.
Exemplo:
Encerramos assim nosso aprendizado sobre criação e execução de scrips SQL. Mais um assunto simples e que é extremamente importante!
• Operadores lógicos em SQL.
A partir desse tópico, conheceremos os operadores lógicos da linguagem SQL. Vale lembrar que SQL não é uma linguagem de programação, e sim uma linguagem de busca que é padrão nos diversos bancos de dados existentes. Porém, mais adiante, veremos a PL/SQL, essa sim é uma linguagem de programação da Oracle baseada em SQL. Portanto aprender o básico de SQL é fundamental para que posteriormente não tenhamos maiores dificuldades em PL/SQL.
A seguir, temos uma tabela com os operadores lógicos que abordaremos nesse curso:
Operador lógico Nome Descrição
= igual
Esse é o operador básico de qualquer linguagem de programação ou de consulta. Sua representação, deiferente de algumas linguagens de programação, é simplesmente o sinal de 'igual'.
<> diferente Aqui temos o sinal de 'diferença'. É reprensentado pela junção do sinal de 'maior' e 'menor'.
> maior O sinal de 'maior', assim como o de 'igual', é representado apenas pelo seu símbolo. < menor Assim como o sinal de 'maior' e 'igual', o sinal de 'menor' também é representado pelo seu símbolo. >= maior ou igual
'Maior ou igual' também segue o modelo que vimos até o momento, ou seja, é a junção do sinal de 'maior' com o sinal de 'igual' (sempre nessa ordem).
<= menor ou igual 'Menor ou igual' também não foge à regra. Ele é a junção dos símbolos de 'menor' e 'igual' (sempre nessa ordem).
AND e
O operador and é um dos mais utilizados em consultas SQL's. Utilizamo-os quando trabalhamos com mais de uma cláusula de comparação.
OR ou Como o próprio nome diz, o operador or nos permite comparar uma ou mais colunas com um ou mais valores.
BETWEEN entre
Usamos o between quando queremos retornar um intervalo de valores, então informamos o valor mínimo e o valor máximo como parâmetros. O between retornará inclusive os parâmetros informados, caso os mesmos existam no campo que estivermos trabalhando.
IN em Usamos o in quando queremos comparar o valor de uma coluna com vários valores. LIKE parecido
A palavra like em inglês possui algumas traduções. No nosso contexto pode ser traduzida como 'parecido'.
sequencia de caracteres especificos. Na teoria é complicado entendermos como funciona o like, que normalmente é utilizado em conjunto com o símbolo %, mas na prática veremos que ele é bem mais simples do que parece.
NOT não
O operador not é utilizado sempre em conjunto com outro operador lógico (normalmente com o in ou like). Ou seja, usamos ele quando queremos obter o resultado contrário de determinda operador.
Tabela 3.2: Operadores Lógicos
Vejamos na prática algumas situações em que podemos utilizar os operadores descritos acima.
Para isso, vamos inseri na nossa tabela cidades mais alguns registros.
A seguir a tabela dos dados que devemos inserir (se optar salve os comando de inserção em scrips SQL):
Tabela: cidades
CAMPO VALOR
codigo 4
nome São Paulo
estado SP pais_regiao Sudeste Tabela 3.3: Cidade 4 Tabela: cidades CAMPO VALOR codigo 5
nome Belo Horizonte
estado MG pais_regiao Sudeste Tabela 3.4: Cidade 5 Tabela: cidades CAMPO VALOR codigo 6 nome Goiania estado GO pais_regiao Centro-oeste Tabela 3.5: Cidade 6 Tabela: cidades
CAMPO VALOR codigo 7 nome Recife estado PE pais_regiao Nordeste Tabela 3.6: Cidade 7 Tabela: cidades CAMPO VALOR codigo 8 nome Assis estado SP pais_regiao Sudeste Tabela 3.7: Cidade 8
Após a inserção, digite um comando SQL de forma que seja possível visualizar todos os dados da tabela cidades. Se houver algum erro fique na liberdade de corrigi-lo.
Vamos imaginar que não sabemos a qual cidade está atribuído o código 7. Vamos então elaborar um comando SQL para retornar esse resultado. Digite no SQL*Plus o comando seguinte:
select * from cidades where codigo = 7;
Após digitar o comando anterior pressione “Enter” para confirmar. Sua tela do SQL*Plus deverá estar como a seguinte:
Figura 3.5: Select Cidades Codigo 7
Neste exemplo vimos como utilizar o operador lógico 'igual'.
No nosso próximo exemplo, utilizaremos o operador lógico 'diferente'.
Vamos imaginar que queremos visualizar todas as cidades, menos as da região Sudeste.
Para isso digite o comando abaixo:
select * from cidades where pais_regiao <> 'Sudeste';
Note que quando vamos fazer comparação com campos de texto, assim como para inserí-los, necessitamos informar o texto entre aspas simples.
Figura 3.6: Operador Lógico “Diferente” Vamos a mais testes!
Agora veremos a funcionalidade do operador 'maior', que diga-se de passagem, não tem nenhum segredo.
Vamos fazer uma consulta que retorne apenas os códigos maiores que 6. Segue o código:
select * from cidades where codigo > 6;
Após pressionar o “Enter”, você terá na tela o seguinte resultado:
Figura 3.7: Oprador Lógico “Maior”
No nosso próximo exemplo vamos ver o resultado do mesmo comando anteriormente digitado, porém no lugar do sinal 'maior' utilizaremos o 'menor':
select * from cidades where codigo < 6;
Observe sua tela, ela deve estar semelhante à seguinte:
Figura 3.8: Operador Lógico “Menor”
Veja que tanto na utilização dos sinais 'maior' e 'menor', o valor que é comparado (no nosso caso o número 6) não é exibido.
Para que ele seja exibido, devemos utilizar o sinal de 'maior' ou 'menor' juntamente com o sinal de 'igual'.
Segue os exemplos para os dois casos: select * from cidades where codigo >= 6; select * from cidades where codigo <= 6;
Figura 3.9: Operador Lógico “Maior ou Igual”
Figura 3.10: Operador Lógico “Menor ou Igual”
No nosso próximo exemplo, veremos a funcionalidade do and. Sem mais delongas, vamos a um exemplo. Digamos que nossa intenção seja retornar no SQL*Plus as cidades que pertencem a região sudeste e que possui o código maior que 4.
Para isso, podemos executar um comando SQL como o seguinte:
select * from cidades where pais_regiao = 'Sudeste' and codigo > 4;
Figura 3.11: Operador Lógico “And” Vejamos agora o funcionamento do operador or.
Vamos usar o mesmo exemplo anterior, mas substituiremos o and pelo or e o 4 pelo 7.
Digete no seu SQL*Plus:
select * from cidades where pais_regiao = 'Sudeste' or codigo > 7;
O resultado deverá ser o seguinte:
Figura 3.12: Operador Lógico “Or”
Vamos agora conhecer o between. Como já foi dito, precisamos informar um valor inicial e um final para que o between traga todos os valores que ele encontrar que estiverem entre os valores informados. Então façamos um comando SQL de tal forma que ele nos retorne as cidades que possuem códigos entre 3 e 5.
Segue o comando a ser digitado:
select * from cidades where codigo between 3 and 5;
Observe o resultado:
Figura 3.13: Operador Lógico “Between”
Note que os valores 3 e 5 (os que usamos como parâmetros) também são exibidos.
Dando sequencia aos nossos estudos, veremos agora como funciona o operador in. Ele, na verdade, tem a mesma funcionalidade do operador 'igual', porém, diferentemente do 'igual', no in podemos comparar vários valores ao mesmo tempo.
Para entendermos melhor, vamos ao exemplo.
Vamos supor que nossa necessidade seja buscar cidades de código aleatórios. Para isso faremos uso do in.
Segue o comando SQL que nos permite realizar essa busca: select * from cidades where codigo in (1, 3, 6, 8, 5);
Figura 3.14: Operador Lógico “In”
Veja que o select que elaboramos nos retornou os códigos que informamos dentro do parênteses do in. Vale ressaltar que os valores sempre deverão estar entre parênteses na utilização do in.
O próximo tópico que abordaremos faz referência ao operador like. Toda vez que utilizarmos o like, devemos informar uma sequencia de caracteres. Podemos, ainda, utilizar o símbolo % antes ou depois dessa sequencia, ou ainda antes e depois. Quando colocamos o % antes dos caracteres indicamos que não importa o que há antes dos caracteres informados. Quando colocamos depois da sequencia de caracteres, informamos que não importa o que vem depois, e quando colocamos o % antes e depois, indicamos que esses caracteres podem estar entre qualquer palavra ou texto.
Vejamos na prática!
Primeiramente vamos ver um exemplo do uso do like com o símbolo % antes da sequencia de caracteres.
Vamos supor que queremos trazer todas as cidades que ficam em regiões terminadas com 'este'.
Para isso podemos digitar o seguinte comando:
select * from cidades where pais_regiao like '%este';
