SQL em ambiente Oracle, Notas de estudo de Processamento de Dados

Baixe SQL em ambiente Oracle e outras Notas de estudo em PDF para Processamento de Dados, somente na Docsity!

Índice

• SQL em ambiente Oracle..............................................................................................................................
  • Índice ........................................................................................................................................................
  • I Introdução .........................................................................................................................................
  • Tabelas básicas a serem utilizadas no curso .............................................................................................
  • Operações relacionais ...............................................................................................................................
  • Uma instalação Oracle típica ....................................................................................................................
  • II SQL (Structured Query Language) .................................................................................................
  • Introdução ...............................................................................................................................................
  • Comandos DML SQL.............................................................................................................................
    • Comando SELECT ..........................................................................................................................
      • 1. Exibindo todos os dados de uma tabela. .............................................................................
      • 2. Exibindo (projetando) o conteúdo de apenas algumas colunas da tabela............................
      • 3. Exibindo valores calculados................................................................................................
      • 4. Especificando um critério para seleção (filtragem) de linhas (cláusula WHERE). ...............
      • 5. Exibindo linhas com ou sem repetição de dados (modificadores ALL | DISTINCT). .......
      • 6. Renomeando colunas no resultado da consulta (operador AS). ..........................................
      • 7. Projetando o resultado de uma função. ...............................................................................
      • 8. Exibindo os dados de uma tabela em uma determinada ordem (cláusula ORDER BY). ......
      • 9. Fazendo totalizações simples (uso de funções de agregação SQL). ...................................
      • 10. Produzindo totais com agrupamentos de linhas (cláusulas GROUP BY e HAVING)..........
      • 11. Utilizando aliases (variáveis tupla) para tabelas. ................................................................
      • 12. Buscando dados em mais de uma tabela (junções). ............................................................
      • 13. Fazendo cálculos em agrupamentos com junção de várias tabelas. ....................................
      • 14. Fazendo consultas com subconsultas. .................................................................................
      • 15. Fazendo junção de uma tabela com ela mesma (auto-relacionamento). .............................
      • 16. Fazendo a operação de união de conjuntos (operador UNION). .........................................
      • 17. Fazendo a operação de intersecção de conjuntos (operador INTERSECT). .......................
      • 18. Fazendo a operação de diferença de conjuntos (operador MINUS).....................................
      • 19. Utilizando condições ao projetar valores (expressões CASE).............................................
      • 20. Retendo apenas as n primeiras linhas de um resultado (pseudo-coluna ROWNUM). ............
    • Comando INSERT ...........................................................................................................................
      • 21. Incluindo valores utilizando a cláusula VALUES................................................................
      • 22. Incluindo valores a partir de um SELECT. .........................................................................
    • Comando DELETE ..........................................................................................................................
      • 23. Excluindo linhas de tabelas.................................................................................................
    • Comando UPDATE..........................................................................................................................
      • 24. Alterando várias colunas em um comando. ........................................................................
      • 25. Alterando várias linhas em um comando. ...........................................................................
    • Resumo dos principais operadores suportados pela SQL ........................................................
    • Resumo das principais funções de agregação............................................................................
  • Funções pré-definidas em SQL...............................................................................................................
    • ABS ....................................................................................................................................................
    • ADD_MONTHS ................................................................................................................................
    • ASCII..................................................................................................................................................
    • AVG....................................................................................................................................................
    • CEIL ...................................................................................................................................................
    • CHR....................................................................................................................................................
    • COUNT ..............................................................................................................................................
    • DECODE ...........................................................................................................................................
    • FLOOR...............................................................................................................................................
    • GREATEST.......................................................................................................................................
    • INITCAP.............................................................................................................................................
    • INSTR ................................................................................................................................................
    • LAST_DAY........................................................................................................................................
    • LEAST................................................................................................................................................
    • LENGTH ............................................................................................................................................
  • LOWER..............................................................................................................................................
  • LPAD..................................................................................................................................................
  • LTRIM ................................................................................................................................................
  • MAX....................................................................................................................................................
  • MIN .....................................................................................................................................................
  • MOD ...................................................................................................................................................
  • MONTHS_BETWEEN .....................................................................................................................
  • NEXT_DAY .......................................................................................................................................
  • NVL ....................................................................................................................................................
  • POWER .............................................................................................................................................
  • REPLACE..........................................................................................................................................
  • ROUND – para números.................................................................................................................
  • ROUND – para datas ......................................................................................................................
  • RPAD .................................................................................................................................................
  • RTRIM..............................................................................................................................................
  • SIGN ................................................................................................................................................
  • SOUNDEX.......................................................................................................................................
  • SQRT ...............................................................................................................................................
  • STDDEV ..........................................................................................................................................
  • SUBSTR ..........................................................................................................................................
  • SUM .................................................................................................................................................
  • TO_CHAR – para números ..........................................................................................................
  • TO_CHAR – para datas e horas..................................................................................................
  • TO_DATE........................................................................................................................................
  • TO_NUMBER .................................................................................................................................
  • TRANSLATE...................................................................................................................................
  • TRUNC – para números ...............................................................................................................
  • TRUNC – para datas .....................................................................................................................
  • UPPER.............................................................................................................................................
  • USERENV .......................................................................................................................................
  • VARIANCE......................................................................................................................................
  • VSIZE...............................................................................................................................................
