• Nenhum resultado encontrado

TRIBUNAL DE JUSTIÇA DO ESTADO DO RIO DE JANEIRO DIRETORIA GERAL DE TECNOLOGIA DA INFORMAÇÃO DEPARTAMENTO DE SISTEMAS DE INFORMAÇÃO.

N/A
N/A
Protected

Academic year: 2021

Share "TRIBUNAL DE JUSTIÇA DO ESTADO DO RIO DE JANEIRO DIRETORIA GERAL DE TECNOLOGIA DA INFORMAÇÃO DEPARTAMENTO DE SISTEMAS DE INFORMAÇÃO."

Copied!
40
0
0

Texto

(1)

TRIBUNAL DE JUSTIÇA DO ESTADO DO RIO DE JANEIRO

DIRETORIA GERAL DE TECNOLOGIA DA INFORMAÇÃO

DEPARTAMENTO DE SISTEMAS DE INFORMAÇÃO

SQL Básico

Cláudio da Costa Blanco

Zanoni da Conceição Maia Jr.

(2)

ÍNDICE

Módulo 1 – Introdução a Sistemas de Banco de Dados ... 4

Conceito... 4

Comparação entre Banco de Dados e Processamento Tradicional de Arquivos... 4

Auto Informação... 4

Separação entre Programas e Dados... 5

Abstração de Dados... 5

Múltiplas Visões de Dados... 5

Vantagens e desvantagens do uso de um SGBD ... 6

Controle de Redundância ... 6

Compartilhamento de Dados ... 6

Restrição a Acesso não Autorizado... 6

Representação de Relacionamentos Complexos entre Dados ... 6

Tolerância a Falhas... 6

O Modelo Relacional de Dados... 7

Terminologia do Modelo Relacional... 7

Modelagem de Dados utilizando o Modelo Entidade Relacionamento (MER) ... 7

Entidades e Atributos ... 8

Relacionamentos... 8

Grau de um Relacionamento ... 9

Restrições de Relacionamentos ... 9

Entidades Fracas... 10

Diagrama Entidade Relacionamento ... 11

SQL... 12

Módulo 2 – Consultas SQL ... 14

Selecionando dados de tabelas ... 14

Seleção de colunas específicas ... 14

Seleção de todas as colunas... 14

Seleção sem valores duplicados ... 15

Emprego de operadores aritméticos ... 15

Utilizando um alias para coluna ... 15

Ordenando as linhas selecionadas ... 15

Restringindo as linhas selecionadas ... 16

Operadores para comparações lógicas... 16

Operadores lógicos ... 18

Funções... 18

Funções de Linha... 18

Funções de Linha que manipulam Datas... 19

Funções de Linha que Realizam Conversões de Dados ... 19

Funções de Grupo... 20

Agrupamento de dados (cláusula Group By) ... 21

A cláusula Having ... 22

Ordem de Avaliação das cláusulas de um comando SELECT... 22

Selecionando dados de várias tabelas (Join)... 23

Subconsultas (Subqueries)... 23

Utilização... 23

Subconsultas correlacionadas... 24

(3)

Quantificador Existencial ... 25

Módulo 3 – Manipulação de dados... 27

O Comando INSERT... 27

O Comando UPDATE... 28

O Comando DELETE... 29

Controle de Transações ... 30

Vantagens do COMMIT ou ROLLBACK ... 30

Controlando Transações Através de SavePoints ... 30

O Estado dos dados antes do Commit ou Rollback... 31

O que Acontece após o Commit ... 31

O que acontece após o Rollback... 31

Rollback em nível de Comando... 31

Módulo 4 – Manipulação de tabelas... 32

Criação de tabelas... 32

Regras para nomear uma tabela... 32

Principais tipos de dados do Oracle... 32

Criando uma tabela... 32

Criando uma tabela a partir de uma subconsulta... 33

Alteração da estrutura de uma tabela... 33

Removendo uma tabela ... 34

Renomeando uma tabela... 34

Eliminando todas as linhas de uma tabela... 34

Módulo 5 – Definindo Restrições (Constraints)... 35

Tipos de Constraints ... 35

Constraints Primary Key e Not Null ... 35

Constraint Check e Foreign Key ... 35

Adicionando uma constraint a uma tabela... 36

Removendo uma constraint de uma tabela... 36

Outras Formas de se Validar uma Restrição de Integridade ... 37

Módulo 6 – Manipulação de outros objetos de banco de dados... 38

Visões (Views) ... 38

Vantagens na Utilização de Visões: ... 38

Criando uma Visão ... 38

Alterando uma visão... 38

Removendo uma Visão... 38

Índices... 39

Regras Gerais para a Criação de Índices ... 39

Criando Índices... 39

Reconstruindo Índices ... 40

Removendo Índices ... 40

(4)

Módulo 1 – Introdução a Sistemas de Banco de Dados

Conceito

A tecnologia aplicada aos métodos de armazenamento de informações vem crescendo e gerando um impacto cada vez maior no uso de computadores, em qualquer área em que os mesmos podem ser aplicados.

Um “banco de dados” pode ser definido como um conjunto de “dados”

devidamente relacionados. Por “dados” podemos compreender como “fatos conhecidos”

que podem ser armazenados e que possuem um significado implícito. Porém, o significado do termo “banco de dados” é mais restrito que simplesmente a definição dada acima. Um banco de dados possui as seguintes propriedades:

• Um banco de dados é uma coleção lógica coerente de dados com um significado inerente; uma disposição desordenada dos dados não pode ser referenciada como um banco de dados;

• Um banco de dados é projetado, construído e populado com dados para um propósito específico; um banco de dados possui um conjunto pré-definido de usuários e aplicações;

• Um banco de dados representa algum aspecto do mundo real, o qual é chamado de “mini-mundo”; qualquer alteração efetuada no mini-mundo é automaticamente refletida no banco de dados.

Um banco de dados pode ser criado e mantido por um conjunto de aplicações desenvolvidas especialmente para esta tarefa ou por um “Sistema Gerenciador de Banco de Dados” (SGBD). Um SGBD permite aos usuários criarem e manipularem bancos de dados de propósito geral. O conjunto formado por um banco de dados mais as aplicações que manipulam o mesmo é chamado de “Sistema de Banco de Dados”.

Comparação entre Banco de Dados e Processamento Tradicional de Arquivos

Auto Informação

Uma característica importante da abordagem Banco de Dados é que o servidor mantém não somente os dados mas também a forma como os mesmos são armazenados, contendo uma descrição completa do banco de dados. Estas informações são armazenadas no catálogo do SGBD, o qual contém informações como a estrutura de cada arquivo, o tipo e o formato de armazenamento de cada tipo de dado, restrições, etc.

A informação armazenada no catálogo é chamada de “Meta Dados”. No processamento tradicional de arquivos, o programa que irá manipular os dados deve conter este tipo de informação, ficando limitado a manipular as informações que o mesmo conhece.

Utilizando a abordagem banco de dados, a aplicação pode manipular diversas bases de dados diferentes.

(5)

Separação entre Programas e Dados

No processamento tradicional de arquivos, a estrutura dos dados está incorporada ao programa de acesso. Desta forma, qualquer alteração na estrutura de arquivos implica na alteração no código fonte de todos os programas. Já na abordagem banco de dados, a estrutura é alterada apenas no catálogo, não alterando os programas.

Figura 1. Um ambiente de Sistema de Banco de Dados.

Abstração de Dados

O SGBD deve fornecer ao usuário uma “representação conceitual” dos dados, sem fornecer muitos detalhes de como as informações são armazenadas. Um “modelo de dados” é uma abstração de dados que é utilizada para fornecer esta representação conceitual utilizando conceitos lógicos como objetos, suas propriedades e seus relacionamentos. A estrutura detalhada e a organização de cada arquivo são descritas no catálogo.

Múltiplas Visões de Dados

Como um conjunto de informações pode ser utilizada por um conjunto diferenciado de usuários, é importante que estes usuários possam ter “visões” diferentes da base de dados. Uma “visão” é definida como um subconjunto de uma base de dados, formando deste modo, um conjunto “virtual” de informações.

SGBD Programas de Aplicação/Consulta

Software para processar manipulação

Software de Acesso aos Dados

Meta Dados Dados

