Oracle SQL – Capitulo9

DO BÁSICO AO AVANÇADO PARA
MANIPULAÇÃO E OTIMIZAÇÃO DE DADOS
Fábio Roberto Octaviano
Utilizando DDL para Gerenciar Tabelas
Após o término do Capítulo:





Categorizar os principais objetos de um BD.
Verificar a estrutura de uma tabela.
Listar os tipos de dados existentes.
Criação de constraints em tempo de criação
de tabelas.
Descrever como funcionam os esquemas de
objetos.
Objeto
Descrição
Tabela
Unidade de armazenamento básica.
Composta por linhas.
Visão
Representa logicamente um subconjunto
de dados de uma ou mais tabelas.
Sequence
Gera valores numéricos.
Índice
Aumenta a performance de queries.
Sinônimo
Dá nomes alternativos a objetos.

Nomes de tabelas e colunas:
◦ Precisam começar com uma letra.
◦ Tamanho de 1 a 30 caracteres.
◦ Devem conter apenas os caracteres:
◦ A–Z, a–z, 0–9, _, $ e #
◦ Não podem ter o mesmo nome de outro
objeto do mesmo usuário.
◦ Não pode ser um nome reservado do Oracle.
◦ Você precisa possuir:
 Privilégio CREATE_TABLE.
 Uma área de storage.
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, ...]);
◦ Você especifica:
 O nome da tabela
 Nome da coluna, tipo de dado da coluna e seu
tamanho.
◦ Tabelas pertencendo a outros usuários não estão
no esquema do seu usuário.
◦ É preciso especificar o nome do dono do obejeto
como prefixo.
USERA
USERB
SELECT *
FROM userB.employees;
SELECT *
FROM userA.employees;




Especifica o valor padrão de uma coluna durante
uma instrução Insert.
Literais, expressões ou funções SQL são válidas.
Outro nome de coluna não é aceito.
O valor default especificado deve ser do mesmo
tipo da coluna.
CREATE TABLE hire_dates
(id
NUMBER(8),
hire_date DATE DEFAULT SYSDATE);
Table created.
◦ Crie a tabela DEPT:
CREATE
◦ : TABLE dept
(deptno
dname
loc
create_date
Table created.
NUMBER(2),
VARCHAR2(14),
VARCHAR2(13),
DATE DEFAULT SYSDATE);
◦ Confirme sua criação:
DESCRIBE dept
Data Type
Description
VARCHAR2(size)
Dado de caracteres de tamanho variável (1 a 4000)
CHAR(size)
Dado de caracteres de tamanho fixo (1 a 2000)
NUMBER(p,s)
Dado numérico de tamanho variável, onde p é a precisão (1
a 38) e s a escala (-84 a 127)
DATE
Valores de data e hora (01/01/4712 AC a 31/12/9999 DC)
LONG
Dado de caracteres de tamanho variável (até 2 Gb)
CLOB
Dados de caracteres (up to 4 GB)
RAW and LONG RAW
Dado binário (até 2 Gb)
BLOB
Dado binário (até 4 GB)
BFILE
Dado binário armazenado em arquivo externo (até 4 GB)
ROWID
Um sistema numérico base-64 representando o endereço
único de uma linha em sua tabela.


Problemas com LONG:
◦ Uma coluna LONG não é copiada quando uma
tabela é criada utilizando subqueries.
◦ Uma coluna LONG não pode ser incluída em
GROUP BY ou ORDER BY.
◦ Apenas uma coluna LONG pode ser utilizada por
tabela.
◦ Nenhuma constraint pode ser definida sobre uma
coluna do tipo LONG.
DICA: Utilize sempre CLOB ao invés de LONG!

É possível utilizar tipos de dados DATETIME.
Tipo de Dado
Descrição
TIMESTAMP
Data com segundos fracionados
INTERVAL YEAR TO
MONTH
Armazenado como um intervalo de
anos e meses
INTERVAL DAY TO
SECOND
Armazenado como um intervalo de
dias, horas, minutos e segundos