• Comandos DDL SQL............................................................................................................................
  • Tipos de dados no Oracle..................................................................................................................
  • Regras para nomes no Oracle ...........................................................................................................
  • Restrições (Constraints) ....................................................................................................................
  • Comando CREATE TABLE ..........................................................................................................
  • Comando ALTER TABLE .............................................................................................................
  • Comando DROP TABLE...............................................................................................................
  • Comando CREATE VIEW.............................................................................................................
  • Comando DROP VIEW .................................................................................................................
  • Comando CREATE INDEX...........................................................................................................
  • Comando DROP INDEX ...............................................................................................................
• Exercícios: Caso “Banco simplificado” ................................................................................................
• Comandos DCL SQL............................................................................................................................
  • Comando GRANT ..........................................................................................................................
  • Comando REVOKE .......................................................................................................................
  • Comando CREATE USER............................................................................................................
  • Comando DROP USER ................................................................................................................
• III SQL *Plus..............................................................................................................................
• Introdução .............................................................................................................................................
  • Abertura de sessão SQL *Plus ..........................................................................................................
• Relação de comandos SQL *Plus .........................................................................................................
  • Comando LIST ...............................................................................................................................
  • Comando APPEND........................................................................................................................
  • Comando CHANGE .......................................................................................................................
  • Comando DEL ................................................................................................................................
  • Comando DESCRIBE....................................................................................................................
  • Comando EXIT ...............................................................................................................................
  • Comando INPUT ............................................................................................................................
• Instruções para consultas ao catálogo do SGBD...................................................................................
  • Como obter os nomes das tabelas disponíveis no banco de dados................................................
  • Como ver a estrutura de uma tabela do banco de dados ...............................................................
  • Como ver os índices de uma tabela...............................................................................................
  • Como obter os nomes das visões disponíveis no banco de dados.................................................
  • Como descobrir o SELECT de uma visão já criada no banco de dados .......................................
  • Como modificar a senha de um usuário do banco de dados .........................................................

I Introdução

Tabelas básicas a serem utilizadas no curso

Para ilustrar os conceitos e recursos a serem trabalhados durante o curso, utilizaremos um

conjunto de 3 tabelas bastante simples que implementam um pequeno controle de pessoal. As

estruturas e os relacionamentos dessas tabelas são exibidos na figura 1.1 e para criá-las no

Oracle você deverá executar o script CriaPes_ora.sql.

Figura 1.1: Esquema lógico do controle de funcionários.

A tabela Cargos contém a descrição dos cargos, por meio de 3 colunas: um código na coluna

CdCargo, que é a sua chave primária; o nome do cargo, na coluna NmCargo, que é de

preenchimento obrigatório e não aceita valores repetidos e o salário mensal referente ao