Sistema de Banco de Dados

(6)

Vantagens e desvantagens do uso de um SGBD

Controle de Redundância

No processamento tradicional de arquivos, cada grupo de usuários deve manter seu próprio conjunto de arquivos e dados. Desta forma, acaba ocorrendo redundâncias que prejudicam o sistema com problemas como:

• Toda vez que for necessário atualizar um arquivo de um grupo, então todos os grupos devem ser atualizados para manter a integridade dos dados no ambiente como um todo;

• A redundância desnecessária de dados levam ao armazenamento excessivo de informações, ocupando espaço que poderia estar sendo utilizado com outras informações.

Compartilhamento de Dados

Um SGBD multi-usuário deve permitir que múltiplos usuários acessem o banco de dados ao mesmo tempo. Este fator é essencial para que múltiplas aplicações integradas possam acessar o banco.

O SGBD multi-usuário deve manter o controle de concorrência para assegurar que o resultado de atualizações sejam corretos. Um banco de dados multi-usuário deve fornecer recursos para a construção de múltiplas visões.

Restrição a Acesso não Autorizado

Um SGBD deve fornece um subsistema de autorização e segurança, o qual é utilizado pelo DBA para criar “contas” e especificar as restrições destas contas; o controle de restrições se aplica tanto ao acesso aos dados quanto ao uso de softwares inerentes ao SGBD.

Representação de Relacionamentos Complexos entre Dados

Um banco de dados pode incluir uma variedade de dados que estão inter- relacionados de várias formas. Um SGBD deve fornecer recursos para se representar uma grande variedade de relacionamentos entre os dados, bem como, recuperar e atualizar os dados de maneira prática e eficiente.

Tolerância a Falhas

Um SGBD deve fornecer recursos para recuperação de falhas tanto de software quanto de hardware.

(7)

O Modelo Relacional de Dados

O Modelo Relacional representa os dados contidos em um Banco de Dados através de relações (tabelas). Estas tabelas contêm informações sobre as entidades representadas e seus relacionamentos. O Modelo Relacional é claramente baseado no conceito de matrizes, onde as chamadas linhas (das matrizes) seriam os registros e as colunas (das matrizes) seriam os campos.

O Modelo Relacional foi originalmente proposto em 1970 pelo Dr. E. F. Codd, pesquisador da IBM, no artigo “A Relational Model of Data for Large Shared Data Banks”.

O Modelo Relacional consiste de:

● Uma coleção de objetos ou relações que armazenam os dados;

● Um conjunto de operações que podem ser aplicadas nos dados para produzir outras relações;

● Um conjunto de regras para garantir a integridade e a consistência dos dados.

Terminologia do Modelo Relacional

Uma tabela é formada por colunas e linhas. As colunas definem os atributos da relação. As linhas definem uma ocorrência específica de uma relação. A interseção de uma linha com uma coluna é denominada de campo.

Toda tabela deve possuir uma chave primária (primary key) que é o atributo que identifica uma ocorrência da relação, isto é, um registro.

Podem existir também chaves secundárias que serão chaves que possibilitarão pesquisas ou ordenações alternativas, ou seja, diferentes da ordem criada a partir da chave primária.

Uma chave formada por apenas um atributo é denominada simples. Uma chave é dita composta quando contém mais de um atributo.

Uma chave estrangeira (foreign key), permite o relacionamento da tabela da qual a chave é atributo com outra tabela na qual ela é chave primária.

Modelagem de Dados utilizando o Modelo Entidade Relacionamento (MER)

O modelo Entidade-Relacionamento é um modelo de dados conceitual de alto nível, cujos conceitos foram projetados para estar o mais próximo possível da visão que o usuário tem dos dados, não se preocupando em representar como estes dados estarão realmente armazenados.

O modelo ER é utilizado principalmente durante o processo de projeto de banco de dados.

(8)

Entidades e Atributos

O objeto básico tratado pelo modelo ER é a “entidade”, que pode ser definida como um objeto do mundo real, concreto ou abstrato e que possui existência independente. Cada entidade possui um conjunto particular de propriedades que a descreve chamado “atributos”.

Um atributo pode ser dividido em diversas sub-partes com significado independente entre si, recebendo o nome de “atributo composto”. Um atributo que não pode ser subdividido é chamado de “atributo simples” ou “atômico”.

Um atributo é denominado de “atributo chave” quando seus valores podem ser utilizados para identificar cada entidade de forma única. Muitas vezes, uma chave pode ser formada pela composição de dois ou mais atributos. Uma entidade pode também ter mais de um atributo chave.

O atributo que pode assumir apenas um determinado valor é denominado

“atributo simplesmente valorado”, enquanto aquele que pode assumir diversos valores é chamado de “multivalorado”. Um atributo que é gerado a partir de outro atributo é chamado de “atributo derivado”. O conjunto de valores que podem ser designados para um atributo é denominado “domínio”.

Relacionamentos

Além de conhecer detalhadamente as entidades, é muito importante conhecer também os relacionamentos entre estas entidades.

Um “relacionamento” R entre n entidades E1, E2, ..., En, é um conjunto de associações entre entidades. Informalmente falando, cada instância de relacionamento r1 em R é uma associação de entidades, onde a associação inclui exatamente uma entidade de cada entidade participante no relacionamento. Isto significa que estas entidades estão relacionadas de alguma forma no mini-mundo.

A figura 2 mostra um exemplo entre duas entidades (empregado e departamento) e o relacionamento entre elas (trabalha para). Repare que para cada relacionamento, participam apenas uma entidade de cada, porém, uma entidade pode participar de mais de um relacionamento.

Figura 2: Exemplo de um Relacionamento

d3 d2 d1

e7 e6 e5 e4 e3 e2 e1 EMPREGADO

Trabalha Para

DEPARTAMENTO

(9)

Grau de um Relacionamento

O “grau” de um relacionamento é o número de entidades que participam do relacionamento. No exemplo da figura 2, temos um relacionamento binário.

O grau de um relacionamento é ilimitado, porém, a partir do grau 3 (ternário), a compreensão e a dificuldade de se desenvolver a relação corretamente se tornam extremamente complexas.

Restrições de Relacionamentos

Geralmente, os relacionamentos sofrem certas restrições que limitam as possíveis combinações das entidades participantes. Estas restrições são derivadas de restrições impostas pelo estado destas entidades no mini-mundo. Veja o exemplo da figura 3.

Figura 3 - Relacionamento EMPREGADO gerencia DEPARTAMENTO

No exemplo da figura 3, temos a seguinte situação: um empregado pode gerenciar apenas um departamento, enquanto que um departamento, pode ser gerenciado por apenas um empregado. A este tipo de restrição, nós chamamos cardinalidade.

A cardinalidade indica o número de relacionamentos dos quais uma entidade pode participar. A cardinalidade pode ser: 1:1, 1:N, M:N.

No exemplo da figura 3, a cardinalidade é 1:1, pois cada entidade EMPREGADO pode gerenciar apenas um departamento e um departamento pode ser gerenciado por apenas um empregado.

No exemplo da figura 2, no relacionamento EMPREGADO Trabalha Para DEPARTAMENTO, o relacionamento é 1:N, pois um empregado pode trabalhar em apenas um departamento, enquanto que um departamento pode possuir vários empregados. Na figura 4, temos um exemplo de um relacionamento com cardinalidade N:M.

d3 d2 d1

e7 e6 e5 e4 e3 e2 e1 EMPREGADO

Gerencia

DEPARTAMENTO

(10)

Figura 4 - Relacionamento N:M

No exemplo da figura 4, nós temos que um empregado pode trabalhar em vários projetos enquanto que um projeto pode ter vários empregados trabalhando.

Outra restrição muito importante é a participação. A participação define a existência de uma entidade através do relacionamento, podendo ser parcial ou total.

Veja o exemplo da figura 3. A participação do empregado é parcial pois nem todo empregado gerencia um departamento, porém a participação do departamento neste relacionamento é total pois todo departamento precisa ser gerenciado por um empregado. Desta forma, todas as linhas da entidade DEPARTAMENTO precisam participar do relacionamento, mas nem todas as linhas da entidade EMPREGADO precisam participar do relacionamento.