O tipo de dado INTERVAL YEAR TO MONTH
armazena um período de tempo utilizando os
campos YEAR e MONTH:
INTERVAL YEAR [(year_precision)] TO MONTH

O tipo de dado INTERVAL DAY TO SECOND
armazena um período de tempo em termos de
dias, horas, minutos e segundos:
INTERVAL DAY [(day_precision)]
TO SECOND [(fractional_seconds_precision)]



INTERVAL YEAR TO MONTH armazena a diferença
entre 2 valores datetime, e sua precisão.
Pode ser interessante em casos como:
 Lembrar o valor de uma data que é 120 meses
futuros.
 Verificar se já se passaram 6 meses de uma data
em especial.
A precisão padrão do ano é 2 casas.

INTERVAL YEAR TO MONTH armazena a diferença
entre 2 valores datetime, e sua precisão.
INTERVAL ‘123-2' YEAR(3) TO MONTH
Indica um intervalo de 123 anos e 2 meses.
INTERVAL ‘123' YEAR(3)
Indica um intervalo de 123 anos e 0 meses.
INTERVAL ‘300' MONTH(3)
Indica um intervalo de 300 meses.
INTERVAL ‘123' YEAR
ERRO pois a precisão padrão é de 2 casas.

INTERVAL DAY TO SECOND armazena um período de
tempo em termo de dias, horas, minutos e segundos.
INTERVAL '4 5:12:10.222' DAY TO SECOND(3)
Indica 4 dias, 5 horas, 12 minutos, 10 segundos e
222 milésimos de um segundo.
INTERVAL '4 5:12' DAY TO MINUTE
Indica 4 dias, 5 horas e 12 minutos.
INTERVAL '400 5' DAY(3) TO HOUR
Indica 400 dias e 5 horas.
INTERVAL '11:12:10.2222222' HOUR TO SECOND(7)
indica 11 horas, 12 minutos e 10.2222222 segundos.

INTERVAL YEAR TO MONTH:
CREATE TABLE time_example2
(loan_duration INTERVAL YEAR (3) TO MONTH);
INSERT INTO time_example2 (loan_duration)
VALUES (INTERVAL '120' MONTH(3));

Imaginando SYSDATE = 26-Sep-2001:
SELECT TO_CHAR( sysdate+loan_duration, 'dd-mon-yyyy')
FROM time_example2;

INTERVAL YEAR TO MONTH:
CREATE TABLE time_example3
(day_duration INTERVAL DAY (3) TO SECOND);
INSERT INTO time_example3 (day_duration)
VALUES (INTERVAL '180' DAY(3));

Imaginando SYSDATE = 26-Sep-2001:
SELECT sysdate + day_duration "Half Year"
FROM time_example3;



O tipo de dado TIMESTAMP é uma extensão do tipo
DATE, e armazena informações adicionais de
horas, minutos, segundos e frações de segundos.
É utilizado quando a precisão de tempo é
importante.
É permitido especificar o “time zone”.
TIMESTAMP[(fractional_seconds_precision)]
TIMESTAMP[(fractional_seconds_precision)]
WITH TIME ZONE
TIMESTAMP[(fractional_seconds_precision)]
WITH LOCAL TIME ZONE

No exemplo, a tabela new_employees é criada com
uma coluna start_date do tipo TIMESTAMP:
CREATE TABLE new_employees
(employee_id NUMBER,
first_name VARCHAR2(15),
last_name VARCHAR2(15),
...
start_date TIMESTAMP(7));
SELECT start_date FROM new_employees;
17-JUN-03 12.00.00.000000 AM


TIMESTAMP WITH TIME ZONE é uma variação de
TIMESTAMP e inclui a especificação do fuso horário
no valor.
O TIME ZONE é a diferença do horário especificado
em relação ao UTC (Universal Time Coordinate),
também chamado Greenwich.
TIMESTAMP[(fractional_seconds_precision)]
WITH TIME ZONE

Por exemplo,
TIMESTAMP '2003-04-15 8:00:00 -8:00'
é o mesmo que
TIMESTAMP '2003-04-15 11:00:00 -5:00'
Isto é, 8:00 a.m. Pacific Standard Time é o mesmo
que 11:00 a.m. Eastern Standard Time.