cargo, que é armazenado na coluna numérica VrSalario. Na tabela Deptos está a descrição dos

departamentos: um código, na coluna CdDepto, que é a chave primária da tabela; o nome do

departamento, na coluna NmDepto, que é de preenchimento obrigatório e que não aceita

valores repetidos e o número do ramal telefônico, armazenado na coluna Ramal. A tabela

Funcionarios contém os dados dos funcionários cadastrados no banco de dados. NrMatric é o

número de matrícula do funcionário e é a chave primária da tabela; NmFunc é o nome

completo do funcionário, de preenchimento obrigatório; DtAdm indica a data em que o

funcionário foi admitido na empresa; Sexo informa o gênero: ‘M’ para masculino e ‘F’ para

feminino; CdDepto é a chave estrangeira de preenchimento opcional que associa um

funcionário ao seu departamento; CdCargo é outra chave estrangeira, de preenchimento

obrigatório, para indicar o cargo exercido pelo funcionário e, por fim, CdSuperv é o número

de matrícula do supervisor do funcionário, que implementa uma auto-relacionamento

opcional, já que um funcionário pode não ter supervisor.

Operações relacionais

No modelo relacional, cada tabela (chamada relação) é tratada como um conjunto onde seus

elementos são linhas (tuplas) e as manipulações sobre esses conjuntos é implementada através

de operações especiais, chamadas operações relacionais, que são mostradas na tabela a seguir.

O resultado de qualquer operação relacional é uma nova relação (tabela).

Operação Finalidade

Seleção Obter apenas as tuplas (linhas) desejadas em uma tabela

Projeção Obter os componentes desejados (colunas) das linhas selecionadas

Junção Obter uma tabela formada pela combinação das linhas de duas tabelas

originais que possuem colunas com valores comuns

Produto

Cartesiano

Obter todas as combinações possíveis dos elementos (linhas) de duas

tabelas

União Obter uma tabela cujas linhas foram obtidas a partir de duas tabelas

originais diferentes (porém compatíveis)

Diferença Obter as linhas de uma tabela que não possuem correspondência em

outra tabela compatível

Renomeação Atribuir apelidos lógicos a uma tabela dentro de uma operação

Intersecção Obter os elementos (linhas) comuns a duas tabelas compatíveis

Divisão Obter os elementos (linhas) de uma tabela que possuem

correspondência com todos os elementos de um conjunto

Atribuição Atribuir o resultado de uma seleção a uma variável para uso posterior

Algumas dessas operações são fundamentais e outras são suas derivadas. As operações que

utilizamos com maior freqüência são a de Seleção, de Projeção e Junção. Exemplos de cada

uma dessas operações com as tabelas que utilizaremos neste curso são mostrados a seguir.

Seleção : Obter todos os funcionários cuja coluna CdDepto possua o valor “D001”.

Resultado:

NrMatric NmFunc DtAdm Sexo CdCargo CdDepto 1034 ROBERTO PEREIRA 23/05/1992 M C003 D 1021 JOSE NOGUEIRA 10/11/1994 M C003 D 1095 MARIA DA SILVA 03/09/1992 F C004 D 1042 PEDRO PINHEIRO 29/07/1994 M C004 D

Projeção : Obter os valores referentes às colunas NmFunc e NrMatric de todos os

funcionários cuja colunas CdCargo possua o valor “C002”.

Resultado :

NmFunc NrMatric JOAO SAMPAIO 1001 LUCIO TORRES 1004 LUIZ DE ALMEIDA 1023 PAULO RODRIGUES 1015

Junção : Obter os valores referentes às colunas NrMatric, NmFunc e NmCargo de todas as

linhas da tabela de funcionários em que a coluna CdDepto possua o valor “D001”

Resultado :

NrMatric NmFunc NmCargo 1034 ROBERTO PEREIRA AUX. ESCRITÓRIO 1021 JOSE NOGUEIRA AUX. ESCRITÓRIO 1095 MARIA DA SILVA ESCRITURÁRIO 1042 PEDRO PINHEIRO ESCRITURÁRIO