Já no exemplo da figura 4, ambas as participações são totais pois todo empregado precisa trabalhar em um departamento e todo departamento tem que ter empregados trabalhando nele.

Estas restrições são chamadas de restrições estruturais.

Algumas vezes, torna-se necessário armazenar um atributo no tipo relacionamento. Veja o exemplo da figura 3. Eu posso querer saber em que dia o empregado passou a gerenciar o departamento. É difícil estabelecer a qual tipo entidade pertence atributo, pois o mesmo é definido apenas pela existência do relacionamento.

Quando temos relacionamentos com cardinalidade 1:1, podemos colocar o atributo em uma das entidades, de preferência, em uma cujo tipo entidade tenha participação total. No caso, o atributo poderia ir para o tipo entidade departamento. Isto porque nem todo empregado participará do relacionamento.

Caso a cardinalidade seja 1:N, então podemos colocar o atributo no tipo entidade com participação N. Porém, se a cardinalidade for N:M, então o atributo deverá mesmo ficar no tipo relação.

Veja o exemplo da figura 4. Caso queiramos armazenar quantas horas cada empregado trabalhou em cada projeto, então este deverá ser um atributo do relacionamento.

Entidades Fracas

Algumas entidades podem não ter um atributo chave por si só. Isto implica que

p3 p2 p1 e4

e3 e2 e1 EMPREGADO

Trabalha Em

PROJETO

(11)

não poderemos distingui-las por que as combinações dos valores de seus atributos podem ser idênticas. Estas entidades são chamadas entidades fracas. As entidades deste tipo precisam estar relacionadas com uma entidade pertencente a uma entidade proprietária. Este relacionamento é chamado de relacionamento identificador. Veja o exemplo da figura 5.

Figura 5 - Relacionamento com uma Entidade Fraca (Dependente)

A entidade DEPENDENTE é uma entidade fraca pois não possui um método de identificar uma entidade única. O EMPREGADO não é uma entidade fraca pois possui um atributo para identificação (atributo chave). O número do RG de um empregado identifica um único empregado. Porém, um dependente de 5 anos de idade não possui necessariamente um documento de identificação.

Desta forma, DEPENDENTE é uma entidade fraca. Uma entidade fraca possui uma chave parcial que juntamente com a chave primária da entidade proprietária forma uma chave primária composta. Neste exemplo: a chave primária do EMPREGADO é o RG. A chave parcial do DEPENDENTE é o seu nome, pois dois irmãos não podem ter o mesmo nome. Desta forma, a chave primária desta entidade fica sendo o RG do pai ou mãe mais o nome do dependente.

Diagrama Entidade Relacionamento

O diagrama Entidade Relacionamento é composto por um conjunto de objetos gráficos que visa representar todos os objetos do modelo Entidade Relacionamento tais como entidades, atributos, atributos chaves, relacionamentos, restrições estruturais, etc.

O diagrama ER fornece uma visão lógica do banco de dados, fornecendo um conceito mais generalizado de como estão estruturados os dados de um sistema.

Os objetos que compõem o diagrama ER estão listados a seguir, na figura 6.

DEPENDENTE

p3 p2 e2 p1

e1 EMPREGADO

Possui Dependentes

(12)

Figura 6- Objetos que Compõem o Diagrama ER

SQL

A SQL (Structured Query Language), Linguagem Estruturada de Consulta, é a linguagem padrão para se lidar com bancos de dados relacionais e é aceita por quase todos os produtos existentes do mercado.

A linguagem SQL foi desenvolvida originalmente no laboratório de pesquisa da IBM no início da década de 1970 e foi implementada pela primeira vez em grande escala em um protótipo da IBM chamado System R, e reimplementada depois em numerosos produtos da IBM e de muitos outros fornecedores.

Em 1986, o American National Standard Institute ( ANSI ), publicou um padrão SQL. Desde então, A linguagem SQL tem se estabelecido como linguagem padrão de Banco de Dados Relacional.

A SQL apresenta uma categoria de comandos que permitem a definição dos

ENTIDADE

ENTIDADE FRACA

RELACIONAMENTO

RELACIONAMENTO IDENTIFICADOR

ATRIBUTO

ATRIBUTO CHAVE ATRIBUTO

MULTI VALORADO

ATRIBUTO COMPOSTO

ATRIBUTO DERIVADO

E1 E2 R

E1 1 N R E2

Participação Parcial de E1 em R, Participação Total de E2 em R Taxa de Cardinalidade 1:N

para E1:E2 em R

R (min, max) E1 Restrição Estrutural (min,max) na

Participação de E1 em R

(13)

dados, chamada de DDL (Data Definition Language), composta pelos comandos de criação, alteração e eliminação de objetos de banco de dados. Como exemplo de comandos da classe DDL, temos os comandos Create, Alter e Drop.

Os comandos da categoria DML (Data Manipulation Language) são destinados a consultas, inserções, exclusões e alterações em um ou mais registros de uma ou mais tabelas de maneira simultânea. Como exemplo de comandos DML, temos os comandos Select, Insert, Update e Delete.

Existe ainda a categoria DCL (Data Control Language) que dispõe de comandos de controle de privilégios como Grant e Revoke.

A Linguagem SQL tem como grandes virtudes sua capacidade de gerenciar índices, sem a necessidade de controle individualizado de índice corrente, algo muito comum nas linguagens de manipulação de dados do tipo registro a registro. Outra característica muito importante disponível em SQL é sua capacidade de construção de visões, que são formas de visualizarmos os dados na forma de listagens independente das tabelas e organização lógica dos dados.

Outra característica interessante na linguagem SQL é a capacidade que dispomos de cancelar uma série de atualizações ou de as gravarmos, depois de iniciarmos uma seqüência de atualizações. Os comandos Commit e Rollback são responsáveis por estas facilidades.

Devemos notar que a linguagem SQL consegue implementar estas soluções, somente pelo fato de estar baseada no modelo de Banco de Dados Relacional que garante por si mesmo a integridade das relações existentes entre as tabelas e seus índices.

(14)

Módulo 2 – Consultas SQL

O formato de um comando SQL é livre, podendo ocupar uma ou mais linhas.

Um comando SQL é composto de cláusulas, algumas opcionais e outras obrigatórias.

Para maior inteligibilidade é conveniente iniciar uma cláusula em uma linha, mas isso não é obrigatório. As palavras que compõem um comando devem ser separadas por espaços ou TABs.

Selecionando dados de tabelas

O comando SELECT é usado para selecionar dados nas tabelas, ou seja, efetuar consultas SQL no banco de dados.

A sintaxe mais simples de um comando SELECT é:

SELECT <colunas>

FROM <tabela>

WHERE <condição>;

Observação: Para conhecer a estrutura de uma tabela, utilizamos o comando:

DESCRIBE <tabela>;

Seleção de colunas específicas

Não é necessário selecionar todas as colunas de uma tabela. Basta nomear, na cláusula SELECT, as colunas desejadas.

Exemplo 1: Seleção de uma coluna de uma tabela (para todas as linhas).

SELECT TABLE_NAME

FROM USER_TABLES;

Exemplo 2: Seleção de duas colunas de uma tabela.

SELECT NUM_MATR, NOME FROM FUNCIONARIO;

Seleção de todas as colunas

Para selecionar todas as colunas de uma tabela, basta substituir os nomes das colunas por um asterisco (*).

Exemplo 3: Seleção de todas as colunas de uma tabela.

SELECT *

FROM COMARCA;

(15)

Seleção sem valores duplicados

Utilize a cláusula DISTINCT para suprimir linhas duplicadas.

Exemplo 4: Seleção de todas as ocorrências distintas de uma coluna.

SELECT DISTINCT COD_CARG FROM FUNCIONARIO;

Emprego de operadores aritméticos

Exemplo 5: Seleção de todos os funcionários, multiplicando-se a coluna SALARIO.

SELECT NOME, SALARIO * 12 FROM FUNCIONARIO;

Utilizando um alias para coluna

Exemplo 6: A mesma consulta, Exibindo o resultado da multiplicação com um título mais adequado.

SELECT NOME, SALARIO * 12 AS SALARIO_ANUAL FROM FUNCIONARIO;

Variação:

SELECT NOME, SALARIO * 12 “SALARIO_ANUAL”

FROM FUNCIONARIO;

Ordenando as linhas selecionadas