Isso também pode ser representado por:
TIMESTAMP '2003-04-15 8:00:00 US/Pacific'



O tipo de dado TIMESTAMP WITH LOCAL TIME ZONE
é outra variação de TIMESTAMP.
A diferença em relação a TIMESTAMP WITH TIME
ZONE é que o fuso horário não é armazenado como
parte do dado na coluna.
Considera o TIME ZONE especificado no banco.
TIMESTAMP[(fractional_seconds_precision)]
WITH LOCAL TIME ZONE

É apropriado para aplicações nas quais se deseja
exibir datas e horas usando os fusos dos clientes.

Por exemplo:
CREATE TABLE time_example
(order_date TIMESTAMP WITH LOCAL TIME ZONE);
INSERT INTO time_example VALUES('15-JAN-04
09:34:28 AM');
SELECT * FROM time_example;
ORDER_DATE
---------------------------15-JAN-04 09.34.28.000000 AM



Constraints forçam regras em nível de tabela.
Previnem a deleção de tabelas que possuam
tabelas dependentes.
Existem 5 tipos válidos de constraints:
◦ NOT NULL
◦ UNIQUE
◦ PRIMARY KEY
◦ FOREIGN KEY
◦ CHECK




Você pode nomear uma constraint ou o Oracle
especifica um nome usando o formato SYS_Cn.
É possível criar uma constraint em:
◦ Tempo de criação de uma tabela.
◦ Após uma tabela ter sido criada.
A constraint pode ser em nível de coluna ou tabela.
É possível verificar as constraints existentes no
dicionário de dados.
◦ Sintaxe:
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr]
[column_constraint],
...
[table_constraint][,...]);
◦ Constraint em nível de Coluna:
column [CONSTRAINT constraint_name] constraint_type,
◦ Constraint em nível de Tabela:
column,...
[CONSTRAINT constraint_name] constraint_type
(column, ...),
◦ Constraint em nível de Coluna:
CREATE TABLE employees(
employee_id NUMBER(6)
CONSTRAINT emp_emp_id_pk PRIMARY KEY,
first_name
VARCHAR2(20),
...);
◦ Constraint em nível de Tabela:
CREATE TABLE employees(
employee_id NUMBER(6),
first_name
VARCHAR2(20),
...
job_id
VARCHAR2(10) NOT NULL,
CONSTRAINT emp_emp_id_pk
PRIMARY KEY (EMPLOYEE_ID));

Garante que valores nulos não são permitidos na coluna.
…
Constraint NOT NULL
(Nenhuma linha pode
conter um valor nulo
para essa coluna)
Constraint
NOT NULL
Falta de constraint
NOT NULL
(permite valores nulos
nas linhas)
Constraint UNIQUE
EMPLOYEES
…
INSERT INTO
Permitido
Não permitido:
já existe!

Definida em nível de tabela ou de coluna.
CREATE TABLE employees(
employee_id
NUMBER(6),
last_name
VARCHAR2(25) NOT NULL,
email
VARCHAR2(25),
salary
NUMBER(8,2),
commission_pct
NUMBER(2,2),
hire_date
DATE NOT NULL,
...
CONSTRAINT emp_email_uk UNIQUE(email));
DEPARTMENTS
PRIMARY KEY
…
Não permitido
(valor nulo)
Não permitido
(50 já existe)
INSERT INTO
DEPARTMENTS
PRIMARY
KEY
…
EMPLOYEES
FOREIGN
KEY
…
INSERT INTO
Não permitido
(9 não existe)
Permitido