Observe que neste último caso precisamos combinar os dados de duas tabelas, uma vez que as

colunas NrMatric e NmFunc a serem projetadas estão na tabela Funcionários e a coluna

NmCargo encontra-se na tabela Cargos.

II SQL (Structured Query Language)

Introdução

Desenvolvida inicialmente nos laboratórios da IBM na primeira metade dos anos 70, a SQL é

uma linguagem de acesso a bancos de dados. Baseia-se nos princípios lançados por E. F.

Codd, tendo sido projetada para viabilizar a construção de um produto relacional pela IBM.

Apesar dessa origem, constitui-se hoje num padrão internacional para os SGBDRs, com

especificações formalizadas pela ANSI (o último padrão publicado é de 1992, chamado SQL-

92) e adotadas em maior ou menor grau por vários fornecedores de bancos de dados, tendo

sido implementada inclusive por produtos não exatamente relacionais.

Em suas definições, a SQL padrão prevê um reduzido conjunto de comandos, que podem ser

classificados em 3 grupos:

Data Manipulation Language (DML) : permite consultar, adicionar, alterar e excluir

informações do banco de dados. Dentro da sintaxe de cada comando são permitidas algumas

cláusulas adicionais que viabilizam operações relacionais tais como seleção, junção, união,

etc e a utilização de algumas funções de uso mais comum, como de cálculo de média, soma,

contagem de registros e determinação de máximo e mínimo entre valores.

Data Definition Language (DDL) : permite criar e gerenciar a estrutura do banco de dados,

como por exemplo criar ou eliminar tabelas e índices. É possível também a especificação de

alguns itens de segurança adicionais, como por exemplo a definição de quais informações são

obrigatórias dentro de uma dada tabela, que procedimentos devem ser associados a uma

operação em particular sobre o banco de dados, etc.

Data Control Language (DCL) : permite definir as contas dos usuários autorizados a acessar

o banco de dados, bem como impor restrições de uso a esses usuários.

Uma visão um pouco mais detalhada dos principais comandos de cada grupo será vista mais

adiante neste material.

Tecnicamente seria mais correto caracterizar a SQL padrão como uma sublinguagem, já que

não proporciona comandos ou declarações próprios para o controle de repetições (loopings),

de desvios (condicionais e incondicionais) ou de seqüências. Assim, não é possível a

construção de aplicações com um mínimo de complexidade utilizando apenas a SQL padrão.

Por esse motivo os principais gerenciadores do mercado desenvolveram extensões próprias

(não padronizadas) à SQL de maneira a permitir a construção de rotinas para o SGBD. São

exemplos dessas extensões a PL/SQL (Oracle) e Transact SQL (MS-SQL Server) entre outras.

Existem duas formas básicas em que a SQL é utilizada: a interativa (ou dinâmica) feita em

nível de linha de comando, com os comandos sendo processados por um módulo

interpretador, e a embutida (" embedded SQL"), onde o programa é codificado numa

linguagem mais genérica (como o Cobol, C, Pascal ou PL/1) e faz chamadas a declarações

SQL em algumas situações. Dizemos nesse último caso que a SQL está associada com uma

linguagem " host " ou "hospedeira".

Mais recentemente, a difusão da SQL ganhou um significativo impulso com o advento da

tecnologia Cliente / Servidor, tendo consolidado-se como o elemento chave na conexão entre

SGBDs de fornecedores diferentes. É importante ressaltar que existem diferenças entre as

implementações da sintaxe dos comandos conforme o fabricante, mas essas diferenças

geralmente são secundárias e existe sempre uma sintaxe mínima que é suportada por todos os

produtos.

Comandos DML SQL

Já vimos que os recursos SQL podem ser divididos em 3 grupos: DDL, DML e DCL.

Veremos aqui a parte aparentemente mais evidente da SQL, aquela referente à manipulação

de dados (DML), composta por um pequeno conjunto de comandos, uma variedade de

operadores e inúmeras funções (algumas padronizadas para todos os SGBDRs, outras não).