Exemplo 7: Seleção de todos os funcionários, ordenando-se pela coluna NOME, em ordem ascendente (padrão).

SELECT NOME, COD_CARG FROM FUNCIONARIO ORDER BY NOME;

Exemplo 8: Seleção de todos os funcionários, ordenando-se pela coluna NOME, em ordem descendente.

SELECT NOME, COD_CARG FROM FUNCIONARIO ORDER BY NOME DESC;

(16)

Observação: Na ordenação ascendente, os valores NULOS aparecem no final.

Exemplo 9: Ordenando pela posição da coluna no SELECT. No caso, a ordenação será feita pela coluna COD_CARG.

SELECT NOME, COD_CARG FROM FUNCIONARIO ORDER BY 2;

Restringindo as linhas selecionadas

Exemplo 10: Seleção de todos os funcionários segundo um critério de igualdade.

SELECT NUM_MATR, NOME FROM FUNCIONARIO

WHERE NUM_MATR=‘19813’;

Observações:

1. Usar aspas simples quando a comparação for com um literal;

2. Os literais são “Case Sensitive”, isto é, letras maiúsculas são diferentes de letras minúsculas;

3. O formato padrão para datas é DD-MON-YY.

Operadores para comparações lógicas

• = Igual

• <> Diferente

• > Maior que

• < Menor que

• >= Maior ou igual

• <= Menor ou igual

• BETWEEN ... AND ... Entre dois valores

• IN (Lista) Pertence a um conjunto

• LIKE Semelhante / contém a subcadeia

• IS NULL É nulo

Exemplo 11: Seleção de todos os funcionários segundo um critério de desigualdade.

SELECT NUM_MATR, NOME FROM FUNCIONARIO

WHERE COD_CARG <> 1;

Exemplo 12: Seleção de todos os funcionários segundo um critério de comparação.

(17)

SELECT NOME, SALARIO FROM FUNCIONARIO WHERE SALARIO > 1000;

Exemplo 13: Selecionando linhas com BETWEEN ... AND ...

SELECT NOME, DT_CAD FROM FUNCIONARIO

WHERE DT_CAD BETWEEN ‘28-SEP-90’ AND ‘30-JAN-91’;

Exemplo 14: Selecionando linhas com a cláusula IN. Seleção de Funcionários cujo COD_CARG seja igual a 1 ou 2.

SELECT NOME, COD_CARG

FROM FUNCIONARIO WHERE COD_CARG IN (1, 2);

Exemplo 15: Selecionando linhas com a cláusula LIKE. Seleção de Funcionários cujo NOME contenha a subcadeia ‘SILVA’.

SELECT NOME, SALARIO FROM FUNCIONARIO

WHERE NOME LIKE ‘%SILVA%’;

Exemplo 16: Selecionando linhas com a cláusula LIKE. Seleção de Funcionários cujo NOME não contenha a subcadeia ‘SILVA’.

SELECT NOME, SALARIO FROM FUNCIONARIO

WHERE NOME NOT LIKE ‘%SILVA%’;

Exemplo 17: Selecionando linhas com a cláusula LIKE. Seleção de Funcionários cujo NOME contenha uma letra ‘A’ na segunda posição.

SELECT NOME, SALARIO FROM FUNCIONARIO

WHERE NOME LIKE ‘_A%’;

Observações:

1. “%” representa nenhum ou muitos caracteres.

2. “_” representa um único caractere naquela posição especificada.

Exemplo 18: Selecionando linhas com a cláusula IS NULL. Funcionários cujo campo LOGIN seja nulo.

SELECT NUM_MATR, NOME FROM FUNCIONARIO

WHERE LOGIN IS NULL;

Observação: Se valores nulos forem comparados por operadores que não o

(18)

operador “IS NULL” o resultado será sempre falso. Na consulta a seguir, a condição da cláusula Where é sempre avaliada coma falsa pois um valor nulo não pode ser comparado com outros operadores de comparação, mesmo que este outro valor seja nulo.

Exemplo 19: Selecionando linhas sem a cláusula IS NULL. Não haverá linhas selecionadas.

SELECT NUM_MATR, NOME FROM FUNCIONARIO

WHERE COD_SERV = NULL;

Operadores lógicos

• AND E As duas condições devem ser verdadeiras

• OR OU Somente uma condição precisa ser verdadeira

• NOT NÃO Negação de uma condição lógica

Funções

Há dois tipos de funções: funções de linhas e funções de grupos. Uma função de linha retorna um resultado por linha da tabela acessada. Já uma função de grupo retorna um resultado por grupo de registros.

Funções de Linha

• LOWER (‘TJ’) - Converte para letras minúsculas Æ tj

• UPPER (‘tj’) - Converte para letras maiúsculas Æ TJ

• INITCAP (‘TRIBUNAL DE JUSTIÇA’) - Converte cada letra inicial para maiúsculas Æ Tribunal De Justiça

• CONCAT (‘JOSE’, ‘SILVA’) - Concatena duas cadeias Æ JOSESILVA

• SUBSTR (‘JOSE’, 1, 3) - Extrai uma subcadeia de uma cadeia Æ JOS

• LENGTH (‘UFF’) - Retorna o tamanho de um campo Æ 3

• NVL (SALARIO, 0) Æ Se SALARIO for NULO, retorna o valor especificado, no caso, zero.

• ROUND (78.731, 2) – Arredonda o número para o número de decimais especificado. Se o dígito for menor ou igual a 4 arredonda para baixo, Acima de 4 arredonda para cima Æ 78.73

• ROUND (78.731, 0) Æ 79

• TRUNC (78.731, 2) - Trunca o número, preservando sua parte inteira Æ 78.73

• TRUNC (78.731) Æ 78

• MOD (100, 30) - Resto da divisão do primeiro número pelo segundo Æ 10

(19)

Exemplo 20: Utilização da função UPPER.

SELECT NOME, SALARIO, COD_SERV FROM FUNCIONARIO

WHERE UPPER (NOME) = ‘JOAO’;

Exemplo 21: Utilização das funções SUBSTR e LENGTH.

SELECT NOME, LENGTH (NOME) FROM FUNCIONARIO

WHERE SUBSTR (NOME, 1, 3) = ‘MAR’;

Formato de Data

• As datas no Oracle são armazenadas em um formato numérico interno que representa o século, o ano, o mês, o dia, a hora, o minuto e os segundos.

• O formato padrão de exibição é DD-MON-YY.

Funções de Linha que manipulam Datas

• SYSDATE Retorna a data do sistema no formato DD-MMM-YY

• MONTHS_BETWEEN (‘10-JAN-97’, ‘10-JAN-98’) - número de meses decorrido entre duas datas Æ 12

• ADD_MONTHS (‘03-MAR-98’, -3) – Adiciona meses a uma data Æ 03- DEC-97

• NEXT_DAY (‘03-MAR-98’, ‘SATURDAY’) - Próximo dia da semana a partir de uma data Æ 07-MAR-98

Exemplo 22: Utilização da função SYSDATE.

SELECT *

FROM FUNCIONARIO

WHERE DT_CAD = SYSDATE;

Funções de Linha que Realizam Conversões de Dados

• TO_CHAR Æ Converte um número ou uma data para VARCHAR2. Um formato pode ser especificado.

• TO_NUMBER Æ Converte um string contendo dígitos para NUMBER

• TO_DATE Æ Converte um string representando uma data para DATE de acordo com o formato especificado. O formato padrão é DD-MON-YY.

Exemplo 23: Utilização da função TO_CHAR. Como exibir uma data no formato DD/MM/AA.

SELECT NOME, TO_CHAR(DT_CAD,‘DD/MM/YY’) ADMISSÃO FROM FUNCIONARIO;

(20)

Observações:

• Por padrão, a largura de uma coluna que resulta de uma expressão é de 80 posições.

• YYYY ou YYY ou YY ou Y Æ Os últimos 4, 3, 2 ou 1 dígitos do ano.

• MM Æ O mês representado em 2 dígitos.

• MON Æ O nome do mês abreviado em 3 letras, em inglês.

• DDD ou DD ou D Æ Dia do ano, mês ou semana.

• DAY Æ O nome do dia por extenso, completado com brancos até 9 caracteres.

• MM/AA Æ Apenas o mês e o ano são exibidos.