Definida em nível de tabela ou de coluna.
CREATE TABLE employees(
employee_id
NUMBER(6),
last_name
VARCHAR2(25) NOT NULL,
email
VARCHAR2(25),
salary
NUMBER(8,2),
commission_pct
NUMBER(2,2),
hire_date
DATE NOT NULL,
...
department_id
NUMBER(4),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id),
CONSTRAINT emp_email_uk UNIQUE(email));
◦ FOREIGN KEY: define a coluna na tabela
filha.
◦ REFERENCES: identifica a tebela e coluna na
tabela pai.
◦ ON DELETE CASCADE: remove as linhas
dependentes na tabela filha quando uma
linha é removida da tabela pai.
◦ ON DELETE SET NULL: converte valores
dependentes na tabela filha em NULL.
◦ ON DELETE RESTRICT/NO ACTION: não
permite remover a linha da tabela pai.
◦ Define a condição que cada linha precisa satisfazer.
◦ Expressões não permitidas:
 Referências a pseudo-colunas: CURRVAL,
NEXTVAL, LEVEL e ROWNUM.
 Chamadas às funções SYSDATE, UID, USER e
USERENV.
 Consultas que se refiram a outros valores em
outras linhas.
..., salary NUMBER(2)
CONSTRAINT emp_salary_min
CHECK (salary > 0),...
CREATE TABLE employees
( employee_id
NUMBER(6)
CONSTRAINT
emp_employee_id
, first_name
VARCHAR2(20)
, last_name
VARCHAR2(25)
CONSTRAINT
emp_last_name_nn
, email
VARCHAR2(25)
CONSTRAINT
emp_email_nn
CONSTRAINT
emp_email_uk
, phone_number
VARCHAR2(20)
, hire_date
DATE
CONSTRAINT
emp_hire_date_nn
, job_id
VARCHAR2(10)
CONSTRAINT
emp_job_nn
, salary
NUMBER(8,2)
CONSTRAINT
emp_salary_ck
, commission_pct NUMBER(2,2)
, manager_id
NUMBER(6)
, department_id NUMBER(4)
CONSTRAINT
emp_dept_fk
departments (department_id));
PRIMARY KEY
NOT NULL
NOT NULL
UNIQUE
NOT NULL
NOT NULL
CHECK (salary>0)
REFERENCES
UPDATE employees
SET
department_id = 55
WHERE department_id = 110;
UPDATE employees
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.EMP_DEPT_FK)
violated - parent key not found

Você não pode apagar uma linha que contenha
uma primary key que é usada como uma
foreign key em outra tabela.
DELETE FROM departments
WHERE
department_id = 60;
DELETE FROM departments
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.EMP_DEPT_FK)
violated - child record found

Cria uma tabela e insere as linhas por meio de
subconsulta.
CREATE TABLE table
[(column, column...)]
AS subquery;



Número de colunas da tabela precisa ser igual ao da
subconsulta.
Permite definir colunas com nomes de colunas e
valores default.
Somente o tipo de dado e a constraint NOT NULL de
uma coluna são passados para a nova tabela.
CREATE TABLE dept80
AS
SELECT employee_id, last_name,
salary*12 ANNSAL,
hire_date
FROM
employees
WHERE
department_id = 80;
Table created.
DESCRIBE dept80

Use o comando ALTER TABLE para:
 Adicionar uma nova coluna.
 Modificar uma coluna existente.
 Definir uma valor default para uma nova coluna.
 Eliminar uma coluna.

Use o comando ALTER TABLE para adicionar,
modificar ou remover colunas:
ALTER TABLE table
ADD
(column datatype [DEFAULT expr]
[, column datatype]...);
ALTER TABLE table
MODIFY
(column datatype [DEFAULT expr]
[, column datatype]...);
ALTER TABLE table
DROP
(column);

Adicionando uma coluna:
ALTER TABLE dept80
ADD
(job_id VARCHAR2(9));
Table altered.

A coluna é inserida sempre no final da tabela:
…

Modificando uma coluna:
ALTER TABLE dept80
MODIFY
(last_name VARCHAR2(30));
Table altered.


É possível modificar o tipo, tamanho e valor
DEFAULT de uma coluna.
Uma alteração no valor DEFAULT da coluna afeta
apenas inserções subseqüentes na tabela.

Removendo uma coluna:
ALTER TABLE dept80
DROP COLUMN job_id;
Table altered.