Um resumo dos comandos DML é apresentado na tabela a seguir:

Comando Descrição

INSERT Adiciona novos dados a uma tabela

DELETE Remove dados de uma tabela

UPDATE Modifica dados existentes em uma tabela

SELECT Busca dados já cadastrados nas tabelas de um banco de

dados

Veremos agora em detalhes cada um desses comandos, que serão apresentados inicialmente

em sua sintaxe padrão e depois ilustrados por meio de diversos exemplos, onde os resultados

esperados são apresentados e, sempre que necessário, comentados. É importante prestar a

devida atenção a esses comentários, que discutem uma série de aspectos teóricos e práticos

dos recursos utilizados.

Exemplos:

1. Exibindo todos os dados de uma tabela.

Exemplo 1a: Obtendo todo o conteúdo, ou seja, o valor de todas as colunas de todas as

linhas, da tabela de funcionários.

SELECT *

FROM Funcionarios ;

Resultado:

NRMATRIC NMFUNC DTADM SEXO CDCARGO CDDEPTO CDSUPERV

1048 ANA SILVEIRA 1993-06-01 F C

1001 JOAO SAMPAIO 1993-08-10 M C002 D002 1048

1004 LUCIO TORRES 1994-03-02 M C002 D002 1001

1042 PEDRO PINHEIRO 1994-07-29 M C004 D001 1048

1034 ROBERTO PEREIRA 1992-05-23 M C003 D001 1042

1021 JOSE NOGUEIRA 1994-11-10 M C003 D001 1042

1029 RUTH DE SOUZA 1992-11-05 F C001 D003 1042

1095 MARIA DA SILVA 1992-09-03 F C004 D001 1042

1023 LUIZ DE ALMEIDA 1993-01-12 M C002 D002 1001

1015 PAULO RODRIGUES 1992-08-17 M C002 D002 1001

Comentários:

O asterisco colocado após a palavra SELECT significa “todas as colunas”. Na

cláusula FROM especificamos a tabela ou tabelas de onde os dados serão obtidos. A

ordem em que as linhas do resultado são mostradas no seu computador pode ser

diferente da indicada aqui. O formato da data de admissão também, pois depende

de parâmetros de configuração do seu banco de dados.

Exemplo 1b: Obtendo todo o conteúdo da tabela de cargos.

SELECT *

FROM Cargos ;

Resultado:

CDCARGO NMCARGO VRSALARIO

C001 COZINHEIRA 350.

C003 AUX. ESCRITORIO 450.

C006 VIGIA 500.

C002 MECANICO 750.

C005 GERENTE 2300.

C004 ESCRITURARIO 600.

2. Exibindo (projetando) o conteúdo de apenas algumas colunas da tabela.

Exemplo 2a: Listando, para cada funcionário, o seu nome, número de matrícula e data

de admissão.

SELECT NmFunc, NrMatric, DtAdm FROM Funcionarios ;

Resultado:

NMFUNC NRMATRIC DTADM

ANA SILVEIRA 1048 1993-06-

JOAO SAMPAIO 1001 1993-08-

LUCIO TORRES 1004 1994-03-

PEDRO PINHEIRO 1042 1994-07-

ROBERTO PEREIRA 1034 1992-05-

JOSE NOGUEIRA 1021 1994-11-

RUTH DE SOUZA 1029 1992-11-

MARIA DA SILVA 1095 1992-09-

LUIZ DE ALMEIDA 1023 1993-01-

PAULO RODRIGUES 1015 1992-08-

Comentários:

Observe que, em vez do asterisco, especificamos explicitamente quais as colunas

que queremos ter no resultado.

Exemplo 2b: Exibindo, para cada departamento, o seu nome e respectivo ramal

telefônico.

SELECT NmDepto, Ramal FROM Deptos ;

Resultado:

NMDEPTO RAMAL

ADMINISTRACAO 320

OFICINA 310

SERVICOS GERAIS 330

VENDAS 300

modificar o conteúdo da coluna VrSalario deveríamos utilizar outro comando, o

comando UPDATE, que será apresentado posteriormente.