Exemplo 24: Utilização da função TO_CHAR para exibir o dia da semana.

SELECT NOME, TO_CHAR(DT_CAD,‘DAY’) ADMISSÃO FROM FUNCIONARIO;

Exemplo 25: Utilização da função TO_CHAR para exibir a data no formato completo.

SELECT NOME,

TO_CHAR(DT_ADMISSAO,‘DD-MM-YY HH24:MI:SS’)

ADMISSÃO

FROM FUNCIONARIO;

Funções de Grupo

• Funções de grupo operam com um conjunto de linhas para dar um resultado por grupo de linhas. Um conjunto de linhas pode ser uma tabela inteira ou linhas desta tabela divididas em grupos.

• Funções de grupo podem aparecer tanto na cláusula Select quanto na cláusula Having.

• A cláusula Group By divide as linhas de uma ou mais tabelas em grupos de linhas.

• A cláusula Having seleciona os grupos que serão aceitos.

Principais Funções de Grupo Existentes:

• AVG MÉDIA

• COUNT CONTAGEM

• MAX MÁXIMO

• MIN MÍNIMO

• SUM SOMA Observações:

• A cláusula Distinct pode ser utilizada para que sejam considerados apenas valores não duplicados.

(21)

• Todas as funções de grupo ignoram valores nulos. Para substituir um valor nulo por outro valor utilize a função NVL.

Exemplo 26: Utilização de funções de grupo, considerando todas as linhas de uma tabela um único grupo.

SELECT AVG(SALARIO), MAX(SALARIO), MIN(SALARIO), SUM(SALARIO) FROM FUNCIONARIO;

Exemplo 27: As funções de grupo aplicadas às linhas especificadas pela cláusula WHERE.

SELECT AVG(SALARIO), MAX(SALARIO), MIN(SALARIO), SUM(SALARIO) FROM FUNCIONARIO;

WHERE COD_CARG = 1;

Exemplo 28: Utilização da função COUNT para contar o número de funcionários lotados na serventia de número 226008.

SELECT COUNT(*) FROM FUNCIONARIO

WHERE COD_SERV = ‘226008’; Observações:

• COUNT(*) conta o número de linhas na tabela.

• COUNT(LOGIN) conta o número de linhas com sobrenome diferente de nulo.

• COUNT(NUM_MATR) conta o número de linhas na tabela uma vez que a coluna NUM_MATR é a chave primária da tabela e toda chave primária não pode conter valores nulos.

Agrupamento de dados (cláusula Group By)

Para efetuarmos agrupamentos de dados de acordo com os valores de certas colunas utilizamos a cláusula GROUP BY.

Exemplo 29: Utilização da cláusula GROUP BY e da função COUNT para se contar quantos funcionários estão lotados em cada serventia.

SELECT COD_SERV, COUNT(*) FROM FUNCIONARIO

GROUP BY COD_SERV;

(22)

Observações:

• Qualquer coluna incluída na cláusula SELECT, se não estiver em uma função de grupo, deverá constar da cláusula GROUP BY.

• Com a cláusula WHERE é possível excluir determinadas linhas dos grupos.

• Por padrão, as linhas são ordenadas ascendentemente conforme a lista de colunas especificada na cláusula GROUP BY. Para modificar este comportamento é preciso utilizar a cláusula ORDER BY.

Exemplo 30: Agrupamento dos processos por serventia e por código da ação, para saber quantos processos de que ações existem em cada serventia.

SELECT COD_SERV, COD_ACAO, COUNT(*) FROM PROCESSO

GROUP BY COD_SERV, COD_ACAO;

A cláusula Having

• Para se restringir à inclusão de grupos não se pode utilizar a cláusula WHERE.

• A cláusula WHERE deve ser utilizada para se restringir à inclusão de linhas em um grupo.

• Para se omitir a inclusão de grupos inteiros do resultado de uma query deve- se utilizar a cláusula HAVING.

Exemplo 31: A utilização da cláusula HAVING para listar o número das serventias seguido da média salarial de seus funcionários. No entanto, deseja-se listar apenas aquelas serventias cuja média salarial é superior a 2000.

SELECT COD_SERV, AVG(SALARIO) FROM FUNCIONARIO

GROUP BY COD_SERV

HAVING AVG(SALARIO) > 2000;

Ordem de Avaliação das cláusulas de um comando SELECT

1. Se o comando SQL contem a cláusula WHERE, o servidor seleciona as linhas candidatas.

2. O servidor identifica os grupos especificados pela cláusula GROUP BY.

3. A cláusula HAVING restringe os grupos resultantes que não estão de acordo com os critérios especificados nesta cláusula.

(23)

Selecionando dados de várias tabelas (Join)

• Para se exibir dados de mais de uma tabela, através de um comando SQL, é preciso definir condições de junção. (Joins)

• Os Joins geralmente ocorrem entre valores de chave primária e de chave estrangeira.

• Tipos de Joins:

o Equijoin Join por igualdade o Non-equijoin Join por desigualdade o Outer join Join incompleto

o Self Join Join efetuado com a própria tabela

• Um produto cartesiano geralmente ocorre quando a condição de junção é omitida ou inválida.

• Para evitar produtos cartesianos é preciso incluir, na cláusula Where, condições de junção válidas.

Exemplo 32: Uma junção simples entre a tabela de Funcionários e a tabela de Serventias (Equijoin).

SELECT FUNCIONARIO.NOME “FUNCIONARIO”, SERVENTIA.DESC_SERV “SERVENTIA”

FROM FUNCIONARIO, SERVENTIA

WHERE FUNCIONARIO.COD_SERV = SERVENTIA.COD_SERV;

Observação: Haverá um ganho de desempenho e de clareza se você sempre qualificar as colunas com o nome das tabelas às quais elas pertencem.

Exemplo 33: Uma junção entre a tabela Funcionário, a tabela Serventia e a tabela Comarca. As tabelas estão sendo referenciadas por um apelido, no caso, uma letra.

SELECT F.NOME “FUNCIONARIO”,

S.DESC_SERV “SERVENTIA”, C.COMARCA “COMARCA”

FROM FUNCIONARIO F, SERVENTIA S, COMARCA C WHERE F.COD_SERV = S.COD_SERV

AND S.COD_COMA = C.COD_COMA;

Subconsultas (Subqueries)

Uma subconsulta é um comando SELECT embutido em uma cláusula de outro comando SQL.

Utilização

• Escreva subconsultas para recuperar dados baseados em critérios desconhecidos.

(24)

• Pode ser muito útil quando se necessita selecionar linhas de uma tabela com uma condição que depende dos dados que estão na própria ou em outra tabela.

• Subconsultas não podem conter a cláusula ORDER BY.

• Duas classes de operadores de comparações são utilizadas em subconsultas:

o Operadores de uma única linha: >, =, >=, <, <=, <>

o Operadores de múltiplas linhas: IN e NOT IN.

• Como uma subconsulta é processada?

o Primeiramente é executado o comando SELECT aninhado.

o Em seguida o resultado é utilizado em uma condição da consulta principal.

Exemplo 34: A utilização de uma subconsulta aninhada para recuperar o nome e salário de todos os funcionários que trabalham na mesma serventia que o JOSE DA SILVA trabalha.

SELECT NOME, SALARIO FROM FUNCIONARIO

WHERE COD_SERV =

(SELECT COD_SERV FROM FUNCIONARIO

WHERE NOME = ‘JOSE DA SILVA’);

Subconsultas correlacionadas

No exemplo abaixo a subconsulta é executada uma vez para cada linha da tabela de funcionários na consulta mais externa.

Exemplo 35: A utilização de uma subconsulta correlacionada para recuperar o nome dos funcionários que trabalham na serventia 226008.

SELECT NOME

FROM FUNCIONARIO F WHERE ‘226008’ IN

(SELECT COD_SERV FROM FUNCIONARIO

WHERE NUM_MATR = F.NUM_MATR);

Exemplo 36: A utilização de uma subconsulta correlacionada para recuperar o nome e a matrícula dos funcionários que tem o mesmo cargo de um determinado funcionário.

SELECT NOME, NUM_MATR FROM FUNCIONARIO

WHERE COD_CARG IN