A opção SET UNUSED marca uma ou mais colunas
como não-utilizável, para remoção futura.
A opção DROP UNUSED COLUMNS remove as colunas
marcadas como UNUSED.
ALTER
SET
OR
ALTER
SET
TABLE <table_name>
UNUSED(<column_name>);
TABLE <table_name>
UNUSED COLUMN <column_name>;
ALTER TABLE <table_name>
DROP UNUSED COLUMNS;





Todos os dados e estrutura da tabela são apagados.
Quaisquer transações pendentes são commitadas.
Todos os índices são apagados.
Todas as constraints são apagadas.
NÃO é possível fazer rollback do comando DROP
TABLE.
DROP TABLE dept80;
Table dropped.


A tabela e suas constraints são enviadas à lixeira.
Continua a ocupar espaço no banco de dados.







Todos os dados e estrutura da tabela são apagados.
Quaisquer transações pendentes são commitadas.
Todos os índices são apagados.
Todas as constraints são apagadas.
NÃO é possível fazer rollback do comando DROP
TABLE PURGE.
NÃO envia o objeto para a lixeira e não há mais
como recuerá-lo.
Libera a área de armazenamento no banco.
DROP TABLE dept80 PURGE;
Table dropped.


Ferramenta de reparo para modificações acidentais
em tabelas:
 Restaura uma tabela para um ponto anterior.
 Fácil uso, disponibilidade e rápida execução.
Sintaxe:
FLASHBACK TABLE[schema.]table[,
[ schema.]table ]...
TO { TIMESTAMP | SCN } expr
[ { ENABLE | DISABLE } TRIGGERS ];

Exemplo:
DROP TABLE emp2;
Table dropped
SELECT original_name, operation, droptime,
FROM recyclebin;
…
FLASHBACK TABLE emp2 TO BEFORE DROP;
Flashback complete

Objetivos:
◦ Criação de novas tabelas.
◦ Criação de uma tabela por meio da sintaxe
CREATE TABLE AS.
◦ Verificação da existência de tabelas.
◦ Remoção de tabelas.
1.
Crie a tabela DEPT com base na tabela abaixo.
Salve o script. Execute o script e confirme que a
tabela foi devidamente criada.
Column Name
ID
Key Type
Primary key
NAME
Nulls/Unique
FK Table
FK Column
Data type
NUMBER
VARCHAR2
Length
7
25
1.
Solução:
CREATE TABLE dept
(id NUMBER(7) CONSTRAINT department_id_pk
PRIMARY KEY,
name VARCHAR2(25));
DESCRIBE dept
2.
Popule a tabela DEPT com dados da tabela
DEPARTMENTS. Inclua apenas as colunas
necessárias.
INSERT INTO dept
SELECT department_id, department_name
FROM departments;
3.
Crie a tabela EMP com base na tabela abaixo. Salve
o script e execute-o. Confirme que a tabela foi
criada corretamente.
Column Name
ID
LAST_NAME
FIRST_NAME
DEPT_ID
Key Type
Nulls/Unique
FK Table
DEPT
FK Column
ID
Data type
NUMBER
VARCHAR2
VARCHAR2
NUMBER
Length
7
25
25
7
3.
Solução:
CREATE TABLE emp
(id
NUMBER(7),
last_name
VARCHAR2(25),
first_name VARCHAR2(25),
dept_id
NUMBER(7)
CONSTRAINT emp_dept_id_FK REFERENCES dept (id)
);
DESCRIBE emp
4.
Crie a tabela EMPLOYEES2 com base na estrutura
da tabela EMPLOYEES. Inclua apenas o
EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY e
DEPT_ID, respectivamente.
CREATE TABLE employees2 AS
SELECT employee_id id, first_name, last_name,
salary, department_id dept_id
FROM employees;
5.
Apague a tabela EMP.
DROP TABLE emp;
6.
Consulte a lixeira e depois recupere a tabela EMP
eliminada no exercício 5.
SELECT original_name, operation, droptime
FROM recyclebin;
FLASHBACK TABLE emp TO BEFORE DROP;
DESC emp
7.
Apague a tabela EMP de maneira que não possa ser
recuperada. Depois, consulte a lixeira para
confirmar.
DROP TABLE emp PURGE;
SELECT original_name, operation, droptime
FROM recyclebin;