4. Especificando um critério para seleção (filtragem) de linhas (cláusula WHERE).

Exemplo 4a: Exibindo os funcionários do departamento D001.

SELECT *

FROM Funcionarios WHERE CdDepto = 'D001' ;

Resultado:

NRMATRIC NMFUNC DTADM SEXO CDCARGO CDDEPTO CDSUPERV

1042 PEDRO PINHEIRO 1994-07-29 M C004 D001 1048

1034 ROBERTO PEREIRA 1992-05-23 M C003 D001 1042

1021 JOSE NOGUEIRA 1994-11-10 M C003 D001 1042

1095 MARIA DA SILVA 1992-09-03 F C004 D001 1042

Comentários:

Os códigos dos departamentos cadastrados possuem como inicial uma letra ‘D’

maiúscula. Na consulta do exemplo, se tivéssemos escrito 'd001', nenhuma linha

seria apresentada no resultado, pois para cadeias de caracteres, a comparação

considera que uma letra em minúsculas é diferente dessa mesma letra em

maiúsculas. No jargão da computação dizemos que é uma comparação case

sensitive (sensível a maiúsculas e minúsculas). Como regra geral, considere que

qualquer conteúdo em forma de texto que apareça entre aspas simples em um

comando SQL é case sensitive.

Exemplo 4b: Mostrando o número de matrícula e nome de todas as funcionárias.

SELECT NrMatric, NmFunc FROM Funcionarios WHERE Sexo = 'F' ;

Resultado:

NRMATRIC NMFUNC

1095 MARIA DA SILVA

1029 RUTH DE SOUZA

1048 ANA SILVEIRA

Exemplo 4d: Projetando a matrícula, o nome e o código do cargo para os funcionários

que possuem a substring ‘EIR’ em qualquer parte do nome (operador LIKE).

SELECT NrMatric, NmFunc, CdCargo FROM Funcionarios WHERE NmFunc LIKE '%EIR%' ;

Resultado:

NRMATRIC NMFUNC CDCARGO

1048 ANA SILVEIRA C

1042 PEDRO PINHEIRO C

1034 ROBERTO PEREIRA C

1021 JOSE NOGUEIRA C

Comentários:

O operador LIKE permite comparar uma string com um formato definido por meio

de constantes e curingas. Existem 2 curingas: o %, que significa ‘qualquer

combinação de caracteres’ e o underscore ‘_’, que significa ‘exatamente um

caracter qualquer’. No exemplo apresentado, compusemos uma máscara

‘%EIR%’, que significa ‘qualquer combinação de caracteres, seguido da cadeia

“EIR” e terminando com qualquer combinação de caracteres’.

Exemplo 4e: Projetando os nomes dos funcionários que possuem uma letra ‘A’ como

terceiro caracter do nome.

SELECT NmFunc FROM Funcionarios WHERE NmFunc LIKE '__A%' ;

Resultado:

NMFUNC

ANA SILVEIRA

JOAO SAMPAIO

Comentários:

A máscara que desejamos é ‘qualquer caracter na primeira posição, qualquer

caracter na segunda posição, uma letra A na terceira posição e depois, qualquer

combinação de caracteres’.

Exemplo 4f: Listando os cargos com salário entre $450 e $1000 (operador BETWEEN).

SELECT *

FROM Cargos WHERE VrSalario BETWEEN 450 AND 1000 ;

Resultado:

CDCARGO NMCARGO VRSALARIO

C003 AUX. ESCRITORIO 450.

C006 VIGIA 500.

C002 MECANICO 750.

C004 ESCRITURARIO 600.

Comentários:

O operador BETWEEN permite verificar se um dados está dentro de um intervalo,

sendo geralmente utilizado para o tratamento de números e datas. A estrutura do

operador é BETWEEN AND , e a

expressão resulta em verdadeiro para valores que sejam iguais ou maiores que

e menores ou iguais a , o que significa

que a consulta do exemplo é equivalente a

SELECT *

FROM Cargos WHERE VrSalario >= 450 AND VrSalario <= 1000 ;