(SELECT COD_CARG FROM FUNCIONARIO

WHERE NUM_MATR=’19813’);

(25)

Quantificador Existencial

• Exists representa o quantificador existencial, uma noção emprestada da lógica formal.

• Em SQL, um predicado existencialmente quantificado é representado pela expressão da forma EXISTS (SELECT * FROM...).

• Essa expressão será verdadeira se o resultado do cálculo da subconsulta representado por “SELECT * FROM ...” não estiver vazio, isto é, se existir pelo menos um registro na tabela FROM da subconsulta que satisfaz a condição WHERE dessa mesma subconsulta.

• Qualquer consulta que utilize IN pode alternativamente ser formulada com EXISTS, porém o inverso não é verdadeiro.

Exemplo 37: Obter o nome das serventias que possuem funcionários.

SELECT NOME

FROM SERVENTIA S WHERE EXISTS

(SELECT *

FROM FUNCIONARIO

WHERE COD_SERV = S.COD_SERV);

A subconsulta anterior poderia ser substituída pelo seguinte join:

SELECT DISTINCT DESC_SERV

FROM SERVENTIA S, FUNCIONARIO F WHERE S.COD_SERV = F.COD_SERV;

SELECT *

FROM FUNCIONARIO

(26)

WHERE SALARIO > 260 UNION

SELECT *

FROM FUNCIONARIO WHERE NOME ='JOAO';

Exemplo 40: Obter os funcionários com SALARIO > 260 e os funcionários com nome igual a ‘JOAO’. União com repetição.

SELECT *

FROM FUNCIONARIO WHERE SALARIO > 260 UNION ALL

SELECT *

FROM FUNCIONARIO WHERE NOME ='JOAO';

(27)

Módulo 3 – Manipulação de dados

Comandos Descrição

INSERT Adiciona novas linhas a uma tabela.

UPDATE Modifica linhas existentes em uma tabela.

DELETE Remove linhas existentes de uma tabela.

COMMIT Torna permanentes todas as modificações pendentes.

SAVEPOINT Cria uma marca de recuperação para o comando ROLLBACK.

ROLLBACK Reverte todas as manipulações efetuadas.

O Comando INSERT

O comando INSERT é usado para inserir registros nas tabelas do banco de dados.

A sintaxe de um comando INSERT é:

INSERT INTO <tabela>

(<colunas>) VALUES (<valores>)

Exemplo 41: Quando são informados os dados de todas as colunas e na ordem padrão, não é necessário informar o nome das colunas.

INSERT INTO SERVENTIA

VALUES (‘226008’, ‘2A. VARA CRIMINAL’, ‘0054’);

Exemplo 42: Os nomes das colunas foram informados desnecessariamente.

INSERT INTO SERVENTIA

(COD_SERV, DESC_SERV, COD_COMA)

VALUES (‘226008’, ‘2a. Vara Criminal’, ‘0054’);

Exemplo 43: Os nomes das colunas precisaram ser informados uma vez que os dados não estão na ordem padrão.

INSERT INTO SERVENTIA

(DESC_SERV, COD_COMA, COD_SERV,)

VALUES (‘2a. Vara Criminal’, ‘0054’, ‘226008’);

(28)

O Comando UPDATE

O comando UPDATE é usado para atualizar registros nas tabelas do banco de dados, segundo um critério especificado na cláusula WHERE.

A sintaxe de um comando UPDATE é:

UPDATE <tabela>

SET <coluna1> = <valor1>, ..., <colunaN> = <valorN>

WHERE <condição>;

Exemplo 44: Atualização da coluna NOME a partir de um critério especificado.

UPDATE FUNCIONARIO

SET NOME = ‘JAMES BOND’

WHERE NUM_MATR = ‘01/17007’;

Exemplo 45: Atualização da coluna NOME de todos os funcionários em que esta coluna não esteja em letras maiúsculas.

UPDATE FUNCIONARIO

SET NOME = UPPER(NOME) WHERE NOME <> UPPER(NOME);

Exemplo 46: Atualização de duas colunas de um funcionário especificado.

UPDATE FUNCIONARIO

SET NOME = ‘JOAQUIM’, SALARIO = 1000 WHERE NUM_MATR = ‘01/21956’;

Exemplo 47: Utilização com subconsulta. Atualizar em 50% o salário de todos os funcionários que trabalham na DGTEC.

UPDATE FUNCIONARIO

SET SALARIO = SALARIO * 1.50 WHERE COD_SERV IN

(SELECT COD_SERV FROM SERVENTIA

WHERE DESC_SERV = ‘DGTEC’);

Exemplo 48: Quando a cláusula WHERE não é especificada, todas as linhas são atualizadas.

UPDATE FUNCIONARIO

SET SALARIO = SALARIO * 1.15;

(29)

O Comando DELETE

O comando DELETE é usado para excluir registros das tabelas do banco de dados, segundo um critério especificado na cláusula WHERE.

A sintaxe de um comando DELETE é:

DELETE FROM <tabela>

WHERE <condição>;

Exemplo 49: Apagar o funcionário com número de matrícula 01/17007.

DELETE FROM FUNCIONARIO

WHERE NUM_MATR = ‘01/17007’;

Exemplo 50: Apagar todos os funcionários admitidos em janeiro de 2001.

DELETE FROM FUNCIONARIO

WHERE TO_CHAR (DT_CAD,‘MM-YY’) = ‘01-01’;

Exemplo 51: Apagar todos as linhas da tabela FUNCIONARIO. Utilize o comando SELECT COUNT(*) FROM FUNCIONARIO antes e depois do DELETE, para verificar a exclusão dos registros.

SELECT COUNT(*) FROM FUNCIONARIO;

DELETE FROM FUNCIONARIO;

SELECT COUNT(*) FROM FUNCIONARIO;

Utilize agora o comando ROLLBACK:

ROLLBACK;

Observe que o DELETE foi revertido e a tabela voltou a possuir o mesmo número de registros.

SELECT COUNT(*) FROM FUNCIONARIO;

Exemplo 52: Apagar todos as linhas da tabela FUNCIONARIO. Confirmar a operação com o comando COMMIT.

DELETE FROM FUNCIONARIO;

COMMIT;

SELECT COUNT(*) FROM FUNCIONARIO;

(30)

Controle de Transações

Uma transação começa quando o primeiro comando SQL é executado e termina quando ocorre um dos seguintes eventos:

• Execução de um comando COMMIT ou ROLLBACK

• Execução de um comando DDL ou DCL (ocorre um COMMIT automático Antes e Depois da execução destes comandos. Logo, mesmo que o comando DDL ou DCL falhe, não será possível dar ROLLBACK nos comandos que antecederam este comando.).

• Ocorrência de certos tipos de erro como, por exemplo, um deadlock (*) (ocorre um ROLLBACK).

• Término de uma sessão (se o usuário sai normalmente do SQL*PLUS ocorre um COMMIT)

• Falha na estação (por exemplo, quando se desliga a máquina estando conectado pelo SQL*PLUS) ou no servidor.

Quando uma transação termina, o próximo comando SQL inicia automaticamente a próxima transação.

Uma transação contém:

• Um ou mais comandos DML

• Um comando DDL

• m comando DCL

Observação: (*) Um deadlock (“travamento fatal”) é uma situação crítica que ocorre quando dois processos bloqueiam-se mutuamente, cada um deles aguardando que o outro libere um determinado recurso (por exemplo, o acesso exclusivo a uma linha de uma tabela). Como os dois processos já estão bloqueados, eles permanecem nesta situação indefinidamente. O servidor Oracle previne a ocorrência de deadlock, fazendo o ROLLBACK da transação de um processo que bloquearia um outro e levaria os dois a um deadlock.

Vantagens do COMMIT ou ROLLBACK

• Garante a consistência dos dados agrupando operações logicamente relacionadas.

• Agrupa os comandos de manipulação logicamente relacionados.

• Permite que se veja o resultado de manipulações antes que elas sejam confirmadas.

Controlando Transações Através de SavePoints

INSERT INTO FUNCIONARIO

VALUES (‘01/17007’, 'JAMES BOND', 'TECNICO I', '226008', 260, '08-SEP-93', ’31-MAR-64’);

(31)

INSERT INTO FUNCIONARIO

