DO BÁSICO AO AVANÇADO PARA MANIPULAÇÃO E ... TABLE employees ( employee_id NUMBER(6) CONSTRAINT...

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.

◦ Você especifica: O nome da tabela

Nome da coluna, tipo de dado da coluna e seu tamanho.

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr][, ...]);

◦ 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.

USERB USERA

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: ◦ :

◦ Confirme sua criação:

DESCRIBE dept

CREATE TABLE dept

(deptno NUMBER(2),

dname VARCHAR2(14),

loc VARCHAR2(13),

create_date DATE DEFAULT SYSDATE);

Table created.

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:

O tipo de dado INTERVAL DAY TO SECOND armazena um período de tempo em termos de dias, horas, minutos e segundos:

INTERVAL YEAR [(year_precision)] TO MONTH

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.

É apropriado para aplicações nas quais se deseja exibir datas e horas usando os fusos dos clientes.

TIMESTAMP[(fractional_seconds_precision)]

WITH LOCAL TIME ZONE

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:

◦ Constraint em nível de Coluna:

◦ Constraint em nível de Tabela:

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr]

[column_constraint],

...

[table_constraint][,...]);

column,...

[CONSTRAINT constraint_name] constraint_type

(column, ...),

column [CONSTRAINT constraint_name] constraint_type,

◦ Constraint em nível de Coluna:

◦ Constraint em nível de Tabela:

CREATE TABLE employees(

employee_id NUMBER(6)

CONSTRAINT emp_emp_id_pk PRIMARY KEY,

first_name VARCHAR2(20),

...);

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)

Falta de constraint NOT NULL (permite valores nulos nas linhas)

Constraint NOT NULL

…

EMPLOYEES

Constraint UNIQUE

INSERT INTO

Não permitido: já 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,

...

CONSTRAINT emp_email_uk UNIQUE(email));

DEPARTMENTS PRIMARY KEY

INSERT INTO Não permitido (valor nulo)

Não permitido (50 já existe)

…

DEPARTMENTS

EMPLOYEES

FOREIGN

KEY

INSERT INTO Não permitido (9 não existe)

Permitido

PRIMARY

KEY …

…

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));

Definida em nível de tabela ou de coluna.

◦ 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 PRIMARY KEY

, first_name VARCHAR2(20)

, last_name VARCHAR2(25)

CONSTRAINT emp_last_name_nn NOT NULL

, email VARCHAR2(25)

CONSTRAINT emp_email_nn NOT NULL

CONSTRAINT emp_email_uk UNIQUE

, phone_number VARCHAR2(20)

, hire_date DATE

CONSTRAINT emp_hire_date_nn NOT NULL

, job_id VARCHAR2(10)

CONSTRAINT emp_job_nn NOT NULL

, salary NUMBER(8,2)

CONSTRAINT emp_salary_ck CHECK (salary>0)

, commission_pct NUMBER(2,2)

, manager_id NUMBER(6)

, department_id NUMBER(4)

CONSTRAINT emp_dept_fk REFERENCES

departments (department_id));

UPDATE employees

*

ERROR at line 1:

ORA-02291: integrity constraint (HR.EMP_DEPT_FK)

violated - parent key not found

UPDATE employees

SET department_id = 55

WHERE department_id = 110;

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.

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 table

[(column, column...)]

AS subquery;

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:

A coluna é inserida sempre no final da tabela:

ALTER TABLE dept80

ADD (job_id VARCHAR2(9));

Table altered.

…

Modificando uma coluna:

É 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.

ALTER TABLE dept80

MODIFY (last_name VARCHAR2(30));

Table altered.

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 TABLE <table_name>

SET UNUSED(<column_name>);

ALTER TABLE <table_name>

SET UNUSED COLUMN <column_name>;

OR

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.

A tabela e suas constraints são enviadas à lixeira.

Continua a ocupar espaço no banco de dados.

DROP TABLE dept80;

Table dropped.

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

FLASHBACK TABLE emp2 TO BEFORE DROP;

Flashback complete

…

SELECT original_name, operation, droptime,

FROM recyclebin;

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 NAME

Key Type Primary key

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;