VALUES (‘01/00001’, 'EDSON ARANTES', 'ESCRIVAO', '226008', 3000, '02-JAN-73', ’13-SEP-45’);

SAVEPOINT ABC;

UPDATE FUNCIONARIO SET SALARIO = SALARIO * 1.15;

IF (CONDIÇÃO) THEN ROLLBACK TO ABC;

ELSE COMMIT;

END IF;

Observação: Se um único comando DML falha durante a execução de uma transação apenas este comando sofre ROLLBACK. Quanto às demais modificações caberá ao usuário dar COMMIT ou ROLLBACK explicitamente.

O Estado dos dados antes do Commit ou Rollback

• Antes do commit os dados são vistos por outros usuários como se não tivessem sofrido nenhuma manipulação.

• As linhas afetadas são bloqueadas (locked). Outros usuários não podem modificar os dados manipulados uma vez que estes dados encontram-se bloqueados.

O que Acontece após o Commit

• As manipulações efetuadas nos dados são escritas no banco.

• Os dados anteriores são permanentemente perdidos.

• Todos os usuários autorizados podem ver os resultados.

• Todas as linhas bloqueadas são liberadas.

• Todos os Savepoints são apagados.

O que acontece após o Rollback

• As manipulações efetuadas nos dados são desfeitas.

• As linhas bloqueadas são liberadas.

Rollback em nível de Comando

• Se um único comando DML falha durante sua execução, somente este comando sofre Rollback.

• Todas as demais modificações são provisoriamente mantidas até que um comando Commit ou Rollback seja executado.

(32)

Módulo 4 – Manipulação de tabelas

Criação de tabelas

Uma tabela pode ser criada a qualquer momento, desde que haja espaço em disco. Quando uma tabela é criada, a sua definição é armazenada no Dicionário de Dados .

A estrutura de uma tabela pode ser modificada a qualquer momento, sem a necessidade de se tirar o banco do ar. Não é necessário especificar o tamanho de uma tabela no momento da sua criação, embora isto seja possível.

Para criar tabelas é preciso ter o privilégio de CREATE TABLE e o direito de utilizar algum espaço em disco alocado para o banco de dados. Estes direitos são concedidos para os usuários pelo Administrador de Banco de Dados (DBA).

Regras para nomear uma tabela

• O nome de uma tabela deve começar por uma letra.

• Pode ter até 30 caracteres.

• Deve conter apenas: A-Z, a-z, 0-9, _, $ e #.

• Não pode ter o mesmo nome de qualquer outro objeto existente no esquema do usuário.

Principais tipos de dados do Oracle

NUMBER Número ponto flutuante com precisão de 38 dígitos significativos NUMBER(p,s) Valor numérico com um número máximo de dígitos igual a p, e com s

posições decimais.

DATE Data e Hora

CHAR(s) String de caracteres de tamanho fixo igual a s. O valor de s pode variar de 1 a 255.

VARCHAR2(s) String de caracteres de tamanho variável. Tamanho máximo igual a s.

O valor de s pode variar de 1 a 4000.

Criando uma tabela

A sintaxe mais simples do comando SQL para criação de uma tabela é:

CREATE TABLE <nome_tabela>

(<coluna><tipo><tamanho> [NOT NULL]

[{,<coluna><tipo><tamanho>[NOT NULL]}...]) ;

(33)

Exemplo 53: Criação da tabela FUNCIONARIO.

CREATE TABLE FUNCIONARIO

(NUM_MATR VARCHAR2(10), NOME VARCHAR2(30), SALARIO NUMBER(9,2), DT_CAD DATE,

DT_NASC DATE,

COD_CARG NUMBER(3), COD_SERV VARCHAR2(6));

Exemplo 54: Criação da tabela PROCESSO.

CREATE TABLE PROCESSO

(COD_PROC VARCHAR2(19) PRIMARY KEY, COD_SERV VARCHAR2(6),

COD_ACAO NUMBER(4), COD_ADV VARCHAR2(6), VALOR_CAUSA NUMBER(15,2), DATA_CAD DATE);

Criando uma tabela a partir de uma subconsulta

É possível criar uma nova tabela a partir de uma consulta SQL, utilizando-se a cláusula AS <subconsulta> no comando CREATE TABLE.

Exemplo 55: Criação uma cópia da tabela de Processo, utilizando subconsulta.

CREATE TABLE PROCESSO2

AS SELECT * FROM PROCESSO;

Exemplo 56: Criação uma cópia da tabela de Processo, somente para algumas colunas, utilizando subconsulta.

CREATE TABLE PROCESSO3

AS SELECT (COD_PROC, COD_SERV, COD_ACAO) FROM PROCESSO;

Alteração da estrutura de uma tabela

A estrutura de uma tabela pode ser alterada com o comando ALTER TABLE que permite também:

• Adicionar e modificar colunas de uma tabela.

• Adicionar e remover constraints.

• Habilitar e desabilitar constraints.

(34)

Exemplo 57: Adicionando duas colunas a tabela Processo.

ALTER TABLE PROCESSO

ADD (IND_SEGR_JUST VARCHAR2(1), SIT_PROC VARCHAR2(2));

Exemplo 58: Modificando o tamanho de uma coluna na tabela Funcionário.

ALTER TABLE FUNCIONARIO

MODIFY (NOME VARCHAR2 (40));

Removendo uma tabela

Uma tabela pode ser removida com o comando DROP TABLE. Tanto as linhas como a estrutura da tabela são definitivamente eliminadas.

Exemplo 59: Removendo a tabela Funcionário.

DROP TABLE FUNCIONARIO;

O comando DROP TABLE não pode ser revertido por um ROLLBACK. Por se tratar de um comando DDL, quando executado, ele causa um COMMIT automático e deve ser utilizado com extrema cautela.

Renomeando uma tabela

Para modificar o nome de uma tabela, utilize o comando RENAME.

RENAME <nome_tabela> TO <novo_nome>;

Eliminando todas as linhas de uma tabela

Para eliminar todas as linhas de uma tabela numa única operação, utilize o comando TRUNCATE TABLE. Somente os dados são eliminados, mantendo-se a estrutura da tabela preservada.

TRUNCATE <nome_tabela>;

Por se tratar de um comando DDL, este comando não permite ROLLBACK.

Quando executado, ele causa um COMMIT automático e deve ser utilizado com extrema cautela.

(35)

Módulo 5 – Definindo Restrições (Constraints)

É possível definir restrições de integridade para os campos de uma tabela. Estas restrições são denominadas de constraints.

Tipos de Constraints

PRIMARY KEY Chave primária FOREIGN KEY Chave estrangeira NOT NULL Nulo não permitido

UNIQUE Unicidade

CHECK Verificação de uma condição Observações:

• É possível criar uma constraint após a criação da tabela.

• Uma constraint pode ser definida em nível de coluna ou em nível de tabela.

• Constraints são armazenadas no Dicionário de Dados e podem ser facilmente recuperadas se possuírem nomes razoáveis.

Constraints Primary Key e Not Null

Exemplo 60: Emprego de Constraints Primary Key e Not Null.

CREATE TABLE FUNCIONARIO

(NUM_MATR VARCHAR2(10) PRIMARY KEY, NOME VARCHAR2(30) NOT NULL,

COD_CARG VARCHAR2(20),

COD_SERV VARCHAR2(6),

SALARIO NUMBER(9,2),

DT_CAD DATE,

DT_NASC DATE);

Constraint Check e Foreign Key

Exemplo 61: Constraint Check e Integridade Referencial com a própria tabela de Funcionários e com a tabela de Serventias.

CREATE TABLE FUNCIONARIO

(NUM_MATR VARCHAR2(10) PRIMARY KEY, NOME VARCHAR2(30) NOT NULL, CARGO VARCHAR2(20)

CONSTRAINT FUNCIONARIO_CARGO_CK (CARGO IN (‘TECNICO I’,‘ESCRIVAO’, ‘JUIZ’,

(36)

‘OFICIAL DE JUSTICA’,

‘TECNICO II’)),

COD_SERV VARCHAR2(6)

CONSTRAINT PROCESSO_COD_SERV_FK REFERENCES SERVENTIA (COD_SERV), SALARIO NUMBER(9,2),

DT_CAD DATE, DT_NASC DATE);

Adicionando uma constraint a uma tabela.

Exemplo 62: Adicionando uma constraint a uma tabela.

ALTER TABLE PROCESSO

ADD CONSTRAINT PROCESSO_COD_SERV_FK FOREIGN KEY (COD_SERV)

REFERENCES SERVENTIA (COD_SERV);

Exemplo 63: Como remover uma constraint de primary key forçando a remoção da respectiva constraint de foreign key.

Removendo uma constraint de uma tabela.

ALTER TABLE SERVENTIA

DROP CONSTRAINT SERVENTIA_COD_SERV_PK CASCADE;

Forma alternativa:

ALTER TABLE SERVENTIA

DROP PRIMARY KEY CASCADE;

Exemplo 64: Como remover uma tabela e as constraints que dela dependam.

DROP TABLE SERVENTIA CASCADE CONSTRAINTS;

Observações sobre a Constraint Primary Key:

• A constraint Primary Key é uma combinação das constraints Unique e Not Null.

• Um índice único é automaticamente criado.

Observações sobre a Constraint Unique:

• Designa uma coluna ou uma combinação de colunas de tal forma que duas linhas não possam ter o mesmo valor.

(37)

• Valores nulos são aceitos.

• Automaticamente é criado um índice único para a(s) coluna(s) especificada(s).

Observações sobre a Constraint Foreign Key:

• Estabelece um relacionamento com a chave primária ou única da mesma ou de outra tabela.

• Deve referenciar um valor existente na tabela pai ou ser nulo.

• Chaves estrangeiras são baseadas em dados e são puramente lógicas, isto é, não são ponteiros físicos.

• Uma chave estrangeira, parte de uma chave primária, não pode ser nula, pois uma chave primária não pode ser nula, nem parcialmente nula.

• Havendo a cláusula ON DELETE CASCADE, uma exclusão na tabela pai causa a exclusão das linhas relacionadas na tabela filho.

Outras Formas de se Validar uma Restrição de Integridade

• Triggers1

• Procedimentos ou funções armazenados no servidor de banco de dados

• Através do código na própria aplicação.

Observação: (1) Triggers (gatilhos) são procedimentos SQL que são executados automaticamente quando da ocorrência de um determinado evento no banco de dados (como, por exemplo, uma exclusão de um registro de uma tabela).

(38)

Módulo 6 – Manipulação de outros objetos de banco de dados

Visões (Views)

Uma visão (view) é uma tabela virtual baseada em tabelas base ou em outras visões. Por ser uma tabela virtual, a visão existe somente como uma definição no Dicionário de Dados, não ocupando espaço no schema do usuário.

Vantagens na Utilização de Visões:

• Restringem o acesso aos dados (linhas e colunas);

• Fazem com os usuários tenham que implementar consultas mais simples.

• Pode-se utilizar uma visão para gerar um dado derivado, inexistente na tabela base.

• Provêem independência de dados. Uma visão pode ser utilizada para recuperar dados de mais de uma tabela.

• Podem ser utilizadas na definição de grupos de usuários com direitos de acesso comuns. Cada grupo de usuários teria acesso a determinadas visões.

Criando uma Visão

A sintaxe do comando SQL para criação de uma visão é:

CREATE [OR REPLACE] VIEW <nome_visão > AS subconsulta);

Exemplo 65: Criação de uma visão sobre a tabela de funcionários

CREATE VIEW V_FUNC AS SELECT NOME, DT_CAD FROM FUNCIONARIO;

Alterando uma visão

Exemplo 66: Alteração de uma visão sobre a tabela de funcionários, adicionando a coluna NUM_MATR.

CREATE OR REPLACE VIEW V_FUNC AS SELECT NUM_MATR, NOME, DT_CAD FROM FUNCIONARIO;

Removendo uma Visão

Exemplo 67: Removendo uma visão.

DROP VIEW V_FUNC;

(39)

Índices

Uma estrutura de índice garante que o acesso a qualquer linha de uma tabela leve aproximadamente o mesmo tempo independentemente da posição da linha na tabela. Para implementar este mecanismo, o servidor Oracle utiliza, por padrão, uma estrutura de árvore balanceada (B*Tree).

Cada linha de uma tabela possui um número identificador denominado ROWID que é uma composição do número do arquivo, do bloco e da linha onde se encontra a linha da tabela desejada. O índice é construído utilizando-se a coluna e o ROWID da linha da tabela indexada.

Uma vez que um índice é criado para uma tabela, a sua utilização é automática pelo servidor de banco de dados, de acordo com o comando SQL executado.

Regras Gerais para a Criação de Índices É adequada a criação de índices quando:

• A coluna é freqüentemente utilizada na cláusula Where das consultas SQL.

• A coluna possui um grande número de valores diferentes.

• A coluna possui muitos valores nulos.

• A tabela é grande e a maioria das consultas recupera em torno de 2 a 4% das linhas.

A criação de índices não é adequada quando:

• A tabela é pequena.

• As colunas não são utilizadas como condições em consultas.

• A maioria das consultas recupera mais de 2 a 4% das linhas.

• A tabela é atualizada freqüentemente.

Atenção: Uma maior quantidade de índices nem sempre aumenta o desempenho do banco.

Criando Índices

Um índice é criado automaticamente quando se define uma constraint:

• PRIMARY KEY ou

• UNIQUE.

Manualmente, um índice pode ser criado com o comando CREATE INDEX.

Exemplo 68: Como criar um índice único.

CREATE UNIQUE INDEX SERVENTIA_DESC_IDX ON SERVENTIA (DESC_SERV);

(40)

Exemplo 69: Como criar um índice não único.

CREATE INDEX SERVENTIA_DESC_IDX ON SERVENTIA (DESC_SERV);

Reconstruindo Índices

Em tabelas que sofrem muitas inserções e exclusões de registros, o índice torna- se fragmentado, conseqüentemente dimuindo-se a performance do acesso aos dados.

Para reconstruir um índice, utiliza-se o comando ALTER INDEX com a cláusula REBUILD. Durante a reconstrução do índice, o acesso à tabela através deste recurso fica indisponível até o final da operação. A cláusula ONLINE permite a utilização do índice pela tabela mesmo durante o processo de reconstrução, que pode ser demorado dependendo do tamanho da tabela.

Exemplo 70: Reconstrução do índice SERVENTIA_DESC_IDX.

ALTER INDEX SERVENTIA_DESC_IDX REBUILD ONLINE;

Removendo Índices

Exemplo 71: Removendo o índice SERVENTIA_DESC_IDX.

DROP INDEX SERVENTIA_DESC_IDX;

Caso o índice seja utilizado para garantir uma chave primária ou coluna com constraint UNIQUE ocorrerá o seguinte erro: “ORA-02429: cannot drop index used for enforcement of unique/primary key”.

Neste caso, para remover o índice é preciso remover também a constraint correspondente.

Exemplo 72: Removendo o índice e a constraint correspondente.

ALTER TABLE SERVENTIA

DROP CONSTRAINT PK_SERVENTIA CASCADE;

Referências

Documentos relacionados

F REQUÊNCIAS PRÓPRIAS E MODOS DE VIBRAÇÃO ( MÉTODO ANALÍTICO ) ... O RIENTAÇÃO PELAS EQUAÇÕES DE PROPAGAÇÃO DE VIBRAÇÕES ... P REVISÃO DOS VALORES MÁXIMOS DE PPV ...

A proposta do Plano de Ação Educacional indicou ações que poderão ser executadas, no sentido de favorecer as escolas inseridas na referida região através de um treinamento que

O fortalecimento da escola pública requer a criação de uma cultura de participação para todos os seus segmentos, e a melhoria das condições efetivas para

[r]

Ressalta-se que mesmo que haja uma padronização (determinada por lei) e unidades com estrutura física ideal (física, material e humana), com base nos resultados da

Neste capítulo foram descritas: a composição e a abrangência da Rede Estadual de Ensino do Estado do Rio de Janeiro; o Programa Estadual de Educação e em especial as

Por fim, na terceira parte, o artigo se propõe a apresentar uma perspectiva para o ensino de agroecologia, com aporte no marco teórico e epistemológico da abordagem

A realização desta dissertação tem como principal objectivo o melhoramento de um sistema protótipo já existente utilizando para isso tecnologia de reconhecimento